[SOLVED] DynamoDB: Can't use contains in sort key

DynamoDB: Can't use contains in sort key

I believe I've found a bug in DynamoDB using PartiQL.

Given this table:

  name      = "my-table"
  hash_key  = "user_id"
  range_key = "sites"

  attributes = [
    { name = "user_id", type = "S" },
    { name = "sites", type = "S" },
  ]

  global_secondary_indexes = [
    {
      name            = "by-user-id-and-sites-global-index"
      hash_key        = "user_id"
      range_key       = "sites"
      projection_type = "ALL"
    },
  ]

For some user_ids, the following PartiQL SELECT Statement works, for others doesn't (returns no results):

SELECT * FROM "my-table"."by-user-id-and-sites-global-index" WHERE "user_id" = '91f66b0e-1565-431b-aa4a-5db301af9510' AND contains("sites", '6211be1c-472f-4dff-83b5-5e9418106ff3')

I also tried with begins_with and it always works, however I explicitly need to use contains in the sort key.

Any ideas?

Solution

It's not a bug. PartiQL is built on top of the core DynamoDB APIs, so whichever syntax you use dictates the core DynamoDB API being called.

In your case you're using a Query under the hood, as you provide the partition key and a condition on the sort key, however, the contains operator is not a valid sort key expression. Only these conditions can be used: https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Condition.html

For some reason your table and index share the same primary key?

If you want to use contains on the sort key, you would need an index where that attribute is not a key.