Iterating Object of Array in Couchbase

Question

How to iterate Object of Array to get specific key value pair from JSON object. I am using Couchbase Server. Please suggest N1QL for Couchbase.

Below is my JSON format

{
 "A":{
      "B":{
           "C": {
                 "D":[ 
                       {
                         "attr":[
                                  { 
                                   "Name" : "abc"
                                  }
                          ],
                          "Name" : "outer"                         
                        }
                                              
                      ], ​
                 "E":[
                        {
                         "attr":[
                                  { 
                                   "Name" : "xyz"
                                  }
                          ],
                          "Name" : "outer1"                         
                        }
                     ​]
                }
           }
      }
 }
       ​      

Want to retrieve value for Name attribute inside each array of object C. Please note there are two fields with same name inside Array , so want to just access outer one. Key value pair inside C is dynamic.

Thanks in Advance!

Answer 1

If you really looking for all the names objects of path A.B.C use the following expression

ARRAY v.Name FOR v WITHIN A.B.C WHEN v.Name IS NOT MISSING END


SELECT ARRAY v.Name 
       FOR v WITHIN d.A.B.C 
       WHEN v.Name IS NOT MISSING
       END AS val 
FROM [{ "A":{ "B":{ "C": { "D":[{"Name" : "test1", "state" : "state1"} ], 
                           "E":[{"Name" : "test2", "state" : "state2"} ], 
                           "F":[{"Name" : "test2", "state" : "state2"} ] 
                          } 
                   } 
             } 
       }
     ] AS d;

    {
        "val": [
            "test1",
            "test2",
            "test2"
        ]
    }

If you need to pick only some you need to have some other field present in that object consistently.

ARRAY v.Name FOR v WITHIN A.B.C WHEN v.Name IS NOT MISSING AND v.attr IS NOT MISSING END

SELECT ARRAY v.Name FOR v WITHIN d.A.B.C WHEN v.Name IS NOT MISSING AND v.attr IS NOT MISSING END
FROM [{ "A":{ "B":{ "C": { "D":[ { "attr":[ { "Name" : "abc" } ], "Name" : "outer" } ], "E":[ { "attr":[ { "Name" : "xyz" } ], "Name" : "outer1" } ] } } } }] AS d;

If you already know the predefined path (don't use recursive). Construct ARRAY of the values of the dynamic field object. As the value is already ARRAY flatten by 1 level, Then iterate over ARRAY and get Name. If you need unique use ARRAY_DISTINCT()

ARRAY v.Name FOR v IN  ARRAY_FLATTEN((ARRAY nv FOR n:nv IN d.A.B.C END),1) END

SELECT ARRAY v.Name FOR v IN  ARRAY_FLATTEN((ARRAY nv FOR n:nv IN d.A.B.C END),1) END AS obj
FROM [{ "A":{ "B":{ "C": { "D":[ { "attr":[ { "Name" : "abc" } ], "Name" : "outer" } ], "E":[ { "attr":[ { "Name" : "xyz" } ], "Name" : "outer1" } ] } } } }] AS d;