Is it possible to query with an array [apple, orange] on category (also an array. showing below) and get data contains values of either apple or orange?
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 either apple or orange.
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.
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 possible Query should be used. Scan easily supports using contains 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 the Contains syntax and additionally must run only a single HASH value. Two different rows with different HASH values cannot be returned in the same Query. 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 that f(column_value, category_value_check) = true for all possible categories. In order to construct such a composite value, Amazon recommends using a Z-Index.
