Couchbase n1ql - NEST foreign document using indexes

Question

I'm trying to create a N1QL query that would return a list of clients and embed as well an array of API keys. Here is my setup:

Bucket:

{
    "Client-a3c1c7f8r89732": {
        "type": "Client",
        "id": "a3c1c7f8r89732",
        "name": "Cognito Inc"
    },
    "Client-z6cc6f4r6feer5": {
        "type": "Client",
        "id": "z6cc6f4r6feer5",
        "name": "Some Corp"
    },
    "ApiKey-3846516546897987": {
        "type": "ApiKey",
        "key": "3846516546897987",
        "client_id": "a3c1c7f8r89732"
    },
    "ApiKey-98765164865351321": {
        "type": "ApiKey",
        "key": "98765164865351321",
        "client_id": "a3c1c7f8r89732"
    },
    "ApiKey-646546846546549887": {
        "type": "ApiKey",
        "key": "646546846546549887",
        "client_id": "a3c1c7f8r89732"
    }
}

Indexes available:

CREATE INDEX `idx-client-query` ON `primary`(`type`,`name`,`id`) WHERE (`type` = "Client")
CREATE INDEX `idx-apikey-query` ON `primary`(`type`,`client_id`,`key`) WHERE (`type` = "ApiKey")

Desired N1QL query output:

[
    {
        "id": "a3c1c7f8r89732",
        "name": "Cognito Inc",
        "api_keys": [
            {
                "key": "3846516546897987"
            },
            {
                "key": "98765164865351321"
            },
            {
                "key": "646546846546549887"
            }
        ]
    },
    {
        "id": "z6cc6f4r6feer5",
        "name": "Some Corp",
        "api_keys": []
    }
]

So far I've tried

To use an ANSI RIGHT OUTER JOIN to retrieve clients who do not have api keys (the query result would need to be reprocessed to nest the keys). For some reason, it only retrieves the clients that do have keys.
To use an ANSI NEST such as below but I simply get no results

SELECT client.name, client.id, api_key.* as api_keys
FROM `primary` client
INNER NEST `primary` api_key ON client.id = api_key.client_id
WHERE client.type = 'Client' AND api_key.type = 'ApiKey'

Any help or link to a similar problem would be much appreciated, thanks :)

After NEST api_key becomes ARRAY (so you can't do api_key.*) Check out Example 17 of https://blog.couchbase.com/ansi-join-support-n1ql/ — vsr, Nov 08 '21 at 19:57

vsr · Accepted Answer · 2021-11-08T20:51:43.097

CREATE INDEX ix1 ON default (id, name) WHERE type = "Client";
CREATE INDEX ix2 ON default (client_id, `key`) WHERE type = "ApiKey";

CB 7.0

Use correlated subquery. Subquery generates ARRAY

SELECT c.id, c.name,
       (SELECT RAW a.`key`
        FROM default AS a
        WHERE a.type = "ApiKey" AND a.client_id = c.id) AS api_keys
FROM default AS c
WHERE c.type = "Client" AND c.id IS NOT NULL;

CB Pre-7.0

In pre 7.0, correlated subquery has restriction (requires USE KEYS). So use JOIN and aggregate (It can use covering on both sides vs NEST will not use covering).

SELECT c.id, c.name, ARRAY_AGG(a.`key`) AS api_keys
FROM default AS c
LEFT JOIN default AS a ON a.type = "ApiKey" AND a.client_id = c.id
WHERE c.type = "Client" AND c.id IS NOT NULL
GROUP BY c.id, c.name;

OR

Try the Hash JOIN with the Hint

apis WITH (SELECT a1.client_id, ARRAY_AGG(a1.`key`) AS api_keys
           FROM default AS a1
           WHERE a1.type = "ApiKey" AND a1.client_id IS NOT NULL
           GROUP BY a1.client_id)
SELECT c.id, c.name, a.api_keys
FROM default AS c
JOIN apis AS a ON a.client_id = c.id
WHERE c.type = "Client" AND c.id IS NOT NULL;

If the data is reasonable size

apiobj WITH (OBJECT v.client_id:v.api_keys
             FOR v IN (SELECT a1.client_id, ARRAY_AGG(a1.`key`) AS api_keys
                       FROM default AS a1
                       WHERE a1.type = "ApiKey" AND a1.client_id IS NOT NULL
                       GROUP BY a1.client_id)
             END)
SELECT c.id, c.name, apiobj.[c.id] AS api_keys
FROM default AS c
WHERE c.type = "Client" AND c.id IS NOT NULL;

Thanks! The CB Pre-7.0 did it for me – Pierre Roudaut Nov 09 '21 at 13:02 — Pierre Roudaut, Nov 09 '21 at 13:02

score 1 · Answer 2 · edited Nov 07 '21 at 21:19

I think you're on the right track with NEST

Here's what I did:

SELECT c.id, c.name, ARRAY { x.`key` } FOR x IN a END AS api_keys
FROM `prim` c
LEFT NEST `prim` a ON c.id = a.client_id
WHERE c.type = "Client";

I added an index:

CREATE INDEX `adv_client_id` ON `prim`(`client_id`)

You probably want LEFT NEST, since your desired results included a client that has no API keys assigned.

The ARRAY { } syntax will pull just the keys into the result you want, but you could do SELECT c.id, c.name, a AS api_keys . . . and get all the values in the ApiKey documents. But I think you need the new index either way.

Also, you don't need to filter api_key.type, since you're only joining to the API keys.

I tried running your NEST query and I got a syntax error (whereas you said you got no results), so I can't say for sure why it didn't return anything (I'm using Couchbase 7 FYI)

Thank you for your answer! The query suggested partially work but takes several minutes to execute and because of the generic `adv_client_id` index, all documents containing a `client_id` property are also returned. — Pierre Roudaut, Nov 09 '21 at 08:51

Couchbase n1ql - NEST foreign document using indexes

2 Answers2