Is there a way how to address nested properties in AWS DynamoDB for purpose of documentClient.query() call?

Question

I am currently testing how to design a query from AWS.DynamoDB.DocumentClient query() call that takes params: DocumentClient.QueryInput, which is used for retrieving data collection from a table in DynamoDB.

Query seems to be simple and working fine while working with indexes of type String or Number only. What I am not able to make is an query, that will use a valid index and filter upon an attribute that is nested (see my data structure please).

I am using FilterExpression, where can be defined logic for filtering - and that seems to be working fine in all cases except cases when trying to do filtering on nested attribute.

Current parameters, I am feeding query with

parameters  {
  TableName: 'myTable',
  ProjectionExpression: 'HashKey, RangeKey, Artist ,#SpecialStatus, Message, Track, Statistics'
  ExpressionAttributeNames: { '#SpecialStatus': 'Status' },
  IndexName: 'Artist-index',
  KeyConditionExpression: 'Artist = :ArtistName',
  ExpressionAttributeValues: {
    ':ArtistName': 'BlindGuadian',
    ':Track': 'Mirror Mirror'
  },
  FilterExpression: 'Track = :Track'
}

Data structure in DynamoDB's table:

{
'Artist' : 'Blind Guardian',
..
'Track': 'Mirror Mirror',
'Statistics' : [
   {
   'Sales': 42,
   'WrittenBy' : 'Kursch'
   }
]
}

Lets assume we want to filter out all entries from DB, by using Artist in KeyConditionExpression. We can achieve this by feeding Artist with :ArtistName. Now the question, how to retrieve records that I can filter upon WritenBy, which is nested in Statistics?

To best of my knowledge, we are not able to use any other type but String, Number or Binary for purpose of making secondary indexes. I've been experimenting with Secondary Indexes and Sorting Keys as well but without luck.

I've tried documentClient.scan(), same story. Still no luck with accessing nested attributes in List (FilterExpression just won't accept it).

I am aware of possibility to filter result on "application" side, once the records are retrieved (by Artists for instance) but I am interested to filter it out in FilterExpression

Answer 1

If I understand your problem correctly, you'd like to create a query that filters on the value of a complex attribute (in this case, a list of objects).

You can filter on the contents of a list by indexing into the list:

var params = {
    TableName: "myTable",
    FilterExpression: "Statistics[0].WrittenBy = :writtenBy",
    ExpressionAttributeValues: {
        ":writtenBy": 'Kursch'
    }
};

Of course, if you don't know the specific index, this wont really help you.

Alternatively, you could use the CONTAINS function to test if the object exists in your list. The CONTAINS function will require all the attributes in the object to match the condition. In this case, you'd need to provide Sales and WrittenBy, which probably doesn't solve your problem here.

The shape of your data is making your access pattern difficult to implement, but that is often the case with DDB. You are asking DDB to support a query of a list of objects, where the object has a specific attribute with a specific value. As you've seen, this is quote tricky to do. As you know, getting the data model to correctly support your access patterns is critical to your success with DDB. It can also be difficult to get right!

A couple of ideas that would make your access pattern easier to implement:

• Move WrittenBy out of the complex attribute and put it alongside the other top-level attributes. This would allow you to use a simple FilterExpression on the WrittenBy attribute.
• If the WrittenBy attribute must stay within the Statistics list, make it stand alone (e.g. [{writtenBy: Kursch}, {Sales: 42},...]). This way, you'd be able to use the CONTAINS keyword in your search.
• Create a secondary index with the WrittenBy field in either the PK or SK (whichever makes sense for your data model and access patterns).