DYNAMODB Confusion using WHERE IN in select statement with execute_statement called through the resource instead of client

[NT-Timm]

On this page: https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/example_dynamodb_Scenario_PartiQLSingle_section.html
It shows how to call execute_statement through .meta.client of a Dynamodb resource. This is handy due to not having to put the parameters in the partiql string. (Protects against injection attacks and such) And it's doubly handy due to automatically selecting the proper type.
I've got it working in most situations.

But there's a big issue I have with it and that's supplying parameters as a list/set/tuple/whatever
I've tried looking at the execute_statement call and it mentions that it allows for example Set[int]/etc
And I know that's the call for the client side of the function and not the resource side.

Nothing I've tried works:
partiql = select field from table WHERE key=key and field IN [?]
params = [["test1", "test2", "test3"]]
params = [("test1", "test2", "test3")]
params = [{"test1", "test2", "test3"}]
These just get ignored and don't even generate an error.
params = ["test1"]
This parameter for some reason works. But yeah not what I needed. Easier to just use an = then.

I've tried it without the brackets in the partiql:
partiql = select field from table WHERE key=key and field IN ?
But this causes a generic error:
when calling the ExecuteStatement operation: IN operator must have a left hand argument of type Variable Reference and right hand argument of type Seq with at least one member

The weird part is that if I put the parameters hardcoded in the partiql string it works without problem.

Am I using the wrong type on the resource side for the list? (If I use a list as a parameter for a non list action I do get a message that a variable of type S, N, B is needed and not of type L)
Is there otherwise a way for me to see where exactly the Resource call get's translated to a Client call?

[aBurmeseDev]

Hi @NT-Timm thanks for reaching out.

I'm trying to understand what the issue is here, could you share your code snippet to reproduce and debug logs (with any sensitive info redacted) by adding boto3.set_stream_logger('') to your script? That might help give more insight into what's going on.

Also here's the boto3 docs link to execute_statement for reference.

Best,
John

[NT-Timm]

2022-10-19 11:39:23,076 botocore.endpoint [DEBUG] Making request for OperationModel(name=ExecuteStatement) with params: {'url_path': '/', 'query_string': '', 'method': 'POST', 'headers': {'X-Amz-Target': 'DynamoDB_20120810.ExecuteStatement', 'Content-Type': 'application/x-amz-json-1.0', 'User-Agent': 'Boto3/1.24.59 Python/3.9.13 exec-env/AWS_Lambda_python3.9 Botocore/1.27.59 Resource', 'X-Amzn-Trace-Id': 'Root='}, 'body': b'{"Statement": "SELECT paidTotal FROM \\"table\\" WHERE parent IN [\'thisworks\'] AND begins_with(id, ?) AND paidTotal IN [?]", "Parameters": [{"S": "iv_"}, {"L": [{"N": "504"}, {"N": "399"}, {"N": "524"}, {"N": "491"}]}]}', 'url': 'https://dynamodb.eu-central-1.amazonaws.com/', 'context': {'client_region': 'eu-central-1', 'client_config': <botocore.config.Config object at >, 'has_streaming_input': False, 'auth_type': None}}

I've just added the logging. And despite my thought it seems [504,399,524,491] gets translated to {"L": [{"N": "504"}, {"N": "399"}, {"N": "524"}, {"N": "491"}]}
I think that might be the problem. Shouldn't it be "SN" for numberset?

I've tried putting a SS or a SN manually in the parameters but it get's translated to M

What is the correct syntax for usage of IN then?

The stupid part is that if I put the parameters hardcoded in the sql is just works. If I remove the paidTotal part in the WHERE I get all the records. (And yes I've checked some records have those paidTotal amounts. I've also tried it with another field)

[NT-Timm]

Ok. Someone on reddit pointed me to how to properly put parameters in in IN statement.

It seems you have to add a ? for every parameter in the IN and add the corresponding parameter to the parameterlist.

Kinda weird but it works.
But I would have never thought of this. No example ever shows that this is the way it's supposed to use.