dynamodb query multiple values from GSI

Question

I have a dynamo DB table (id(pk),name(sk),email,date,itemId(number)) and GSI on (itemId pk, date(sk) trying to query for an array of itemIds [1,2,3,4] but getting error using the IN statement in KeyExperssionValue when doing

aws.DocClient.query

 const IdsArrat = [1,2,3,4,5];
 const query: {
  IndexName: 'accountId-createdAt-index',
  KeyConditionExpression: 'itemId IN (:a1,:a2,:a3)',
  ExpressionAttributeValues: {
    {
        ':a1':1,
        ':a2':2,
          .......
    }
  },
  ScanIndexForward: false,
},

getting error using the IN statement in.

This it possible to query for multiple values on GSI in dynamoDb ?

detzu · Accepted Answer · 2023-03-23 01:55:16Z

2

It's not possible to have a IN and join multiple values in a query , but it's possible to use BatchGetItem to request multiple queries that are solved in parallel . This is actually very close to the IN solution you want.

This solution is only working to query the table not the GSI index (thank you Monish Chhadwa !)

The result will be a list of the elements in the table.

There are limits in the number of queries in the size of the result set < 16 MB and the number of queries < 100.

Please check this document for details : https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_BatchGetItem.html

edited Mar 23, 2023 at 1:55

answered Apr 27, 2022 at 20:39

detzu

7566 silver badges14 bronze badges

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

2 Comments

Monish Chhadwa Over a year ago

But you cannot do a BatchGetItem on a GSI; it can only be done on the main table

detzu Over a year ago

@MonishChhadwa I agree , I will specify this. There are ways to solve this depending on the data, scanning the whole index and then filtering with in ? Could be very costly or not. Size of the GSI is important .

puji · Accepted Answer · 2020-04-17 02:52:34Z

1

You're trying to query for multiple different partition key's in a GSI. This can only be done by doing multiple individual queries (3 in the example). It's also possible with a GSI that multiple values would get returned for a single Partition key lookup, so it's better to query the partition key "itemId" individually.

See the following for reference: https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Query.html#DDB-Query-request-KeyConditionExpression

answered Apr 17, 2020 at 2:52

puji

5074 silver badges10 bronze badges

3 Comments

Jason Over a year ago

could you please explain how to query multiple values (array of values) for a single Partition key. for example I want to get all itemIds that are in this array [1,2,3,4]. is it possible in a single query

puji Over a year ago

Dynamodb is a key value lookup database. Its not possible in a single query to dynamo to lookup multiple partition key values. It's like saying for a hash table lookup you want to lookup multiple keys but with a single request. In this case just loop through your array and make the different calls to dynamodb.

puji Over a year ago

If your item ids are in a range, it might be possible to return multiple if you setup the right GSI. But again in this case you need a single partition key that includes all the item ids, and item id would be the sort key that you could search via a range.

JustinChien · Accepted Answer · 2022-08-17 15:16:03Z

0

refering to this answer https://stackoverflow.com/a/70494101/7706503, you could try partiQL to construct similar statement for querying gsi table with multiple key,

select * from table."gsi_index_name" where partition_key in [key1,key2]

then you could send the statement with low level api in one shot, for example, in dotnet, it's called ExecuteStatementAsync

answered Aug 17, 2022 at 15:16

JustinChien

847 bronze badges

Collectives™ on Stack Overflow

dynamodb query multiple values from GSI

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related