Updating Docs For Sending in Arrays

[jlag34]

There are workarounds that you should add to the documentation around sending in array of values or when you hit this error:

BadRequestException: Array parameters are not supported.

If you stringify the array and cast it it will work. Here is an example where I have pictures and tags and adding them to a join table. Since a picture can have many tags, I want to create a record for each picture/tag combo using their ids:

CreatePictureTag = async (pictureId, tagIds) => {
  const sql = `
    INSERT INTO picture_tags (picture_id, tag_id)
    VALUES (:pictureId, unnest(array[:tagIds]));
  `

  const sqlValues = [
    { name: 'pictureId', value: pictureId, cast: 'uuid' },
    { name: 'tagIds', value: `{${tagIds.join(',')}}`, cast: 'uuid[]' },
  ]

  try {
    await rdsService.query(sql, sqlValues)
    return { status: 200 }
  } catch (err) {
    console.log('createPictureTag error : ', err)
    throw err
  }
}

value: {${tagIds.join(',')}} is the magic line. Replace tagIds with your array and you should be good to go.

[jlag34]

@jeremydaly I also found a solution to your issue in the docs about not being able to support arrayValues

credit to people here:
aws/aws-sdk#9

Basic idea:

const params = {
    secretArn: 'yourSecretArn',
    resourceArn: 'yourResourceArn',
    sql: 'SELECT * FROM member WHERE member_id IN (:id0, :id1)',
    parameters: [
      {  name: 'id0', value: { stringValue: '123'  }  },
      {  name: 'id1', value: { stringValue: 'abc'  }  },
    ],
  })

Dynamic solution:

const parameters = ids.map((id, index) => ({ name: `id${index}`, value: {stringValue: id} }))
const placeholder = [...Array(ids.length).keys()].map(x => `:id${x}`).join(',')
const params = {
    secretArn: 'yourSecretArn',
    resourceArn: 'yourResourceArn',
    sql: `SELECT * FROM member WHERE member_id IN (${placeholder})`,
    parameters 
  })

Then just the match the style guide in the docs. Also, I had to add a cast since I was passing in UUIDs so just showing off that it's possible to cast (error message might show if you don't).

const parameters = ids.map((id, index) => ({ name: `id${index}`, value: { stringValue: id }, cast: 'uuid' }))
const placeholder = [ ...Array(ids.length).keys() ].map((x) => `:id${x}`).join(',')

const sql = `
  SELECT * FROM member
  WHERE member_id IN (${placeholder});
`

try {
  await rdsService.query(sql, parameters)
  return { status: 200 }  
}

I hope this helps!

EDIT:

lol I see that you actually posted in that issues thread back in 2020. Maybe you have already seen the workaround then?