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 -> stackoverflow.com/a/54019892/3996255 — ranjith
– ranjith, Commented Mar 27, 2020 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
– Dragos Stoica, Commented Mar 27, 2020 at 16:46
I really hope there is an easier method ! FilterExpression = "contains(#requestItems, :val)" ExpressionAttributeValues being- { ":val", new AttributeValue { L = new List<AttributeValue>{ { new AttributeValue { M = new Dictionary<string,AttributeValue>{ { "Id", new AttributeValue { N = "3401" } } } } }}}} — ranjith
– ranjith, Commented Mar 27, 2020 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
– Dragos Stoica, Commented Mar 30, 2020 at 7:01
Better (though not ideal) link to existing question: stackoverflow.com/questions/32235474/… — Dima Tisnek
– Dima Tisnek, Commented Apr 6, 2020 at 6:54

ojii · Accepted Answer · 2020-04-06 07:49:58Z

3

+50

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.

answered Apr 6, 2020 at 7:49

ojii

4,7812 gold badges26 silver badges36 bronze badges

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

Comments

Dima Tisnek · Accepted Answer · 2020-04-06 07:17:07Z

1

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.

answered Apr 6, 2020 at 7:17

Dima Tisnek

11.9k4 gold badges73 silver badges129 bronze badges

Collectives™ on Stack Overflow

Dynamo DB query not returning expected results when using nested collections

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related