Cosmo DB Array contains BOTH values

Question

I have a simple JSON document DB in Cosmo where I want to extract all 'Drills' that have a target of value 1 AND value 2.

Example of JSON:

{
    "drill": {
        ...
        "targets": [
            "Static Strength",
            "Power"
        ],
        ...
    }
},
{
    "drill": {
        ...
        "targets": [
            "Power",
            "Coordination"
        ],
        ...
    }
},
{
    "drill": {
        ...
        "targets": [
            "Body Tension",
            "Static Strength",
            "Power"
        ],
        ...
    }
},...

In the example above, lets say I want to get all 'Drills' where the value of Power AND Static Strength are present in Targets. How would I do that?

If I use ARRAY_CONTAINS(["Power","Static Strength"], t) I get all places where EITHER of the values are present in the Targets array.

I realize this might be a simple ask but im really stumped on it. Thanks in advance!

Answer 1

You can simply use AND in your filter to add an expression for each value.

SELECT * 
FROM c
WHERE 
    ARRAY_CONTAINS(c.drill.targets, 'Power') AND 
    ARRAY_CONTAINS(c.drill.targets, 'Static Strength')

Note that ARRAY_CONTAINS takes the array as first parameter and in the second parameter the value you want to have included inside the array.