Query Azure Cosmos for items that have all elements

Let’s say I have couple sample items in my DB that look like this:

Example 1:

{
    'id': 'someid',
    'columns': ['apple', 'banana'],
    'filters': {'car': 'red', 'truck': 'blue'}
}

Example 2:

{
    'id': 'someid',
    'columns': ['apple', 'banana', 'carrots'],
    'filters': {'truck': 'blue', 'car': 'red', 'boat':'green'}
}

Fast forward to the present, I have a new set of columns and of filters that might look like:

newcolumns=['banana','apple'] and newfilters={'truck':'blue', 'car':'red'}

I want to do a query like

select *
from f
where f.columns=newcolumns and f.filters=newfilters

but I don’t care about the order and they have to be a complete match. Neither set can be a super or subset of the other. So in this case my query should return example 1 but not example 2.

As a note, there are two parts to my question, the columns matching is answered by this but the filters field isn’t a list so the syntax isn’t the same.

>Solution :

You can’t really match by doing an equality-comparison on two arrays like you had:

where f.columns=newcolumns and f.filters=newfilters

Instead, use Cosmos DB’s built-in ARRAY_CONTAINS(), combined with ARRAY_LENGTH().

Since columns is a scalar array, it would look something like:

WHERE ARRAY_CONTAINS(c.columns, "banana")
AND ARRAY_CONTAINS(c.columns, "apple")

For filters, that isn’t an array, so… first check to make sure the key is defined, then check if the value is correct:

WHERE IS_DEFINED(c.filters.truck) AND c.filters.truck="blue"
AND IS_DEFINED(c.filters.car) AND c.filters.car="red"

note: probably a good idea to turn your filters into subdocuments, since your current schema is an anti-pattern (using values as keys). Something like:

{
    "filters": [
       { "vehicleType": "truck", "color": "blue" },
       { "vehicleType": "car", "color": "red" }
}

at that point you can compare with ARRAY_CONTAINS() again. Something like:

WHERE ARRAY_CONTAINS(c.filters, { "vehicleType": "truck", "color": "blue"}, true)