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!

In that case you need a Scan operation which basically loads each and every entry in your database (and bills that access) and then filters the results — luk2302
– luk2302, Commented Jul 22, 2021 at 8:13
Thank you for your answer @luk2302, i would try this method! — Jarrett GXZ
– Jarrett GXZ, Commented Jul 22, 2021 at 9:45
It works! I used the .scan() method with the JS aws-sdk library — Jarrett GXZ
– Jarrett GXZ, Commented Jul 22, 2021 at 10:07

Maurice · Accepted Answer · 2021-07-22 08:19:01Z

3

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.

answered Jul 22, 2021 at 8:19

Maurice

13.3k2 gold badges31 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jarrett GXZ Over a year ago

Thanks for your answer! However, wouldn't this structure take up more space? For example, if a single user have 6 values, it would take up 6 items worth of space.

Maurice Over a year ago

Yes, but storage is cheaper than compute, which is why NoSQL patterns are around ;-)

lowcrawler Over a year ago

@Maurice To further dig into that, how does it affect things if there are a significant amount of other fields. It's one thing to refactor the DB structure around a single array with a trivial data structure -- but what if each element was considerably more complex and had other sub-arrays and sub-objects? I could see a single 'item' easily turning into hundreds of DB 'rows'? Is there something I'm missing?

Maurice Over a year ago

@lowcrawler That could happen, but then you're maybe not using the right tool or data model for the job. Can't say much about this without a concrete problem.

Jarrett GXZ · Accepted Answer · 2021-07-22 14:00:14Z

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

answered Jul 22, 2021 at 14:00

Jarrett GXZ

6181 gold badge8 silver badges20 bronze badges

1 Comment

Amr Saber Over a year ago

You should not be depending on scan for production, it is extremely slow as it needs to scan the whole table to return what you queried for.

Collectives™ on Stack Overflow

AWS DynamoDB querying with values in an array

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related