How can i use "Not in" with ARRAY_CONTAINS in Cosmos DB?

Question

I am New to Cosmos DB. I am retrieving records using SQL API.now i want to add "Not In" in ARRAY_CONTAINS. I did lots of R&D for the same but not able to find out any solutions.

Answer 1

You cannot add "Not In" in ARRAY_CONTAINS. There is not such syntax in Cosmos DB.

If you want to retrieving the records which donot contain some value. You can use NOT EXISTS. For below example

SELECT food.id,
    food.description,
    food.tags,
    food.foodGroup
FROM food 
WHERE NOT EXISTS(SELECT VALUE t FROM t IN food.tags WHERE t.name = 'orange')

Above query will retrive all the food records on the condition "orange" is not in tags. Below is an example from above query result.

{
  "id": "19015",
  "description": "Snacks, granola bars, hard, plain",
  "tags": [
    {
      "name": "snacks"
    },
    {
      "name": "granola bars"
    },
    {
      "name": "hard"
    },
    {
      "name": "plain"
    }
  ],
  "foodGroup": "Snacks"
}

You can go to the Query Playground of Cosmos DB to learn and practice Cosmos DB querying.

Answer 2

This will find all items where the property resolutions (which is an array) does not contain the value "600".

SELECT VALUE root FROM root WHERE (NOT ARRAY_CONTAINS(root["resolutions"], 600))

Answer 3

You can add NOT IN simply like below example:

SELECT
    food.id,
    food.description,
    food.tags,
    food.foodGroup
FROM 
  food    
WHERE food.id
NOT IN ('ID1', 'ID2', 'ID3')

This is tested and will work like a charm.