Query embedded document with where clause - COSMOS SQL API

Question

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?

Steve Johnson · Accepted Answer · 2020-12-31 03:58:58Z

1

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.

answered Dec 31, 2020 at 3:58

Steve Johnson

8,8081 gold badge11 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Query embedded document with where clause - COSMOS SQL API

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related