Dynamo DB query not returning expected results when using nested collections

Question

I have an AWS DynamoDB Table with the following structure:

I am trying to get back all the items that have at least one RequestItem with the Id 3401. Here is what I've tried so far (c# code):

     IAmazonDynamoDB client = new AmazonDynamoDBClient(
            new BasicAWSCredentials(configuration["AccessKey"], configuration["SecretKey"]),
            RegionEndpoint.USEast1);

        var request = new ScanRequest
        {
            TableName = "dynamo-table-name",
            ExpressionAttributeNames = new Dictionary<string, string>
            {
                {"#requestItems", "RequestItems"},
                {"#requestId", "Id"}
            },
            ExpressionAttributeValues = new Dictionary<string, AttributeValue>
            {
                {":val", new AttributeValue {N = "3401"}}
            },
            FilterExpression = "contains(#requestItems.#requestId, :val)"
        };
        var response = await client.ScanAsync(request);

I did some variations on FilterExpression (using a simple "=" instead of "contains") but... I still don't get back the results. The query passes without errors, but the result it's an empty list.

However, the same code works for properties which are not collections (e.g. Contact.EmailAddress)

What am I missing?

[EDIT]

I tried another solution that was suggested:

 var request = new ScanRequest
        {
            TableName = "dynamo-table-name",
            ExpressionAttributeNames = new Dictionary<string, string>
            {
                {"#requestItems", "RequestItems"}
            },
            ExpressionAttributeValues = new Dictionary<string, AttributeValue>
            {
                {
                    ":val",
                    new AttributeValue
                    {
                        L = new List<AttributeValue>
                        {
                            {
                                new AttributeValue
                                {
                                    M = new Dictionary<string, AttributeValue>
                                        {{"Id", new AttributeValue {N = "3401"}}}
                                }
                            }
                        }
                    }
                }
            },
            FilterExpression = "contains(#requestItems, :val)"
        };
        var response = await client.ScanAsync(request);

but I still do not receive results.

The filterexpression #requestItems.#requestId is probably for nested map. In your case, you might be looking for something like this -> https://stackoverflow.com/a/54019892/3996255 — ranjith, Mar 27 '20 at 16:29
Hmm, not sure, I have a nested list and I want to check one of it's item's property. And I don't down how to replicate that answer to C# (there is javascript, passing dynamic objects) in c# you need to pass something which is strongly-typed — Dragos Stoica, Mar 27 '20 at 16:46
I really hope there is an easier method ! FilterExpression = "contains(#requestItems, :val)" ExpressionAttributeValues being- { ":val", new AttributeValue { L = new List{ { new AttributeValue { M = new Dictionary{ { "Id", new AttributeValue { N = "3401" } } } } }}}} — ranjith, Mar 27 '20 at 17:44
Thanks for trying to help. I tried your suggestion. It's not working... I still get 0 results. I will update my question to reflect your suggestion. — Dragos Stoica, Mar 30 '20 at 07:01
Better (though not ideal) link to existing question: https://stackoverflow.com/questions/32235474/dynamodb-nested-query-support — Dima Tisnek, Apr 06 '20 at 06:54
@FranczykRafał, no, I've dropped Dynamo db in favor of Mongo DB, you simply can't do complex queries on a Key-Value pair database — Dragos Stoica, Nov 15 '21 at 07:08

score 3 · Answer 1 · answered Apr 06 '20 at 07:49

You cannot really do the query you want with DynamoDB. The only thing you could do, if you know the maximum amount of items that could be in RequestItems is to chain together a lot of contains checks with OR: (RequestItems.0.Id = :val) OR (RequestItems.1.Id = :val) OR (RequestItems.2.Id = :val) .... That doesn't seem like a good idea though, unless you know in advance that RequestItems will always contain a certain, low, number of items.

contains does not work the way you want it to. If you do contains(path, <some number>), DynamoDB checks if the value found at path is a Set of Numbers and whether the value provided in <some number> is contained within that set.

I'm afraid your only option, given your data schema, is to fetch all the items and filter them in your code.

score 1 · Accepted Answer · answered Apr 06 '20 at 07:17

I apologise if this is not authoritative.

I suspect that DynamoDB cannot do that.

Moreover, the idea behind DynamoDB is that it should not do that.

DynamoDB does not support arbitrary function evaluation over data.

DynamoDB is a K-V (kinda), store, not a database. The "dynamo" way is to query all the rows (items) you may need and analyse the columns (keys) client-side. Note that it costs exactly same (for dynamo, small difference for traffic), because aws charges you for something like "database disk reads". And that it's just as cumbersome or easy, for example, you still have to deal with pagination.

Dynamo DB query not returning expected results when using nested collections

2 Answers2