DynamoDB PartiQL Query For Specific Map Element From List

Question

I have a DynamoDB with data that looks like this:

{
  "userId": {
    "S": "someuserID"
  },
  "listOfMaps": {
    "L": [
      {
        "M": {
          "neededVal": {
            "S": "ThisIsNeeded1"
          },
          "id": {
            "S": "1"
          }
        }
      },
      {
        "M": {
          "neededVal": {
            "S": "ThisIsNeeded2"
          },
          "id": {
            "S": "2"
          }
        }
      },
      ...
    ]
  },
  "userName": {
    "S": "someuserName"
  }
}

The listOfMaps can contain more than just 2 maps, as is denoted by the ellipsis. Is there a PartiQL query I can put together that will let me get the neededVal based on the userId and the id of the item in the map itself?

I know that I can query for the n-th item's neededVal like this:

SELECT "listOfMaps"[N]."neededVal" 
FROM "table-name" 
WHERE "userId" = 'someuserID'

But is it possible to make it do something like this:

SELECT "listOfMaps"."neededVal" 
FROM "table-name"
WHERE "userId" = 'someuserID' AND "listOfMaps"."id" = '4'

user_13549664 · Answer

It looks like you're modeling the one-to-many relationship using a complex attribute (e.g. a list of objects). This is a completely valid approach to modeling one-to-many relationships and is best used when 1) the results data doesn't change (or don't change often) and 2) you don't have any access patterns around the data within the complex attribute.

However, since you do want to perform searches based on data within the complex attribute, you'd be better off modeling the data differently.

For example, you might consider modeling results in the user partition with a PK=user_id SK=neededVal#. This would allow you to fetch items by User ID (QUERY where PK=USER#user_id SK begins_with neededVal#).

I don't know the specifics around your access patterns, but can say that you'll need to move results into their own items if you want to support access patterns around the data within your complex attribute.

DynamoDB PartiQL Query For Specific Map Element From List

1 Answers