DynamoDB GetBatch operation with nested query

Question

So my situation is that I have the below JSON data in AWS DynamoDB table user:

[{
        "name": "data1",
        "addresses": [{
                "city": "Mumbai",
                "state": "Maharashtra"
            },
            {
                "city": "Chennai",
                "state": "Tamil Nadu"
            }
        ]
    },
    {
        "name": "data2",
        "addresses": [{
            "city": "Amritsar",
            "state": "Punjab"
        }]
    }
]

Now I need to get all the user who has cities like Mumbai and Amritsar. So how should I create a query to get this result.

I tried to Scan with a nested query and used GetBatch to search by multiple values. But doing this together is not happening. Can someone put a light on this issue? I am trying to do this on AWS AppSync.

Thanks!!!

Answer 1

Unfortunately, DynamoDB does not support querying nested attributes in this way.

You've modeled the one-to-many relationship between Users and Addresses by storing addresses in a complex attribute. This is a common pattern to model one-to-many relationships. The drawback to this pattern is that you cannot support any access patterns that fetch users by their address.

Instead, you'll need to model the one-to-many relationship in a way that supports fetching users by address. Ideally, the address would be built into the primary key so you could perform a fast query operation.