skip to Main Content

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 ?

3

Answers


  1. Chosen as BEST ANSWER

    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
    

  2. You would have to do multiple queries.

    Login or Signup to reply.
  3. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search