Joining some fields of different document types on same bucket

SQL++
1

Hey guys, i have the following situation:

Doc A:

{cbType: "typeA", id: "xyz", url: "xyz.de"}

Doc B:

{cbType: "typeB", id: "xyz", title: "Hello World"}

Both in the same bucket foo

I would like to get the following as result:

{id: "xyz", url: "xyz.de", title: "Hello World"}

DocB.id references DocA.id field. I tried to join them, but wasn’t succesful:

SELECT a.id, a.url, b.title
FROM foo a JOIN foo b ON KEYS b.id
WHERE a.cbType = "typeA" AND b.cbType = "typeB"
LIMIT 5;

This query gives me an “Ambiguous reference to field b.” error. I’m new to Couchbase, sorry if this is a stupid question :smiley: Thanks for your help, it is much appreciated

2

JOINS or LEFT to RIGH. ON KEYS can only reference expression LEFT of JOIN keyspace.

SELECT a.id, a.url, b.title
FROM foo b JOIN foo a ON KEYS b.id
WHERE a.cbType = "typeA" AND b.cbType = "typeB"
LIMIT 5;
3

Thanks for your answer. Unfortunately, this gives me an empty result set…is it a problem that they both have the same id?

4

That means you don’t have any relation ship or nothing qualified. Post the both documents with corresponding keys. Doc B.id must be document key of A.

Example: dockey is “docA” document {cbType: “typeA”, id: “xyz”, url: “xyz.de”}
dockey is “docB” document {cbType: “typeB”, id: “docA”, title: “Hello World”}

Otherwise in your model Use ANSI JOIN In 5.5

SELECT a.id, a.url, b.title
FROM foo b JOIN foo a ON  b.id = a.id
WHERE a.cbType = "typeA" AND b.cbType = "typeB"
LIMIT 5;