Joining and nesting multiple objects under a single parent

SQL++
1

Hello,

I have the two following structures (both are stored in the same bucket, lets call the bucket “data” for now):
KEY: a_12345 (parent object)

{
  "_id": "a_12345",
  "_type": "service"
}

KEY: b_12345

{
  "_id": "b_12345",
  "parent": "a_12345",
  "data": {
    "a": "b",
    "c": "d"
  }
}

KEY: b_12346

 {
  "_id": "b_12346",
  "parent": "a_12345",
  "data": {
    "6": "3",
    "d": "f"
  }
}

I would optimally would like to get a an object where both children (b_12345, b_12346) are nested under the parent object (a_12345).
I am currently using couchbase “4.0.0-4051 Community Edition (build-4051)”
I have N amount of parents, and N amount of children for each parent, I would like to retrieve all parents and all children nested under the parent in a single query.

Any ideas? I have been trying every iteration of Join, group by, and aggregation that I know of.

Thanks!

2

Yes, you can do this.

With 4.1 and above, you can do:

CREATE INDEX idx_parent ON data( parent );
SELECT *
FROM data a NEST data b ON KEY b.parent FOR a;

With 4.0, you can do:

SELECT a, ARRAY_AGG(b) AS b
FROM data b JOIN data a ON KEYS b.parent
GROUP BY a;

3

Hi Gerald,

Thank you very much, the query for version 4.0 works flawlessly.

1 Like