CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodbaws-sdk-nodejs
Jarrett GXZ
Jarrett GXZ

Reputation: 608

AWS DynamoDB querying with values in an array

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!

Upvotes: 4

Views: 4962

Answers (2)

Jarrett GXZ
Jarrett GXZ

Reputation: 608

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)`,
});

Upvotes: 2

Maurice
Maurice

Reputation: 13108

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.

Upvotes: 3

Related Questions