N1QL for merge 3 records

SQL++
1

I have 3 separate documents as below

Doc one:

{
  "type":"android"
  "Data":{
    "key1":"value1",
    "key2":"value2"
  }
}

Doc 2:

{
  "type":"ios"
   "Data":{
      "key1":"value3",
      "key2":"value4"
  }
}

{
    "type": "key-meta-data",
        "keyMetaData": [{
            "keyName": "key1",
            "features": [
              "CLI",
              "Transection",
              "Statements"
            ]
        },
        {   
            "keyName": "key2",
            "features": [
              "CLI",
              "Transection",
              "Statements",
              "Reward"
            ]
        }]
}

How to merge the records , my output should be

{"type": "key-meta-data",
  "keyMetaData": [
    {
        "android":"value1" ,
        "ios":"value3" ,
        "keyName": "key1",
        "features": [
            "CLI",
        "Transection",
        "Statements"
        ]
    },
    {
        "android":"value2" ,
        "ios":"value4" ,
        "keyName": "key2",
        "features": [
            "CLI",
        "Transection",
        "Statements",
        "Reward"
        ]
    }
  ] 
}
2

What is relation. How it is related to merge.

3

key1 and key2 are the relation

4

Could you please explain more details. What i can see is every array object is added “android”:"", “ios”:"" is that right?

5

We have to maintain key data in 3 documents . Each key is having android value in android document , ios value in ios document and some other information in key-meta-data document . I need to join all the three documents and make the list of keys with android value,ios value ,features .

6

Hi @venkata84.joga. It’s a bit hard to see exactly how the merge is done due to the formatting. But this may be a task that’s easier to solve with the KV API than with N1QL. Something like this would get you started with your merge if you’re using Java:

    JsonDocument android = bucket.get(androidDocId);
    JsonDocument ios = bucket.get(iosDocId);
    JsonDocument metadata = bucket.get(metadataDocId);

    JsonDocument out = JsonDocument.create(finalDocId,
            JsonObject.create()
                    .put("type", "key-meta-data")
                    .put("keyMetaData", JsonArray.create()
                            .add(JsonObject.create()
                            .put("android", android.content().getString("key1"))
                            .put("ios", ios.content().getString("key3")))
                    )
    );
    
    bucket.insert(out);

The KV API docs are here, including the other supported languages.

7 
INSERT INTO default VALUES("a1",{ "type":"android", "Data":{ "key1":"value1","key2":"value2" } }),
                          ("i1",{ "type":"ios", "Data":{ "key1":"value3","key2":"value4" } }),
                          ("m1",{"type": "key-meta-data", "keyMetaData": [ { "keyName": "key1", "features": [ "CLI", "Transection", "Statements" ] }, { "keyName": "key2", "features": [ "CLI", "Transection", "Statements", "Reward" ] }] });

SELECT m.type, ARRAY OBJECT_PUT(OBJECT_PUT(v,i1.type,i1.Data.[v.keyName]),a1.type,a1.Data.[v.keyName]) FOR v IN m.`keyMetaData` END AS `keyMetaData` FROM default AS m USE KEYS ["m1"]
LET a1 = (SELECT RAW a FROM default AS a USE KEYS ["a1"])[0],
    i1 = (SELECT RAW i FROM default AS i USE KEYS ["i1"])[0];
1 Like
8

Hi Thank you for your quick response . I appreciate the same .
This looks awesome for me . But is there any way with out hard coding a1 and i1 document names.
Because in future i can add one more document like w1 for web. and the value also should come in the output.

9

a1 and i1 just variables in query. it never appear in your output. If you want replaces with actual expression.

10

I am talking about USE KEYS [“a1”]. In future if i have another document like w1 as below

{
“Data”: {
“bodyBackground”: “#zzz”,
“bodyColor”: “#zzz”
},
“type”: “web”
}

By that time output should be
[
{
“keyMetaData”: [
{
“defaultValue”: “login”,
“features”: [
“CLI”,
“Transection”,
“Statements”
],
“isValueRequired”: “True”,
“key-data-android”: “#ffffff”,
“key-data-ios”: “#0000”,
“key-data-web”: “#zzzz”,
“keyName”: “bodyBackground”,
“type”: “image”
},
{
“defaultValue”: “#434343”,
“edit”: false,
“features”: [
“CLI”,
“Transection”,
“Statements”,
“Reward”
],
“groupName”: “style”,
“isValueRequired”: “true”,
“key-data-android”: “#525252”,
“key-data-ios”: “#00000”,
“key-data-web”: “#zzz”,
“keyName”: “bodyColor”,
“type”: “hex”
}
],
“type”: “key-meta-data”
}
]

11

If you don’t want USE KEYS use search criteria.

   SELECT m.type, (SELECT d.*, (OBJECT v.type:v.Data.[d.keyName] FOR v IN a1 END).* FROM m.`keyMetaData` AS d WHERE ISSTRING(d.keyName))  AS `keyMetaData`
    FROM default AS m USE KEYS ["m1"]
    LET a1 = (SELECT RAW a FROM default AS a WHERE a.type IN ["android","ios","web"]);

OR

SELECT m.type,
       (SELECT d.*,
               (OBJECT v.type:v.Data.[d.keyName] FOR v IN
                     (SELECT RAW a
                      FROM default AS a
                      WHERE a.type IN ["android","ios", "web"]) END).*
       FROM m.`keyMetaData` AS d 
       WHERE ISSTRING(d.keyName)) AS `keyMetaData`
FROM default AS m USE KEYS ["m1"];
12

Perfect ! thank you very much :grinning:

13

I am really impressed with this query. Could you please do one change here. Out put show be as below. Need to place the ios, android and web values in side a array. Please help me in this

[
{

“keyName”:“bodyBackground”,
“defaultValue”: “#222222”,
“features”: [
“CLI”
],
“isValueRequired”: true,

“values”:{

“android:”#ffffff",

“ios”:“#0000”

},
“type”: “image”
} ]

14 
SELECT m.type,
       (SELECT d.*,
               (OBJECT v.type:v.Data.[d.keyName] FOR v IN
                     (SELECT RAW a
                      FROM default AS a
                      WHERE a.type IN ["android","ios", "web"]) 
                 WHEN ISSTRING(d.keyName) END) AS `values`
       FROM m.`keyMetaData` AS d ) AS `keyMetaData`
FROM default AS m USE KEYS ["m1"];

keyName is not a string values sub-object will not be present.

FYI: values is reserved keyword you need to use back-ticks

15

Thank you for your support!