CODEWITHSUNDEEP
azureazure-cosmosdbazure-cosmosdb-sqlapi
majewka
majewka

Reputation: 121

Query CosmosDB when document contains Dictionary

I have a problem with querying CosmosDB document which contains a dictionary. This is an example document: 

{
    "siteAndDevices": {
        "4cf0af44-6233-402a-b33a-e7e35dbbee6a": [
            "f32d80d9-e93a-687e-97f5-676516649420",
            "6a5eb9fa-c961-93a5-38cc-ecd74ada13ac",
            "c90e9986-5aea-b552-e532-cd64a250ad10",
            "7d4bfdca-547a-949b-ccb3-bbf0d6e5d727",
            "fba51bfe-6a5e-7f25-e58a-7b0ced59b5d8",
            "f2caac36-3590-020f-ebb7-5ccd04b4412c",
            "1b446af7-ba74-3564-7237-05024c816a02",
            "7ef3d931-131e-a639-10d4-f4dd5db834ca"
        ]
    },
    "id": "f9ef9fb6-4b70-7d3f-2bc8-c3d335018624"
}

I need to get all documents where provided guid is in the list, so in the dictionary value (I don't know dictionary key). I found an information somewhere here that it is not possible to iterate through keys in dictionary in CosmosDB (maybe it has changed since that time but I din't find any information in documentation), but maybe someone will have some idea. I cannot change form of the document.

I tried to do it in Linq, but I didn't get any results.

var query = _documentClient
                .CreateDocumentQuery<Dto>(DocumentCollectionUri())
                .Where(d => d.SiteAndDevices.Any(x => x.Value.Contains("f32d80d9-e93a-687e-97f5-676516649420")))
                .AsDocumentQuery();

Upvotes: 1

Views: 1782

Answers (1)

David Makogon
David Makogon

Reputation: 71055

Not sure of the Linq query, but with SQL, you'd need something like this:

SELECT * FROM c
where array_contains(c.siteAndDevices['4cf0af44-6233-402a-b33a-e7e35dbbee6a'],"f32d80d9-e93a-687e-97f5-676516649420")

This is a strange document format though, as you've named your key with an id:

"siteAndDevices": {
     "4cf0af44-6233-402a-b33a-e7e35dbbee6a": ["..."]
}

Your key is "4cf0af44-6233-402a-b33a-e7e35dbbee6a", which forces you to use a different syntax to reference it:

c.siteAndDevices['4cf0af44-6233-402a-b33a-e7e35dbbee6a']

You'd save yourself a lot of trouble refactoring this to something like:

{
   "id": "dictionary1",
   "siteAndDevices": {
      "deviceId": "4cf0af44-6233-402a-b33a-e7e35dbbee6a",
      "deviceValues": ["..."]
   }
}

You can refactor further, such as using an array to contain multiple device id + value combos.

Upvotes: -1

Related Questions