AWS DynamoDB querying with values in an array

Question

With the following (TableName: "Table"):

[
 {
  name: "name1",
  values: ["string1", "string2"]
 },
 {
  name: "name2",
  values: ["string1", "string2", "string3"]
 }
]

My partition key would be name, without any sort key. I am trying to query all the items with the same value field. The following is what I have tried:

docClient.query({
      TableName: "Table",
      KeyConditionExpression: "name = :name",
      FilterExpression: "contains(values, :value)",
      ExpressionAttributeValues: {
        ":name": "certain_name",
        ":value": "string1",
      },
    });

Suppose I want to query all the items with the value field of "string1". However, AWS DynamoDB requires the partition key which is unique for all my items. Are there any ways to query all the items with the same value field, without bothering about the partition key?

Or would a better approach be to just get all the items from DynamoDB, and just query with my own methods?

Thank you everyone!

Answer 1

For that query pattern you should probably reconsider your data model, I'd suggest something like this:

PK	SK	GSI1PK	GSI1SK
NAME#name1	VALUE#val1	VALUE#val1	NAME#name1
NAME#name1	VALUE#val2	VALUE#val2	NAME#name1
NAME#name1	VALUE#val3	VALUE#val3	NAME#name1
NAME#name2	VALUE#val1	VALUE#val1	NAME#name2

PK and SK are the partition and sort key of the base table and there is a Global Secondary Index "GSI1" which has GSI1PK as the partition and GSI1SK as the sort key.

Get All Values By Name would be something like this:

Query(KeyConditionExpression: PK = "NAME#<name>")

This returns a list of all values.

Get All Names By Value could be done like this:

Query(KeyConditionExpression: GSI1PK = "VALUE#<value>", Index: GSI1)

This returns a list of all names.

This pattern is called an inverted index and you could in principle also define the Global Secondary Index with the partition key as SK and sort key as PK in order not to duplicate the attributes.

The NAME# and VALUE# prefixes could also be omitted, but it's good practice if you're using a single table design.

Answer 2

I managed to make it work by using the .scan() method from the aws-sdk.

const attributName = "values";
const attributeValue = "string1";

docClient.scan({
  TableName: "Table",
  ExpressionAttributeValues: {
    ":attribute": attributeValue,
  },
  FilterExpression: `contains(${attributName}, :attribute)`,
});