Cosmos DB IN Clause thru REST API

Question

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.

You are directly executing on the document explorer or is this in the code? — Sajeetharan, Jun 21 '19 at 02:09
I suspect that `IN` isn't supported yet. Have a look at this work around https://stackoverflow.com/questions/51229108/use-linq-any-inside-a-linq-where-on-cosmosdb/51229956#51229956 — bit, Jun 21 '19 at 08:35

score 5 · Accepted Answer · answered Jun 21 '19 at 08:31

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']"
    }
  ]

Cosmos DB IN Clause thru REST API

1 Answers1

Linked

Related