Merge two child documents

SQL++
1

Hi
I have two documents as belwo

client::c1
{
“type”: “client”,
“versions”: [
“1.0”,
“1.1”,
“1.2”
]
}

client::c2
{
“type”: “client”,
“versions”: [
“1.0”
]
}

{
“type”: “versions”,
“data”: {
“1.0”: [
“1.0.1”,
“1.0.2”
],
“1.1”: [
“1.1.1”,
“1.1.2”
]
}
}

How to merge the records like below

{[
{“clientName”: “c1”,
“versions”: [
“1.0”,
“1.0.1”,
“1.0.2”
“1.1”,
“1.1.1”,
“1.1.2”
“1.2”

]},
{“clientName”: “c2”,
“versions”: [
“1.0”,
“1.0.1”,
“1.0.2”

]}
}

2 
SELECT SUBSTR(META().id,8) AS clientName,
       ARRAY_FLATTEN(ARRAY ARRAY_CONCAT([v],IFMISSINGORNULL(v1.data.[v],[])) FOR v IN versions END,1) AS versions
FROM default
LET v1 = (SELECT RAW d FROM default AS d WHERE type = "versions")[0]
WHERE type = "client";

OR

SELECT ARRAY_AGG({"clientName":SUBSTR(META().id,8) ,
       "versions":ARRAY_FLATTEN(ARRAY ARRAY_CONCAT([v],IFMISSINGORNULL(v1.data.[v],[])) FOR v IN versions END,1) } ) AS doc
FROM default
LET v1 = (SELECT RAW d FROM default AS d WHERE type = "versions")[0]
WHERE type = "client";
3

Thanks for your response .

Is it possible to get the documents like below

{[
{“clientName”: “c1”,
“versions”: [
{versionName :“1.0”,“type”:“parent/major”},
{versionName :“1.0.1”,“type”:“child/minor”},
{versionName :“1.0.2”,“type”:“child/minor”},
{versionName :“1.1”,“type”:“parent/major”},
{versionName :“1.1.1”,“type”:“child/minor”},
{versionName :“1.1.2”,“type”:“child/minor”},
{versionName :“1.2”,“type”:“parent/major”}

]},
{“clientName”: “c2”,
“versions”: [
{versionName :“1.0”,“type”:“parent/major”},
{versionName :“1.0.1”,“type”:“child/minor”},
{versionName :“1.0.2”,“type”:“child/minor”}

]}
}

4 
SELECT ARRAY_AGG({"clientName":SUBSTR(META().id,8) ,
       "versions":ARRAY_FLATTEN(ARRAY ARRAY_CONCAT([{"versionName":v,"type":"parent/major"}],
                                                   ARRAY {"versionName":v2,"type":"child/minor"}
                                                   FOR v2 IN IFMISSINGORNULL(v1.data.[v],[]) END)
                                FOR v IN versions END,1) } )
FROM default
LET v1 = (SELECT RAW d FROM default AS d WHERE type = "versions")[0]
WHERE type = "client";
5

Thank you !

I have one more issue .

Is it possible to get records from 2nd(version) document as below

[
{
“type”: “major”,
“versionName”: “1.0”
},
{
“type”: “minor”,
“versionName”: “1.0.1”
},
{
“type”: “minor”,
“versionName”: “1.0.2”
},
{
“type”: “major”,
“versionName”: “1.1”
},
{
“type”: “minor”,
“versionName”: “1.1.1”
},
{
“type”: “minor”,
“versionName”: “1.1.2”
},
{
“type”: “major”,
“versionName”: “1.2”
}
]

6 
SELECT ARRAY_AGG({"clientName":SUBSTR(META().id,8) ,
       "versions":ARRAY_FLATTEN(ARRAY ARRAY_CONCAT([{"versionName":v,"type":"major"}],
                                                   ARRAY {"versionName":v2,"type":"minor"}
                                                   FOR v2 IN IFMISSINGORNULL(v1.data.[v],[]) END)
                                FOR v IN versions END,1) } )
FROM default
LET v1 = (SELECT RAW d FROM default AS d WHERE type = "versions")[0]
WHERE type = "client";
7

thank you very much :slight_smile: