How to search in dynamodb with a complex value using .net sdk

Question

I have just started with DynamoDB. I have background in MongoDB and relational databases and I am structuring my JSON in more like a graph structure than a flat structure. For example,

[
  {
    "id": "1",
    "title": "Castle on the hill",
    "lyrics": "when i was six years old I broke my leg",
    "artists": [
        {
            "name": "Ed Sheeran",
            "sex": "male"
        }
    ]
  }
]

For example, If I like to search the item by 'Ed Sheeran'. The closest I have got is this and this is not even matching any value.

var request = new ScanRequest
{
  TableName = "Table",
  ProjectionExpression = "Id, Title, Artists",
  ExpressionAttributeValues = new Dictionary<string,AttributeValue>
  {
    { ":artist", new AttributeValue { M = new Dictionary<string, AttributeValue> 
            {
                { "Name", new AttributeValue { S = "Ed Sheeran" }}
            } 
        } 
    }
  },
  ExpressionAttributeNames = new Dictionary<string, string>
  {
    { "#artist", "Artists" },
  },
  FilterExpression = "#artist = :artist",
};

var result = await client.ScanAsync(request);

Most of the example and tuturials I have watched so far, they have treated dynamodb as a table in a normal relational database with very flat design. Am I doing it wrong to structure the JSON as above? Should Artists be in a separate table?

And If it can be done, how do i search by some value in a complex type like in the above example?

techriften · Accepted Answer · 2020-04-11 10:35:25Z

0

First of all, you should not be using the scan operation in dynamodb. I would strongly recommend to use query. Have a look at this stack overflow question first. If you want to search on any attribute, you can either mark them as the primary key (either hash_key or hash_key + sort_key) or create an index on the field you want to query on.

Depending on the use case of id attribute in your schema, if you are never querying on id attribute, I would recommend the structure something like this :

[
  {
    "artist_name" : "Ed Sheeran" // Hash Key
    "id": "1", // Sort Key (Assuming id is unique and combination of HK+SK is unique)
    "title": "Castle on the hill",
    "lyrics": "when i was six years old I broke my leg",
    "artists": [
        {
            "name": "Ed Sheeran",
            "sex": "male"
        }
    ]
  }
]

Alternatively, if you also need to query on id and it has to be the hash key, you can an index on the artist_name attribute and then query it.

[
  {
    "artist_name" : "Ed Sheeran" // GSI Hash key
    "id": "1", // Table Hash key
    "title": "Castle on the hill",
    "lyrics": "when i was six years old I broke my leg",
    "artists": [
        {
            "name": "Ed Sheeran",
            "sex": "male"
        }
    ]
  }
]

In either case, it is not possible to query inside a nested object without using scan operation and then iterating it in code, something which you have already tried.

answered Apr 11, 2020 at 10:35

techriften

4213 silver badges16 bronze badges

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

1 Comment

Laurence Over a year ago

Thanksf or your reply, hmm... what would you suggest if artists are in an array (as you can see in my example json) so i can't do exact what you suggested. what is the best model for this kind of one > many relationship type in Dynamodb?

Collectives™ on Stack Overflow

How to search in dynamodb with a complex value using .net sdk

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related