Merge two documents

SQL++
1

Hi I have two documents like below

{
“config”: {
“key1”: “value1”,
“key2”:“value2”,
“key3”: “value3”
},
“type”: “config-ios”
}

{
“type”: “key-meta-data”,
“keyMetaData”: [
{
“keyName”: “key1”,
“type”:“string”
},
{
“keyName”: “key2”,
“type”:“html”
}
{
“keyName”: “key1”,
“type”:“boolean”
}
]

Output like below

{
“content”:{
“key1”: “value1”,
“key2”:“value2”,
},
“style”:{
“key3”:“value3”,
}
}

content is grouping for type string and html
style is group for boolean

2 
INSERT INTO default VALUES("c1", { "config": { "key1": "value1", "key2":"value2", "key3": "value3" }, "type": "config-ios" });
INSERT INTO default VALUES("k1", { "type": "key-meta-data", "keyMetaData": [ { "keyName": "key1", "type":"string" }, { "keyName": "key2", "type":"html" }, { "keyName": "key3", "type":"boolean" } ] });


SELECT (OBJECT v1.grp:v1.o FOR v1 IN
       ( SELECT  d.grp,
                 OBJECT v.name:v.val FOR v IN a END AS o
         FROM ARRAY_FLATTEN(( SELECT  RAW ARRAY {"o":{v.keyName:cfg.[v.keyName]},
                                                 "grp": CASE WHEN v.type IN ["string","html"]
                                                             THEN "content" WHEN v.type IN ["boolean"]
                                                             THEN "style" ELSE "others"
                                                             END }
                                          FOR v IN km.keyMetaData
                                          WHEN v.keyName IS NOT MISSING
                                          END
                              FROM default AS km
                              LET cfg = (SELECT RAW c.config FROM default AS c WHERE c.type = "config-ios")[0]
                             WHERE km.type = "key-meta-data"),1) AS d
       GROUP BY d.grp
       LETTING a = ARRAY_FLATTEN(ARRAY_AGG(OBJECT_PAIRS(d.o)),2)) END).*
;
3

INSERT INTO default VALUES(“c1”, { “config”: { “key1”: “value1”, “key2”:“value2”, “key3”: “value3” }, “type”: “config-ios” });
INSERT INTO default VALUES(“c1”, { “config”: { “key1”: “value4”, “key2”:“value5”, “key3”: “value6” }, “type”: “config-android” });
INSERT INTO default VALUES(“k1”, { “type”: “key-meta-data”, “keyMetaData”: [ { “keyName”: “key1”, “type”:“string” }, { “keyName”: “key2”, “type”:“html” }, { “keyName”: “key3”, “type”:“boolean” } ] });

Output

[{
“content”:{
“key1”: “value1”,
“key2”:“value2”,
},
“style”:{
“key3”:“value3”,
},
“type”:“ios”
},
{
“content”:{
“key1”: “value4”,
“key2”:“value5”,
},
“style”:{
“key3”:“value6”,
},
“type”:“android”
}
]

How to pass cfg value dynamically in the above query

4

You can use query parameter or named parameter if you want pass it as dynamically.

5

Could you please let me know what is wrong in the below query for the above output

SELECT ARRAY (OBJECT v1.grp:v1.o FOR v1 IN
( SELECT d.grp,
OBJECT v.name:v.val FOR v IN a END AS o
FROM ARRAY_FLATTEN(( SELECT RAW ARRAY {“o”:{v.keyName:cfg.[v.keyName]},
“grp”: CASE WHEN v.type IN [“string”,“html”]
THEN “content” WHEN v.type IN [“boolean”]
THEN “style” ELSE “others”
END }
FOR v IN km.keyMetaData
WHEN v.keyName IS NOT MISSING
END
FROM default AS km
LET cfg = cfg1
WHERE km.type = “key-meta-data”),1) AS d
GROUP BY d.grp
LETTING a = ARRAY_FLATTEN(ARRAY_AGG(OBJECT_PAIRS(d.o)),2)) END) FOR cfg1 IN (SELECT RAW c.config FROM default AS c WHERE c.type LIKE “config-%”) END

6 
INSERT INTO default VALUES("c1", { "config": { "key1": "value1", "key2":"value2", "key3": "value3" }, "type": "config-ios" });
INSERT INTO default VALUES("c2", { "config": { "key1": "value4", "key2":"value5", "key3": "value6" }, "type": "config-andorid" });
INSERT INTO default VALUES("k1", { "type": "key-meta-data", "keyMetaData": [ { "keyName": "key1", "type":"string" }, { "keyName": "key2", "type":"html" }, { "keyName": "key3", "type":"boolean" } ] });


SELECT d.type, (OBJECT f.tag:(OBJECT v.field:v.fieldval FOR v IN f.val END) FOR f IN fields END).*
FROM ( SELECT type, tag, val
       FROM default AS k
       UNNEST k.keyMetaData AS km
       UNNEST (SELECT RAW c FROM default AS c WHERE c.type LIKE "config%") AS c
       LET tag = (CASE WHEN km.type IN ["string","html"] THEN "content"
                       WHEN km.type IN ["boolean"] THEN "style" ELSE "others"
                  END),
           type = SUBSTR(c.type,7)
       WHERE k.type = "key-meta-data" AND km.keyName IS NOT MISSING
       GROUP BY tag, type
       LETTING val = ARRAY_AGG({"field":km.keyName, "fieldval":c.config.[km.keyName]})) AS d
GROUP BY d.type
LETTING fields = ARRAY_AGG({d.tag, d.val});