DynamoDB Index Query Includes

I'm trying to query a DynamoDB table and check to see if the query I pass in is included in the partition key of the table index.

For example I have a DynamoDB table with example data below.

+-----+----------------+---------------------------------------------+
| id  |     title      |                 description                 |
+-----+----------------+---------------------------------------------+
| id1 | My Title One   | This is an awesome item in my database      |
| id2 | My Title Two   | This is also an awesome item in my database |
| id3 | The Item Three | Hello World                                 |
+-----+----------------+---------------------------------------------+

I have a global index on the property title. The primary partition key is id.

What I want to do is take in a search query from the user and match all items for the title.

I have the following code that will match the user term exactly to the title.

const params = {
    ExpressionAttributeValues: {
        ":q": {
            S: req.query.q
        }
    },
    KeyConditionExpression: "title = :q",
    TableName: "MyTable",
    IndexName: 'title-index'
};

dynamodb.query(params, (err, titles) => {
    if (err) {
        return res.status(500).send({"Error": "Error"});
    }

    titles = titles.Items
    .map(item => AWS.DynamoDB.Converter.unmarshall(item));

    return res.send(titles);
});

So if the user searches for My Title One it will return the result with the id of id1. Now I want to add support for if the user enters My Title, I want it to return id's id1 AND id2. Currently it returns nothing, since nothing exactly matches.

I know this is possible with using DynamoDB scans instead of queries, but due to the fact that my table has so many items, doing a scan in this case seems like it would be SO inefficient in terms of performance and cost.

What is the easiest way to achieve this?

1 answer

  • answered 2018-07-11 09:29 bwinant

    You won't be able to do this with a DynamoDB query. A DynamoDB query requires an exact equals comparison against a primary key. You would have to use a scan which, as you noted, could be very inefficient and expensive depending on the size of your table.

    Even if it was efficient and cheap, a scan still isn't a great option to implement search functionality. What if your user searches for "my title"? Dynamo has no functions for doing case insensitive string comparisons. You would have to store another attribute in the table with a more searchable version of the fields you want to scan and filter on.

    If you need real search functionality, then you will need to use something else. It is "relatively" easy to hookup Dynamo and ElasticSearch (if you know ElasticSearch). You could use a DynamoDB stream with a Lambda trigger to insert/update/delete an ElasticSearch index when the table changes. Then you would execute your title search against ElasticSearch. If you have worked with ElasticSearch before, it isn't that bad. If not, there is a bit of learning curve.

    Another option, which you probably don't want to hear, is to ditch Dynamo and use a relational database, and use a LIKE query with some wildcards.

    Unfortunately there isn't a nice way to implement a decent search on top of just Dynamo.