Query to get list of members and their group

Mobile Couchbase Lite
1

I have this documents:

[
    {
        "type":"client",
        "clientId": 1
    },
        {
        "type":"client",
        "clientId": 2
    },
        {
        "type":"group",
        "groupId": 1,
        "groupName": "MeTime",
        "clientIds": [1]
    },

]

I want to get list of clients and the group they belong too so final result would be:

{
    "type":"client",
    "clientId": 2,
   "groupId": null
},
{
    "type":"client",
    "clientId": 1,
   "groupId": 1
},

I am really not sure how to go about it…any starting hints?

2

Update: Ignore this post. Did not see the category. As topic says Query assumed n1ql service.

CREATE INDEX ix1 ON default(clientId) WHERE type = "client";
CREATE INDEX ix2 ON default(ALL clientIds, groupId) WHERE type = "group";

SELECT c.*,
       (SELECT DISTINCT RAW g.groupId
        FROM default AS g
        UNNEST g.clientIds AS cId
        WHERE g.type = "group" AND cId = c.clientId) AS groupIds
FROM default AS c
WHERE type = "client" AND c.clientId IS NOT NULL;
3

The above may work for Server, but it uses features that are not available on mobile. There is not a graceful way to accomplish what you want, unfortunately. I’d recommend putting the groupID directly onto the client rather than the other way around, as it is a more horizontal way of doing things. Then you can easily select the information you want, and use joins or aggregations to get other relevant information.

4

that is for server; I am on couchbase lite android.

5

True. But I don’t have control over it sadly; just have the front-end under me.