How to write a SQL query in CosmosDB for a JSON document which has nested/multiple array

Question

I need to write a SQL query in the CosmosDB query editor, that will fetch results from JSON documents stored in Collection, as per my requirement shown below

The example JSON

{
  "id": "abcdabcd-1234-1234-1234-abcdabcdabcd",
  "source": "Example",
  "data": [
    {
      "Laptop": {
        "New": "yes",
        "Used": "no",
        "backlight": "yes",
        "warranty": "yes"
      }
    },
    {
      "Mobile": [
        {
          "order": 1,
          "quantity": 2,
          "price": 350,
          "color": "Black",
          "date": "07202019"
        },
        {
          "order": 2,
          "quantity": 1,
          "price": 600,
          "color": "White",
          "date": "07202019"
        }
      ]
    },
    {
      "Accessories": [
        {
          "covers": "yes",
          "cables": "few"
        }
      ]
    }
  ]
}

Requirement: SELECT 'warranty' (Laptop), 'quantity' (Mobile), 'color' (Mobile), 'cables' (Accessories) for a specific 'date' (for eg: 07202019)

I've tried the following query

SELECT
c.data[0].Laptop.warranty,
c.data[1].Mobile[0].quantity,
c.data[1].Mobile[0].color,
c.data[2].Accessories[0].cables
FROM c
WHERE ARRAY_CONTAINS(c.data[1].Mobile, {date : '07202019'}, true)

Original Output from above query:

[
    {
        "warranty": "yes",
        "quantity": 2,
        "color": "Black",
        "cables": "few"
    }
]

But how can I get this Expected Output, that has all order details in the array 'Mobile':

[
    {
        "warranty": "yes",
        "quantity": 2,
        "color": "Black",
        "cables": "few"
    },
    {
        "warranty": "yes",
        "quantity": 1,
        "color": "White",
        "cables": "few"
    }
]

Since I wrote c.data[1].Mobile[0].quantity i.e 'Mobile[0]' which is hard-coded, only one entry is returned in the output (i.e. the first one), but I want to have all the entries in the array to be listed out

Hi @JayGong thank you for responding, I've tried your query but it returned only - [ { "quantity": 2, "color": "Black" }, { "quantity": 1, "color": "White" } ] It missed out on 'warranty' and 'cables' — Makkar, Aug 12 '19 at 14:27
@JayGong now I'm trying to implement multiple JOINs, lets suppose in the above example JSON we have more items in Accessories field, I've tried the below query but it gave me no result (0-0) SELECT DISTINCT c.data[0].Laptop.warranty, mobile.quantity, mobile.color, accessories.cables FROM c JOIN data in c.data JOIN mobile in data.Mobile JOIN accessories in data.Accessories WHERE ARRAY_CONTAINS(data.Mobile, {date : '07202019'}, true) OR ARRAY_CONTAINS(data.Laptop, {New : 'yes'}, true) — Makkar, Aug 13 '19 at 15:42
@JayGong, gone through your advice, thanks a lot for clarifying — Makkar, Aug 19 '19 at 14:37

Jay Gong · Accepted Answer · 2019-08-15T01:33:09.670

Please consider using JOIN operator in your sql:

SELECT DISTINCT
c.data[0].Laptop.warranty,
mobile.quantity,
mobile.color,
c.data[2].Accessories[0].cables
FROM c
JOIN data in c.data
JOIN mobile in data.Mobile
WHERE ARRAY_CONTAINS(data.Mobile, {date : '07202019'}, true)

Output:

Update Answer:

Your sql:

SELECT DISTINCT c.data[0].Laptop.warranty, mobile.quantity, mobile.color, accessories.cables FROM c 
JOIN data in c.data JOIN mobile in data.Mobile 
JOIN accessories in data.Accessories 
WHERE ARRAY_CONTAINS(data.Mobile, {date : '07202019'}, true)

My advice:

I have to say that,actually, Cosmos DB JOIN operation is limited to the scope of a single document. What possible is you can join parent object with child objects under same document. Cross-document joins are NOT supported.However,your sql try to implement mutiple parallel join.In other words, Accessories and Mobile are hierarchical, not nested.

I suggest you using stored procedure to execute two sql,than put them together. Or you could implement above process in the code.

Please see this case:CosmosDB Join (SQL API)

How to write a SQL query in CosmosDB for a JSON document which has nested/multiple array

1 Answers1