N1QL Join on Ref Ids

SQL++
,
1

I have two types of documents. Elections and Contests.

For simplicity let’s say Elections look like:
{
doc_type: ‘election’,
name: ‘e1’,
contests: [‘c1’, ‘c2’, …(maybe I have thousands of ref ids here)]
}

and Contests look like:
{ doc_type: 'contest', id: 'c1' }

How can I most efficiently query an Election and instead of getting the array of ref ids, I get an array of the actual objects the ref ids refer to? I assume N1QL will be the most efficient way here? I’ve tried looking but I can’t find the exact query to do this? I want:

{ doc_type: 'election', name: 'e1', contests: [ { doc_type: 'contest', id: 'c1'}, { doc_type: 'contest', id: 'c2' } ] }

I’ve tried

SELECT e
FROM main e
NEST main c ON KEYS e.contests
WHERE e.doc_type = ‘election’ AND e.id = ‘e1’

but no luck…

(Also, is there a better way than doing N1QL?)

2

Your query is correct.

The id’s in your Election document need to be the external id’s (i.e. primary keys) of the contest documents.

3

If I do

SELECT e, c

I see that it resolved those references, but it’s still not replacing ‘contests’ with the value of c?

4

I don’t think you addressed my previous reply.

5

@geraldss Sorry, I am responding to your reply and I made sure to do what you said. But I am still not getting the desired result. The ‘contests’ field in the result still just contains the ids

6

Can you post the following:

SELECT META().id FROM main WHERE doc_type = ‘contest’ LIMIT 10;

SELECT contests FROM main LIMIT 10;

7

I changed the ids in Election to match the ones here:

SELECT META().id FROM main WHERE doc_type = ‘contest’ LIMIT 10;
[
{
“id”: “contest:c1”
},
{
“id”: “contest:c2”
}
]

SELECT contests FROM main LIMIT 10;

[
{},
{},
{},
{},
{},
{},
{},
{},
{},
{
“contests”: [
“contest:c1”,
“contest:c2”
]
}
]

8

Ok. You should also have an index on e.id.

SELECT OBJECT_REMOVE(e, "contests").*, c AS contests
FROM main e
NEST main c ON KEYS e.contests
WHERE e.doc_type = 'election' AND e.id = 'e1'
;
1 Like
9

@geraldss Thank you! What would I need to do to make the same query work, but by instead referencing the Contests in the Elections’ ‘contests’ array by a secondary/non-primary index, such as ‘contest_name’?

I tried

CREATE INDEX c_name ON main (contest_name) USING GSI;

and added the necessary docs in the bucket and then executed the same query but the result is empty

10

N1QL JOIN / NEST currently require that one document be able to produce the primary key of the other document. Are the contest_names related to the contest_id’s? Are the contest_names unique per contest?

Or can the contests reference the elections?

For arbitrary joins, without any restriction on keys or relationships, stay tuned :slight_smile: