N1QL for merge 3 records

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"
        ]
    }
  ] 
}

What is relation. How it is related to merge.

key1 and key2 are the relation

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

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 .

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.

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

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.

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

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”
}
]

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"];

Perfect ! thank you very much :grinning:

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”
} ]

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

Thank you for your support!