How to Search Nested Array of Object in DynamoDB

Question

I am new to dynamoDb, i would like to search nested array properties. For ex my table has sample data given below

[{
      id: '123',
      name: 'test',
      subShops: [
        {
          shopId: '234',
          shopName: 'New Shop'
        },
        {
          shopId: '345',
          shopName: 'New Shop 2'
        }
      ]
    },
    {
      id: '1234',
      name: 'test2',
      subShops: [
        {
          shopId: '2345',
          shopName: 'New Shop 3'
        },
        {
          shopId: '3456',
          shopName: 'New Shop 4'
        }
      ]
    }
]

I want to search where name : ['test', 'test2', 'test3'] or subShops[].shopeName where ['New Shop', 'New Shop 2', ''New Shop 3].

I have existing code for only name : ['test', 'test2', 'test3']

const params: AWS.DynamoDB.DocumentClient.ScanInput = {
    TableName: VENDOR_TABLE_INFO.Name,
    ExpressionAttributeNames: { "#Id": "name" },
    FilterExpression: `#Id in (${Object.keys(keyValues).toString()}) or contains (subShops, :category2)`,
    ExpressionAttributeValues: {
      ...keyValues,
      ':category2': {
        ...keyValues
      }
    }
  };

As a general note: If your data model requires a scan, you should probably reconsider it. — Maurice
– Maurice, Commented Jul 18, 2021 at 14:25
It looks like your access pattern is "fetch sub shops by shop name". If that's the case, you need to model your data so it can be fetched by name. The way you have it modeled now, it appears you can only fetch by ID. — Seth Geoghegan
– Seth Geoghegan, Commented Jul 18, 2021 at 21:26

peteragility · Accepted Answer · 2021-07-19 04:28:33Z

1

Please notice that DynamoDB (DDB) is mainly a hyperscale key-value serverless datastore with very limited query pattern and flexibility, you need to be ok with that to use it.

In each DDB table you can only define one hash key (pk), and up to 5 local secondary index (sort key) for querying. And you can have up to 20 Global Secondary Index (GSI)

In you example, you have hash key of "id", and then if you need to query by "name" only, you need to build a GSI with name as hash key, and included the needed fields in the projection. There is no way to query by "shopname" in sub shop array unless you "flaten" the JSON tree structure.

In short, if you want JSON tree level data query/manipulation and all of your data is JSON documents, i would suggest you to use Amazon DocumentDB which is MongoDB 4 compatible, or directly use MongoDB itself.

answered Jul 19, 2021 at 4:28

peteragility

2711 silver badge4 bronze badges

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

1 Comment

Ambati Dilip Over a year ago

Understand your answer, i should better use sub table in this way if it is much complex at query. Thanks for suggestion.

Collectives™ on Stack Overflow

How to Search Nested Array of Object in DynamoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related