select a particular field from nested subdocuments Couchbase

Question

I have in a couchbase bucket documents having this structure:

    "name": {
      "grandfather": {
        "parent1": {
          "child1": [
            {
              .....
              "uid": "value1",
            },
            {
              "uid": "value2",
            }
          ],
        },
        "parent2": {
           "child2"
          [
            {
              "uid": "value3",
            }
          ],
        }
    }
}

I would need a query that returns

{
    {
      "uid": "value1",
    },
    {
      "uid": "value2",
    }
    {
      "uid": "value3",
    }
}  

.. intuitively something like:

select grandfather.*.*.uid from name;

but this one doesn't work. If someone can help, thank you

Answer 1

Across all the documents

WITH doc AS ( { "grandfather": { "parent1": { "child1": [ { "uid": "value1" }, { "uid": "value2" } ],
                                              "child2": [{ "uid": "value3"}]
                                            },
                                 "parent2": { "child1": [ { "uid": "value4" }, { "uid": "value5" } ],
                                              "child2": [{ "uid": "value6"}]
                                            }
                                }
              })
SELECT RAW c1
FROM doc AS a
UNNEST OBJECT_VALUES(a.grandfather) AS p
UNNEST OBJECT_VALUES(p) AS c
UNNEST c AS c1;

One entry per document

WITH doc AS ( { "grandfather": { "parent1": { "child1": [ { "uid": "value1" }, { "uid": "value2" } ],
                                              "child2": [{ "uid": "value3"}]
                                            },
                                 "parent2": { "child1": [ { "uid": "value4" }, { "uid": "value5" } ],
                                              "child2": [{ "uid": "value6"}]
                                            }
                                }
              })
SELECT ARRAY_FLATTEN(ARRAY (ARRAY cv FOR cn:cv IN pv END) FOR pn:pv IN a.grandfather END,3) AS names
FROM doc AS a;