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

Answer 1

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)