How to query on cosmos database using CreateDocumentQuery in C#

Question

I have the following 2 rows of json data stored as a 2 documents in cosmos db:

{
 "Lists": [ 
   {
      "number":"1", 
      "Name": "Doctor"
   },
   {
      "number":"2",
      "Name": "Lawyer"
   }
 ],
 "Brands": [18,25],                                                
 "type":"Msg"

}

{
 "Lists": [ 
    {
      "number":"3", 
      "Name": "Engineer"
    },
    {
      "number":"4",
      "Name":"Labour"
    }
 ],     
 "Brands": [16,27],
 "type": "Call"
}

How to query to cosmos db collection to filter such that Brands.Contains(16) && Lists.number.Contains(3) i:e 2nd row in the above json. I want to achieve this with help of JObject or any other logic so that without creating a Model Class for the above json I can filter the collection.

Below is the Query logic but how to write the above filter in this below IDocumentQuery?

IDocumentQuery<JObject> query = client.CreateDocumentQuery<JObject>(
            UriFactory.CreateDocumentCollectionUri(Constants.C4CosmosDatabaseId, collectionId),
            _feedOptions)
            .AsDocumentQuery();

Roman Marusyk · Accepted Answer · 2022-03-11 12:12:47Z

1

If you use Microsoft.Azure.Cosmos Version="3.19.0", then try

string number = "3";
int brand = 16;

var query = $"SELECT c.Lists, c.Brands FROM c JOIN t IN c.Lists WHERE CONTAINS(t.number, {number}, false) and ARRAY_CONTAINS(c.Brands, {brand }, false)";

FeedResponse<JObject> response = await _container
   .GetItemQueryIterator<JObject>(new QueryDefinition(query))
   .ReadNextAsync();

answered Mar 11, 2022 at 12:12

Roman Marusyk

24.8k27 gold badges83 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

nagaraja Over a year ago

I tried the above method but I'm getting: "Microsoft.Azure.Cosmos.Query.Core.Exceptions.ExpectedQueryPartitionProviderException" I'm doubting of "CONTAINS" & "ARRAY_CONTAINS" function in SQL Query doesn't support here

Roman Marusyk Over a year ago

Why? What is the error message? Because ExpectedQueryPartitionProviderException doesn't say that "CONTAINS" & "ARRAY_CONTAINS" are not supported. It says something about your partition key. Did you see my question about your partition key? What Cosmos API do you use?

nagaraja Over a year ago

Looks like ARRAY_CONTAINS is working after couple of changes but CONTAINS(t.FranchiseId, {franchiseId}, false) is giving 0 results. Any reason for this?

Roman Marusyk Over a year ago

Check type of t.number, is it string to int? If string, try to add some braces "{number}"

nagaraja Over a year ago

It worked with below query SELECT VALUE S FROM S JOIN F IN S.Lists WHERE F.number={number} AND ARRAY_CONTAINS(S.Brands, {brand}, false). Also I have another ask: I have a string array variable as var types = new string[] { "Call","Msg","MMS"}; I want to check if the json's one of key "type" has any of the above "types"

Collectives™ on Stack Overflow

How to query on cosmos database using CreateDocumentQuery in C#

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related