How to Filter Nested Array Object in DynamoDB

Question

I am very beginner to AWS DynamoDB, I want to scan the DynamoDB with SENDTO.emailAddress = "[email protected]" as FilterExpression.

The DB Structure looks like this

{
    ID
    NAME
    MESSAGE
    SENDTO[
        {
            name
            emailAddress
        }
    ]
}

A Sample Data

{
    ID: 1,
    NAME: "HELLO",
    MESSAGE: "HELLO WORLD!",
    SENDTO: [
        {
            name: "First",
            emailAddress: "[email protected]"
        },
        {
            name: "Second",
            emailAddress: "[email protected]"
        }
    ]
}

I want to retrieve document that match emailAddress. I tried to scan with filter expression and here is my code to retrieve the data. I am using AWS Javascript SDK.

let params = {
    TableName : "email",
    FilterExpression: "SENDTO.emailAddress = :emailAddress",
    ExpressionAttributeValues: {
        ":emailAddress": "[email protected]",
    }
}

let result = await ctx.docClient.scan(params).promise();

You can fetch data from dynamodb table by using the partition key or the combination of partition key and sort key. On the other hand, you can use indexes. — Dmitry Grinko
– Dmitry Grinko, Commented Nov 15, 2017 at 11:05
I tried about partition key and sort key it wasn't helpful, I read that this operation is not supported by DynamoDB. We can query an object attribute but not object array attribute. — Narayanan Ramanathan
– Narayanan Ramanathan, Commented Nov 15, 2017 at 15:47

notionquest · Accepted Answer · 2017-11-15 18:04:02Z

6

In order to find the item by sendto attribute, you need to know both name and emailAddress attribute value. DynamoDB can't find the data by just one of the attributes in an object (i.e. email attribute value alone).

CONTAINS function can be used to find the data in List data type.

CONTAINS is supported for lists: When evaluating "a CONTAINS b", "a" can be a list; however, "b" cannot be a set, a map, or a list.

Sample code using Contains:-

var params = {
    TableName: "email",
    FilterExpression: "contains (SENDTO, :sendToVal)",
    ExpressionAttributeValues: {
        ":sendToVal": {
            "name" : "First",
            "emailAddress" : "[email protected]"
        }
    }
};

If you don't know the value of name and emailAddress attribute, you may need to remodel the data to fulfill your use case.

answered Nov 15, 2017 at 18:04

notionquest

39.4k6 gold badges120 silver badges111 bronze badges

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

3 Comments

Narayanan Ramanathan Over a year ago

Yeah thanks, I have remodel the data as two separate arrays.

notionquest Over a year ago

If you keep two separate arrays, how would you associate the name and email address?

foxtrotuniform6969 Over a year ago

This compares the whole object, which can be useful. How do we go about just checking one field of the map?

Dmitry Grinko · Accepted Answer · 2017-11-15 16:15:29Z

2

I think that you should create two tables for users and for messages.

The user table has partition_key: user_id and sort_key: email and a field with an array of his messages ids.

The message table has partition_key: message_id and a field with an array of users ids.

When you will get the array of users ids you can use BATCH GET query to get all users of one message.

When you will get the array of message ids you can use BATCH GET query to get all messages of one user.

If you want to get one user by email you can use QUERY method.

Docs

edited Nov 15, 2017 at 16:15

answered Nov 15, 2017 at 16:05

Dmitry Grinko

15.4k15 gold badges66 silver badges91 bronze badges

6 Comments

Narayanan Ramanathan Over a year ago

Thanks, I have remodel the data as two separate arrays. But there is no way in DynamoDB to do the above operation.

Dmitry Grinko Over a year ago

You should use documentClent for this

Dmitry Grinko Over a year ago

I gave you a link

Narayanan Ramanathan Over a year ago

Yeah I used documentClient and tried your solution, it did work but I want to have the data as a single document.

Dmitry Grinko Over a year ago

Actually this is a feature of nosql databases. Feel free to create separate tables. It will help to manage throwout capacity for each table and save your money.

|

Collectives™ on Stack Overflow

How to Filter Nested Array Object in DynamoDB

2 Answers 2

3 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related