DynamoDB LIKE '%' (contains) search over an array of objects using a key from the object, NodeJS

Question

I am trying to use a "LIKE" search on DynamoDB where I have an array of objects using nodejs.

Looking through the documentation and other related posts I have seen this can be done using the CONTAINS parameter.

My question is - Can I run a scan or query over all of my items in DynamoDB where a value in my object is LIKE "Test 2".

Here is my DynamoDB Table

This is how it looks as JSON:

{
  "items": [
    {
      "description": "Test 1 Description",
      "id": "86f550e3-3dee-4fea-84e9-30df174f27ea",
      "image": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX/86f550e3-3dee-4fea-84e9-30df174f27ea.jpg",
      "live": 1,
      "status": "new",
      "title": "Test 1 Title"
    },
    {
      "description": "Test 2 Description",
      "id": "e17dbb45-63da-4567-941c-bb7e31476f6a",
      "image": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX/e17dbb45-63da-4567-941c-bb7e31476f6a.jpg",
      "live": 1,
      "status": "new",
      "title": "Test 2 Title"
    },
    {
      "description": "Test 3 Description",
      "id": "14ad228f-0939-4ed4-aa7b-66ceef862301",
      "image": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX/14ad228f-0939-4ed4-aa7b-66ceef862301.jpg",
      "live": 1,
      "status": "new",
      "title": "Test 3 Title"
    }
  ],
  "userId": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"
}

I am trying to perform a scan / query which will look over ALL users (every row) and look at ALL items and return ALL instances where description is LIKE "Test 2".

I have tried variations of scans as per the below:

{
      "TableName": "my-table",
      "ConsistentRead": false,
      "ExpressionAttributeNames": {
        "#items": "items",
      },
      "FilterExpression": "contains (#items, :itemVal)",
      "ExpressionAttributeValues": {
        ":itemVal": 
        {
          "M": {
            "description": {
              "S": "Test 2 Description"
            },
            "id": {
              "S": "e17dbb45-63da-4567-941c-bb7e31476f6a"
            },
            "image": {
              "S": "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX/e17dbb45-63da-4567-941c-bb7e31476f6a.jpg"
            },
            "live": {
              "N": "1"
            },
            "status": {
              "S": "new"
            },
            "title": {
              "S": "Test 2 Title"
            }
          }
        }
      }
    }

The above scan works but as you can see I am passing in the whole object as an ExpressionAttributeValues, what I want to do is just pass in the description for example something like the below (which doesnt work and returns no items found).

{
      "TableName": "my-table",
      "ConsistentRead": false,
      "ExpressionAttributeNames": {
        "#items": "items.description",
      },
      "FilterExpression": "contains (#items, :itemVal)",
      "ExpressionAttributeValues": {
        ":itemVal": 
        {
          "S": "Test 2"
        }
      }
    }

Alternatively, would it be better to create a separate table where all the items are added and they are linked via the userId? I was always under the impression there should be one table per application but in this instance I think if I had all the item data at the top level, scanning it would be a lot safer and faster.

C.K Munn C.K Munn · Accepted Answer · 2019-10-17T06:57:08

So with nearly 200 views since posting and no responses I have come up with a solution that does not immediately solve the initial problem (I honestly do not think it can be solved) but have come up with an alternative approach.

Firstly I do not want two tables as this seems overkill, and I do not want the aws costs associated with two tables.

This has lead me to restructure the primary keys with prefixes which I can search over using the "BEGINS_WITH" dynamodb selector query.

Users will be added as U_{USER_ID} and items will be added as I_{USER_ID}_{ITEM_ID}, this way I only have one table to manage and pay for and this allows me to run BEGINS_WITH "U_" to get a list of users or "I_" to get a list of items.

I will then flatten the item data as strings so I can run "contains" searches on any of the item data. This also allows me to run a "contains {USER_ID}" search on the primary keys for items so I can get a list of items for a particular user.

Hope this helps anyone who might come up against the same issue.

DynamoDB LIKE '%' (contains) search over an array of objects using a key from the object, NodeJS

1 Answers