JOIN 3 "bucket" and group by some fields

Couchbase Server
1

Hi, My documents looks like this:

{"id":"P::R::1",type":"profile","name":"p1","status":"active"}
{"id":"P::R::2",type":"profile","name":"p2","status":"inactive"}
{"id":"R::1","type":"relationship","members":["user1"]}
{"id":"R::2","type":"relationship","members":["user1","user2"]}
{"id":"S::1","type":"subset","ref_key":"R::1","set":"set1"}
{"id":"S::2","type":"subset","ref_key":"R::1","set":"set2"}
{"id":"S::3","type":"subset","ref_key":"R::2","set":"set3"}

and all document are in bucket mydb.

what I need :
1 get the result by members,such as where "user1" in members;
2 join profile document with relationship document and join relationship `` document with ``subset` document. 2 group the result by key ofrelationship```.

Following is what I want by where "user1" in members condition:

[
    {
        "id": "R:1",
        "name": "p1",
        "status": "active",
        "setlist": [
            {
                "id": "S::1",
                "set": "set1"
            },
            {
                "id": "S::2",
                "set": "set2"
            }
        ]
    },
    {
        "id": "R:2",
        "name": "p2",
        "status": "inactive",
        "setlist": [
            {
                "id": "S::3",
                "set": "set3"
            }
        ]
    }
]

How should I write one N1QL to get the result?

2

Hi atom_yang,
I assume the ‘id’ attribute is unique, and using it as key for the documents. Following query produces the result you want. Adjust the query appropriately if ‘id’ can’t be used as key for the documents.

cbq> insert into default (key, value)
values (“P::R::1”, {“type”:“profile”,“name”:“p1”,“status”:“active”}),
values (“P::R::2”, {“type”:“profile”,“name”:“p2”,“status”:“inactive”}),
values (“R::1”, {“type”:“relationship”,“members”:[“user1”]}),
values (“R::2”, {“type”:“relationship”,“members”:[“user1”,“user2”]}),
values (“S::1”, {“type”:“subset”,“ref_key”:“R::1”,“set”:“set1”}),
values (“S::2”, {“type”:“subset”,“ref_key”:“R::1”,“set”:“set2”}),
values (“S::3”, {“type”:“subset”,“ref_key”:“R::2”,“set”:“set3”}) returning *;

cbq> SELECT meta(r).id as id, p.name name, p.status status,

ARRAY_AGG({‘id’:meta(s).id, ‘set’:s.set}) as setlist
FROM default s JOIN default r ON KEYS s.ref_key
JOIN default p ON keys (“P::” || s.ref_key)
WHERE s.type = “subset” AND r.type = “relationship”
AND p.type = “profile” AND “user1” IN r.members
GROUP BY meta(r).id, p.name, p.status;
{
“requestID”: “9bbae860-6a14-4311-b27d-ad12e05774cc”,
“signature”: {
“id”: “json”,
“name”: “json”,
“setlist”: “array”,
“status”: “json”
},
“results”: [
{
“id”: “R::1”,
“name”: “p1”,
“setlist”: [
{
“id”: “S::1”,
“set”: “set1”
},
{
“id”: “S::2”,
“set”: “set2”
}
],
“status”: “active”
},
{
“id”: “R::2”,
“name”: “p2”,
“setlist”: [
{
“id”: “S::3”,
“set”: “set3”
}
],
“status”: “inactive”
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “13.034331ms”,
“executionTime”: “13.010672ms”,
“resultCount”: 2,
“resultSize”: 583
}
}
cbq>

1 Like
3

Thank you very much,It works!
using ARRAY_AGG can aggregate data;
using

default s JOIN default r ON KEYS s.ref_key
JOIN default p ON keys ("P::" || s.ref_key)

can JOIN 3 type of documents.
I learned a lot, Thank you!