CODEWITHSUNDEEP
.netlinqazure-cosmosdbnosql
DasAmigo
DasAmigo

Reputation: 333

How can I Query DocumentDB with a Hydrating subquery?

if I have 2 documents in my DocumentDB collection

'Document A' 

{ "id": 123, "OtherDocId": 456, "Bdoc" : null }

and 'Document B'

{ "id": 456, "name": "" }

how can I I get Document A so that it looks like this

{ "id": 123, "OtherDocId": 456, "Bdoc" : { "id": 456, "name": "" }}

If it was SQL I could do somthing like

SELECT *, some-sub-query-here AS 'BDOC' from...

I'm not trying to turn a no-sql into a relational DB but I have a regularly updated document (Bdoc) that is associated with some of the documents in the collection and I don't want to have to do 2 round trips to get a document find it's Bdocid and then fetch it's associated Bdoc then put them together in some for loop.

If I do it straight nosql style then the Bdoc is copied in each of the A documents and Bdoc becomes a maintenance mess every time I want to update it since I have to update every document that Bdocid

Upvotes: 0

Views: 479

Answers (1)

Andrew Liu
Andrew Liu

Reputation: 8119

You can't combine two separate documents using the SQL grammar (that effectively is a relational or cross-document join).

If you're looking to avoid 2 network round-trips, you could write a stored procedure that does two separate lookup and transformation.

Upvotes: 1

Related Questions