Query a Global Secondary Index using contains in DynamoDB local

Question

I have id as the hash key of my table and returnItemId which is the GSI. The returnItemId is a string which contains values separated by commas. Given a number for the GSI, I want to be able to query and get the correct item that contains it by using contains

var params = {
    "AttributeDefinitions": [ // describbes the key schema of the table
    {
      "AttributeName": "id",
      "AttributeType": "S"
    },
    {
      "AttributeName": "returnItemId",
      "AttributeType": "S"
    }
  ],
  // Hash for Primary Table
  "KeySchema": [
    {
      "AttributeName": "id",
      "KeyType": "HASH"
    }
  ],

  "GlobalSecondaryIndexes": [
    {
      "IndexName": "ReturnItemIndex",
      "KeySchema": [
        {
          "AttributeName": "returnItemId", //must match one of attributedefinitions names
          "KeyType": "HASH"
        }
      ],
      "Projection": {
        "ProjectionType": "ALL"
      },
      "ProvisionedThroughput": {
        "ReadCapacityUnits": 5,
        "WriteCapacityUnits": 5
      }
    }
  ],

  "ProvisionedThroughput": {
    "ReadCapacityUnits": 5,
    "WriteCapacityUnits": 5
  },


  "TableName": "my-table"
};
dynamodb.createTable(params, function(err, data) {
    if (err) ppJson(err); // an error occurred
    else ppJson(data); // successful response

});

Then I am going to create 2 items

var params = {
    TableName: 'my-table',
    Item: { 
    
        "id": "the_first_item",
        "returnItemId": "123,456,789"
    },
};
docClient.put(params, function(err, data) {
    if (err) ppJson(err); // an error occurred
    else ppJson(data); // successful response
});

And the second item

var params = {
    TableName: 'my-table',
    Item: { 
    
        "id": "the_second_item",
        "returnItemId": "987,654,321"
    },
};
docClient.put(params, function(err, data) {
    if (err) ppJson(err); // an error occurred
    else ppJson(data); // successful response
});

The two items look like

I am trying to run a query and get the correct item which contains 987 using the following query. Since my first item has 123,456,789 and the second item has 987,654,321 this method should return the second item.

var params = {
    TableName: 'my-table',
    IndexName: 'ReturnItemIndex', // optional (if querying an index)
    KeyConditionExpression: 'contains(returnItemId, :return_id)',
    //FilterExpression: 'contains(returnItemId, :return_id)', // a string representing a constraint on the attribute
    ExpressionAttributeValues: { ':return_id': '987' },
};
docClient.query(params, function(err, data) {
    if (err) ppJson(err); // an error occurred
    else ppJson(data); // successful response
});

But am getting errors about using contains in keyconditionexpression. Is this method possible?

Answer 1

contains can only be used in filters which means:
- query or scan operations will traverse all data to apply your filters
- your cost of read operations will include all data read not just matched data
- with contains "12", you would probably match "123" and "124" too
- better than comma separated is to use StringSet or NumberSet data type

I would suggest another layout
Keyschema:
Partiton Key: id
Sort Key: returnItemId
GSI
Partition Key: returnItemId

Data:

------------------------------------
| id                | returnItemId |
------------------------------------
| "the_first_item"  | "123"        |
| "the_first_item"  | "456"        |
| "the_first_item"  | "789"        |
| "the_second_item" | "987"        |
| "the_second_item" | "654"        |
| "the_second_item" | "321"        |
------------------------------------

then query GSI for key condition returnItemId = 987 ( no filter expression )