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

Question

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 ?

Leeroy Hannigan · Accepted Answer · 2022-09-29 18:41:16Z

1

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.

answered Sep 29, 2022 at 18:41

Leeroy Hannigan

20.5k4 gold badges23 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

hunterhacker · Accepted Answer · 2022-09-29 07:57:13Z

0

You would have to do multiple queries.

answered Sep 29, 2022 at 7:57

hunterhacker

7,2801 gold badge18 silver badges15 bronze badges

Comments

Naxi · Accepted Answer · 2022-09-29 17:47:42Z

0

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

answered Sep 29, 2022 at 17:47

Naxi

2,2647 gold badges50 silver badges102 bronze badges

1 Comment

jarmod Over a year ago

No need for client-side filtering. You should be able to query on the name 'Alex' while supplying a FilterExpression e.g. ssn in ('ssn1','ss2','ss3',ssn4').

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related