How to specify parameters for DynamoDB PartiQL in Nodejs

Question

I want to execute a SELECT query on DynamoDb with PartiQL in NodeJs, and getting errors.

a simple statement works with no problem.

    const r = await client.send(
      new ExecuteStatementCommand({
          Statement: `SELECT * FROM Users WHERE userId in ['abcd1234']`
      })
    )

but when try to execute statements with parameters, i got an error.

code

    const r2 = await client.send(
      new ExecuteStatementCommand({
          Statement: `SELECT * FROM Users WHERE userId in ?`,
          Parameters: [
            ['abcd1234']
          ]
      })
    )

error

{
  "errorType": "ValidationException",
  "errorMessage": "1 validation error detected: Value '[]' at 'parameters' failed to satisfy constraint: Member must have length greater than or equal to 1",
  "trace": [
    "ValidationException: 1 validation error detected: Value '[]' at 'parameters' failed to satisfy constraint: Member must have length greater than or equal to 1",
    "    at deserializeAws_json1_0ExecuteStatementCommandError (/var/task/node_modules/@aws-sdk/client-dynamodb/dist/cjs/protocols/Aws_json1_0.js:2202:41)",
    "    at processTicksAndRejections (internal/process/task_queues.js:97:5)",
    "    at async /var/task/node_modules/@aws-sdk/middleware-serde/dist/cjs/deserializerMiddleware.js:6:20",
    "    at async /var/task/node_modules/@aws-sdk/middleware-signing/dist/cjs/middleware.js:12:24",
    "    at async StandardRetryStrategy.retry (/var/task/node_modules/@aws-sdk/middleware-retry/dist/cjs/defaultStrategy.js:56:46)",
    "    at async /var/task/node_modules/@aws-sdk/middleware-logger/dist/cjs/loggerMiddleware.js:6:22",
    "    at async /var/task/node_modules/@aws-sdk/lib-dynamodb/dist/cjs/commands/ExecuteStatementCommand.js:29:26",
    "    at async Runtime.handler (/var/task/save.js:39:16)"
  ]
}

Does anyone has any solutions?

here is the whole code.

  const client = DynamoDBDocumentClient.from(
    new DynamoDBClient({})
  );

    // this works
    const r = await client.send(
      new ExecuteStatementCommand({
          Statement: `SELECT * FROM Users WHERE userId in ['abcd1234']`
      })
    )

    // this gets an error
    const r2 = await client.send(
      new ExecuteStatementCommand({
          Statement: `SELECT * FROM Users WHERE userId in ?`,
          Parameters: [
            ['abcd1234']
          ]
      })
    )

Answer 1

As far as I can tell, it's not quite possible to do what you're trying to do (pass an array of strings as a parameter in a prepared statement as the right-hand argument to an IN operator ).

It seems that the best you can do is:

executeStatement({
  Statement: `SELECT * FROM Users WHERE userId in [?, ?]`,
  Parameters: [
    {"S": "firstId"},
    {"S": "secondId"}, 
    // ...etc.
  ]
})

Note that, the Parameters property takes an array of key-value pairs. Each parameter attribute value is described as a name-value pair. The name is the data type (e.g. "S" for string), and the value is the data itself (e.g. the user ID.)

In your case, if you had a list of users, you could do something like:

const users = [
  { id: "id-1" },
  { id: "id-2" },
  { id: "id-3" },
]

executeStatement({
  Statement: `SELECT * FROM Users WHERE userId in [${users.map(() => '?').join(',')}]`,
  Parameters: 
    users.map(user => ({"S": user.id}))  
})

Be aware that there's probably some upper limit to how many parameters you can pass, but I couldn't find this number documented anywhere.

Answer 2

// Before
SELECT * FROM Users WHERE userId in ?

// After
SELECT * FROM Users WHERE userId in '?'

Answer 3

When I try this in Python with boto3 client the way it works for me is if pass parameter values directly, not the type/value maps they show in the docs

executeStatement({
  Statement: `SELECT * FROM Users WHERE userId in (?,?,?)`,
  Parameters: [
     'id-1',
     'id-2',
     'id-3'
  ]
})

When I pass [{"S": "id-1"}, {"S": "id-2"}, {"S": "id-3"}] I get

An error occurred (ValidationException) when calling the ExecuteStatement operation: 
Key value must be of type S, N, or B. Key name: accessionNumber, Key type: M