Question 243770 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Amazon web services – Query DynamoDB table using multiple GSI simultaneously

I have a DynamoDB to store logs, the table has the columns creation_year, creation_month and state_code, I need to filter by those columns so I created a GSI for each, right now I have the following query for one index and it works fine:

let command = new QueryCommand({
    TableName: "my-table",
    IndexName: 'creation_year-index',
    ExpressionAttributeValues: {
      ":creation_year":  "2023"
    },
    KeyConditionExpression: "creation_year = :creation_year"
  });

However now I need to fetch a log from X creation_year and Y creation_month (and later on by state_code as well), how could I build such query combining global secondary indexes?

Thanks beforehand.

Tags:

2

Answers


  1. 0

    You cannot. You can create a hierarchical sort key, but it may not be suitable.

    Best approach would be to use the Keyconditionexpression to narrow down the items as much as possible, then use a filter expression to filter the remaining subset of items.

    If you only ever want a specific month worth of data for a given year then a simple GSI like this will work

    PK SK Data
    2023:04 Status Some data

    But it’s more likely you are searching for a specific log belonging to a single process within a given time period, in that case it’s bests to use a timestamp as sort key:

    PK SK Status Data
    LogSourceId 2023-04-01T20:55:000 Status Some data

    Now for a given metric, you can check all of the logs within millisecond granularity. You can further filter on status. However, if you find that your filtering is inefficient, you can also append the status to the sort key.

    Login or Signup to reply.
  2. 0

    You could create an aggregated field that have the year and month like this: {creation_year}_{creation_month}, and create a GSI for it. For the state_code you could use expression filters.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search