CODEWITHSUNDEEP
azure-cosmosdb
Azutanguy
Azutanguy

Reputation: 141

How to merge lists of one document CosmosDb

I have a document which has 2 list attributes

{
   CurrentDocument:[
      { DocName: "name1", DocType: "Identity" },
      { DocName: "name2", DocType: "Authorization" }
   ],
   ClosedDocument:[
      { DocName: "name3", DocType: "Passport" }
   ]
}

I want to have a query that return DocName & DocType of my two lists. I can't use Join because if one of the list is empty, my query return nothing. Furthermore, in case of a join, I can't merge all my attributes in one list.

SELECT cur.DocName AS curName, clo.DocName AS cloName FROM c JOIN cur IN c.CurrentDocument JOIN clo IN c.ClosedDocument

This query is not what I'm looking for cause :

I tried using the Union expression, but i can't seem to make it work in a query.

Thanks in advance.

Upvotes: 0

Views: 397

Answers (1)

Hasan Savran
Hasan Savran

Reputation: 393

Use UDF for this. Create the following UDF

function userDefinedFunction(current, closed){
return current.concat(closed);}

Use it in your query

SELECT udf.MergeLists(o.CurrentDocument, o.ClosedDocument) as merged FROM Orders o WHERE o.id = 'a811d13f-a308-4df1-85c1-31e566e9fc1e'

This returns the following

enter image description here

Upvotes: 1

Related Questions