How to query dynamodb where I have fetch records based on a list of key values?-CodePudding

I have a dynamodb table on which a GSI is defined with a partition key and sort key.

Let's say the parition key is name and sort key is ssn for the GSI.

I have to fetch based upon a name and ssn, below is the query I am using and it works fine.

table.query(IndexName='lookup-by-name',KeyConditionExpression=Key('name').eq(name)\
                & Key('ssn').eq(ssn))

Now, I have to query based upon a name and a list of ssns.

For Example

ssns=['ssn1','ss2','ss3',ssn4']
name='Alex'

query all records which has name as 'Alex' and whose ssn is present in ssns list.

How do I implement something like this ?

CodePudding user response：

You would have to do multiple queries.

CodePudding user response：

Ended up using just the name as keycondition and then filter out the ssn in python code.

Below worked for me as the number of records was not a lot.

response=table.query(IndexName='lookup-by-name',KeyConditionExpression=Key('name').eq(name)
ssns=['ssn1','ss2','ss3',ssn4']
data= response['Items']

data=list(filter(lambda record: record['ssn'] in ssns,data))
return data

CodePudding user response：

While DynamoDB native SDK cannot provide the functionality to do this, you can achieve it using PartiQL which provides a SQL like interface for interacting with DynamoDB.

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-gettingstarted.html

import boto3
client = boto3.client('dynamodb', region_name="eu-west-1")

name = 'Alex'
ssns = ['ssn1','ssn2','ssn3','ssn4']

response = client.execute_statement(
    Statement = "Select * from \"MyTableTest\".\"lookup-by-name\" where \"name\" = '%s' AND \"ssn\" IN %s" % (name, ssns)
)

print(response['Items'])

It would also require you to use the lower level Client instead of the Table level resource which you are using above.