AWS Dynamodb Query - get items from table with condition

Question

There is Dynamo table with fields:

• email (primary)
• tenant
• other stuff

I want to get all the items where email contains 'mike'

In my nodejs server, I have this code

 const TableName= 'UserTable';
 const db = new aws.DynamoDB();
 const email = 'mike.green@abc.com'

    params = {
      TableName: userTableName,
      KeyConditionExpression: '#email = :email',
      ExpressionAttributeNames: {
        '#email': 'email',
      },
      ExpressionAttributeValues: {
        ':email': { S: email },
      },
    };

  db.query(params, (err, data) => {
    if (err) {
      reject(err);
    } else {
      const processedItems = [...data.Items].sort((a, b) => a.email < b.email ? -1 : 1);
      const processedData = { ...data, Items: processedItems };
      resolve(processedData);
    }

this works ^^ only if I search entire email mike.green@abc.com

Question 1 - But, if i want to search mike, and return all items where email contains mike, How can i get that?

Question 2 If I want to get all the rows where email contains mike and tenant is Canada. How can i get that?

Answer 1

I'm not a NodeJS user but hope it will be helpful.

Question 1 - But, if i want to search mike, and return all items where email contains mike, How can i get that?

Key expressions are reserved to equality constraints. If you want to have more querying flexibility, you need to use a filter expression. Please notice that you won't be able to use filter expression on your partition key. You can find more information on https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html but the most important is:

Key Condition Expression

To specify the search criteria, you use a key condition expression—a string that determines the items to be read from the table or index.

You must specify the partition key name and value as an equality condition.

You can optionally provide a second condition for the sort key (if present). The sort key condition must use one of the following comparison operators:

a = b — true if the attribute a is equal to the value b

a < b — true if a is less than b

a <= b — true if a is less than or equal to b

a > b — true if a is greater than b

a >= b — true if a is greater than or equal to b

a BETWEEN b AND c — true if a is greater than or equal to b, and less than or equal to c.

The following function is also supported:

begins_with (a, substr)— true if the value of attribute a begins with a particular substring.

......

Question 2 If I want to get all the rows where email contains mike and tenant is Canada. How can i get that?

You can use a filter expression to do that and use one of available functions https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Expressions.OperatorsAndFunctions.html#Expressions.OperatorsAndFunctions.Syntax. A filter expression is:

If you need to further refine the Query results, you can optionally provide a filter expression. A filter expression determines which items within the Query results should be returned to you. All of the other results are discarded.

A filter expression is applied after a Query finishes, but before the results are returned. Therefore, a Query will consume the same amount of read capacity, regardless of whether a filter expression is present.

A Query operation can retrieve a maximum of 1 MB of data. This limit applies before the filter expression is evaluated.

A filter expression cannot contain partition key or sort key attributes. You need to specify those attributes in the key condition expression, not the filter expression. https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Query.html

To wrap-up:

• if e-mail is your partition key, you cannot apply contains on it - you have to query it directly.
• eventually you can do a scan over your table and apply filter on it (https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_Scan.html) but I wouldn't do that because of consumed capacity of the table and response time. Scan involves operating over all rows in the table, so if you have kind of hundreds of GB, you will likely not get the information in real-time. And real-time serving is one of purposes of DynamoDB.