CODEWITHSUNDEEP
azure-cosmosdbazure-cosmosdb-sqlapi
VR1256
VR1256

Reputation: 1450

Query embedded document with where clause - COSMOS SQL API

I am trying to query document which is embedded to get the results as follows:

Document:

{
    "id": "1",
    "description": "coco cola",
    "locations": [
        { "locationId": "14", "taxType" : "20", "state": "Florida" },
        { "locationId": "14", "taxType" : "22", "state": "Florida" },
        { "locationId": "16", "taxType" : "23", "state": "California" }
      ]  
},
{
    "id": "2",
    "description": "Mac & Cheese",
    "locations": [
        { "locationId": "22", "taxType" : "31", "state": "Texas" },
        { "locationId": "16", "taxType" : "23", "state": "California" }
      ]  
}

I am trying to query above document using SQL query:

SELECT * FROM c
where c.id= "1" and ARRAY_CONTAINS(c.locations,{"locationId": "14"},true)

Expected output: (as we are using where locationId = 14 we need to ignore locationId = 16 for Id = 1):

{
    "id": "1",
    "description": "coco cola",
    "locations": [
        { "locationId": "14", "taxType" : "20", "state": "Florida" },
        { "locationId": "14", "taxType" : "22", "state": "Florida" }
      ]  
}

ARRAY_CONTAINS doesn't work and I am not getting expected output, it gives me empty result when I use above query. How can I query with where clause on the embedded documents?

Upvotes: 0

Views: 142

Answers (1)

Steve Johnson
Steve Johnson

Reputation: 8660

I think your expected output can't be achieved by SQL. You can use UDF and pay attention to it's cost. If you think cost of UDF is too expensive, you can transform this on your client side.

Upvotes: 1

Related Questions