How to filter by elements in an array (or nested object) in DynamoDB

Question

My data is as follows:

[
  {
    orgId: "ABC",
    categories: [
      "music",
      "dance"
    ]
  },
  {
    orgId: "XYZ",
    categories: [
      "math",
      "science",
      "art"
    ]
  },
  ...
]

I have the primary key on orgId, and I want to use DynamoDB query to filter and return only items with category "science," for example.

(Category does not need to be part of any index: I am willing to accept the additional worker overhead, provided that I can do the query within Dynamo itself.)

I am having a dickens of a time getting this working. I can readily change categories into nested objects if that would help?

But the comparison operators are so limited in DynamoDB that it appears there is no way to filter by array elements, or nested objects?

If not, what's the better approach here? To turn each category into its own first level attribute, such as:

[
  {
    orgId: "XYZ",
    category_math: true,
    category_science: true
  }
]

Surely not?

Answer 1

 var params = {
  ExpressionAttributeValues: {
   ":orgIdValue": {
     S: "XYZ"
    },
   ":categoriesValue": {
     S: "science"
    }
  }, 
  KeyConditionExpression: "orgId = :orgIdValue", 
  FilterExpression : "categories CONTAINS :categoriesValue", 
  TableName: "MYTABLE"
 };
 dynamodb.query(params, function(err, data) {
   if (err) console.log(err, err.stack); // an error occurred
   else     console.log(data);           // successful response
 });

CONTAINS : Checks for a subsequence, or value in a set. AttributeValueList can contain only one AttributeValue element of type String, Number, or Binary (not a set type). If the target attribute of the comparison is of type String, then the operator checks for a substring match. If the target attribute of the comparison is of type Binary, then the operator looks for a subsequence of the target that matches the input. If the target attribute of the comparison is a set ("SS", "NS", or "BS"), then the operator evaluates to true if it finds an exact match with any member of the set. 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.

Categories is a top level attribute, you don't actually have any nested attributes. Top level scalar attributes can be indexed. Although categories is top level its not a scalar attribute (its a set), so you cannot index it.

You can use a FilterExpression to narrow down your query, and you can use the CONTAINS comparator on lists.

Answer 2

The answer posted above should work, per the documentation. But when using the Node.JS AWS DynamoDB SDK's DocumentClient, it doesn't. Particularly, I tried:

  {
    TableName: "site",
    IndexName: "orgId-lastCaptured-index",
    KeyConditionExpression: "orgId = :orgId",
    FilterExpression: "categories CONTAINS :categoriesValue",
    ExpressionAttributeValues: {
      ":orgId": orgId,
      ":categoriesValue": myVariable,
    }
  }

This resulted in the following error: { ValidationException: Invalid FilterExpression: Syntax error; token: "CONTAINS", near: "categories CONTAINS :categoriesValue"

I adjusted the query to the alternative query formatting as follows:

  {
    TableName: "site",
    IndexName: "orgId-lastCaptured-index",
    KeyConditions: {
      orgId: {
        ComparisonOperator: "EQ",
        AttributeValueList: [orgId],
      },
    },
    QueryFilter: {
      categories: {
        ComparisonOperator: "CONTAINS",
        AttributeValueList: [myVariable],
      }
    }
  }

This worked as anticipated, filtering the returned results such that categories variable has an element that matches myVariable.

Update: You can now do CONTAINS operations without using the deprecated QueryFilter with this syntax: FilterExpression: "contains(categories, :categoriesValue)"