Cosmos DB SQL query in single embed document

Question

I am working with Cosmos DB and I want to write a SQL query that returns multiple document in one single embed documents.

To elaborate, imagine you have the following two document types in one container. OrderId of Order document has reference in OrderDetail document.

1.Order

{ 
    "OrderId": "31d4c08b-ee59-4ede-b801-3cacaea38808", 
    "Name": "ABC", 
    "Type": "Order", 
    "DeptName": "ABC", 
    "TotalAmount": 100.05 
}

2.OrderDetail

{ 
    "OrderDetailId": "689bdc38-9849-4a11-b856-53f8628b76c9", 
    "OrderId": "31d4c08b-ee59-4ede-b801-3cacaea38808", 
    "Type": "OrderDetail", 
    "ItemNo": 202, 
    "Quantity": 10, 
    "UnitPrice": 10.05 
}, 

{ 
    "OrderDetailId": "789bdc38-9849-4a11-b856-53f8628b76c9", 
    "OrderId": "31d4c08b-ee59-4ede-b801-3cacaea38808", 
    "Type": "OrderDetail", 
    "ItemNo": 200, 
    "Quantity": 11, 
    "UnitPrice": 15.05 
}

I want to write a query that will return all entries of OrderDetail in one array based on reference OrderId="31d4c08b-ee59-4ede-b801-3cacaea38808"

Output should be like below

{
   "OrderId":"31d4c08b-ee59-4ede-b801-3cacaea38808",
   "Name":"ABC",
   "Type":"Order",
   "OrderDetail":[
      {
         "OrderDetailId":"689bdc38-9849-4a11-b856-53f8628b76c9",
         "Type":"OrderDetail",
         "ItemNo":202,
         "Quantity":10,
         "UnitPrice":10.05
      },
      {
         "OrderDetailId":"789bdc38-9849-4a11-b856-53f8628b76c9",
         "Type":"OrderDetail",
         "ItemNo":200,
         "Quantity":11,
         "UnitPrice":15.05
      }
   ]
}

I have no idea how to query in Cosmosdb to get the above result.

Jay Gong · Accepted Answer · 2019-12-13 02:17:10Z

1

Your desired output should be applied in relational database,Cosmos db is non-relational db which is not appropriate for your scenario. Per my knowledge, no query sql could produce above output directly.

I suggest you executing 2 sqls, one produces:

{"OrderId":"31d4c08b-ee59-4ede-b801-3cacaea38808",
   "Name":"ABC",
   "Type":"Order"}

other one produces:

"OrderDetail":[
      {
         "OrderDetailId":"689bdc38-9849-4a11-b856-53f8628b76c9",
         "Type":"OrderDetail",
         "ItemNo":202,
         "Quantity":10,
         "UnitPrice":10.05
      },
      {
         "OrderDetailId":"789bdc38-9849-4a11-b856-53f8628b76c9",
         "Type":"OrderDetail",
         "ItemNo":200,
         "Quantity":11,
         "UnitPrice":15.05
      }
   ]

Then combine them. Surely,you could do such process in Stored Procedure.

answered Dec 13, 2019 at 2:17

Jay Gong

23.8k2 gold badges30 silver badges32 bronze badges

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

1 Comment

Gaurav Mantri Over a year ago

@JayGong...I am wondering if your answer is still correct? Also, it would be great if you can elaborate how can one accomplish this in a stored procedure as mentioned in your answer. Thanks.

Collectives™ on Stack Overflow

Cosmos DB SQL query in single embed document

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related