CODEWITHSUNDEEP
azureazure-storageazure-cosmosdb
Ashish
Ashish

Reputation: 431

Cosmos DB IN Clause thru REST API

I am unable to formulate search query using IN clause for Azure Cosmos document DB

Query

{
  "query": "SELECT * FROM LOADS l WHERE l.schedulingSystem in (@schedulingSystem)",
  "parameters": [
    {
      "name": "@schedulingSystem",
      "value": "A,B"
    }
  ]
}

I have tried providing values in single quotes "value": "'A','B'" as well but did not work. I am using 'x-ms-version', '2018-12-31' header for the query

There is no error response but getting blank response (data do exist for this search criteria).

Any help or pointers are really appreciated.

Upvotes: 3

Views: 12487

Answers (1)

Jay Gong
Jay Gong

Reputation: 23782

If you use a parameterized IN list with sqlQuerySpec, then it will be considered as a single value when the parameter is expanded.

Please use convenient way to write your query is to use ARRAY_CONTAINS instead and pass the list of items as a single parameter. Please adjust your query like this: 

SELECT * FROM LOADS l WHERE ARRAY_CONTAINS(@schedulingSystem, l.schedulingSystem,false)

 "parameters": [
    {
      "name": "@schedulingSystem",
      "value": "['A','B']"
    }
  ]

Similar question for your references:

1.https://github.com/Azure/azure-cosmos-dotnet-v2/issues/614

2.WHERE IN with Azure DocumentDB (CosmosDB) .Net SDK

Upvotes: 6

Related Questions