How to query a DynamoDB table to retreive only items which have a specific value inside an string-set attribute?

Question

The items in my table have an attribute of type string set. I'll stick to the example from the documentation and call the set "colors". As the name indicates the set holds various strings representing colors in each item. This would look like this.

Now I want to query the table so that I retrieve all items where a specific color is within the set. So in regards to the attached picture I would like to query for the color "Green" and want to receive the items Picture2 and Picture3.

Is there a way to do this?

Since the amount of all possible colors and items is huge plus the fact that only a very small amount of colors are associated to an item, a scan would be very inefficient. So far I tried to create a global secondary index (GSI) but it seems that its not possible in the way I want it or am I wrong?

Seth Geoghegan Seth Geoghegan · Accepted Answer · 2020-10-13T20:31:29

Unless the field you are searching for is built into the primary key or secondary index, scan will be your only option.

The scan operation will allow you to use the contains keyword to search the set

let params = {
    TableName : 'TABLE_NAME',
    FilterExpression: "contains(#color, :color)",
    ExpressionAttributeNames: {
        "#color": "color",
    },
    ExpressionAttributeValues: {
        ":color": "Blue",
    }       
};

documentClient.scan(params, function(err, data) {
    console.log(data);
});

According to the docs on secondary indexes, you cannot build an index using a set as the primary key

The key schema for the index. Every attribute in the index key schema must be a top-level attribute of type String, Number, or Binary. Other data types, including documents and sets, are not allowed.

How to query a DynamoDB table to retreive only items which have a specific value inside an string-set attribute?

1 Answers