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 :)

Answer 1

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;