DynamoDB query by contains one of the values from array for Node.js
Solution 1
You can use CONTAINS function to check for values in the list. However, if you want to check multiple values, you need to use logical OR condition for each value.
CONTAINS is supported for lists: When evaluating "a CONTAINS b", "a" can be a list; however, "b" cannot be a set, a map, or a list.
Example:-
var params = {
TableName : 'tableName',
FilterExpression: "contains (category, :category1) OR contains (category, :category2)",
ExpressionAttributeValues : {
':category1' : "apple",
':category2' : "orange"
}
};
Note:-
I assumed you are going to use Scan API. If you are using Query API, please include the KeyConditionExpression
.
Solution 2
The question asked about Query
, but it seems that it is unclear whether on Scan
is the technology being used. I will answer for each of those parts.
-
Scan
is very expensive and where possibleQuery
should be used.Scan
easily supports usingcontains
to do what you asked.await db.scan({ TableName : 'tableName', FilterExpression: "contains (category, :category1) OR contains (category, :category2)", ExpressionAttributeValues : { ':category1' : "apple", ':category2' : "orange" } }).promise();
Given that Scan is inefficient, it would be better to have a strategy where the rows it the table can be returned using the
Query
.Query
does NOT support theContains
syntax and additionally must run only a singleHASH
value. Two different rows with differentHASH
values cannot be returned in the sameQuery
. Assuming this criteria is met, then a custom index would need to be constructed. There are a couple of ways to do this, depending on the number of categories that you have and if multiple categories can be assigned to reach row. For 1 cat per row, a single indexed column works. Otherwise, we'll need to construct a indexed column that is able to use the one of the other operators.
That column would need to contain a composite value for all the categories of the row, such thatf(column_value, category_value_check) = true
for all possible categories. In order to construct such a composite value, Amazon recommends using a Z-Index.
Skate to Eat
Updated on September 28, 2020Comments
-
Skate to Eat over 3 years
Is it possible to query with an array
[apple, orange]
on category (also an array. showing below) and get data contains values of eitherapple
ororange
?row1 | "category": [apple, orange, banana, watermelon] row2 | "category": [banana, watermelon] row3 | "category": [orange, watermelon]
I expect to get
row1, row3
as a result that contains eitherapple
ororange
.