Difficulty in understanding lookup join

Greetings.

I am from RDBMS background (MS SQL Server to be specific). I am trying to understand Lookup Join using N1QL but no luck.

I am referring two sample queries:

SELECT airline.name, airline.callsign, route.destinationairport, route.stops, route.airline
FROM `travel-sample` route JOIN `travel-sample` airline ON KEYS route.airlineid 
WHERE route.type = "route" 
AND airline.type = "airline" 
AND route.sourceairport = "SFO" 
AND route.stops = 0
LIMIT 4;

In travel-sample bucket, there is no field by name “airlineid” in documents with type = “airline”. Then how route is mapped with airline?

In another example,

select br.name brewery, b.name beer, b.style style 
from `beer-sample` b join `beer-sample` br on keys b.brewery_id 
where br.name = "Yards Brewing" order by beer, style;

We have brewery_id field in document with type = beer but no mapping field in document with type = brewery.
How Couchbase server does this mapping?

I am trying to get N1QL equivalent of “select * from beer b join brewery br on b.brewery_id = br.brewery_id”

Your help is much appreciated.

Thanks in advance.

Regards,
Satish

JOINS in N1QL are LEFT to RIGHT

ON KEYS expression (route.airlineid) value matches with document of right side keyspace (i.e airline document key)

Please checkout https://dzone.com/articles/visually-explaining-n1ql-joins

In 5.5.0-DP we haveANSI JOIN

Thanks for your quick reply.

Right side keyspace is document with type = “airline”.
When I execute query:
select * from travel-sample where type = "airline" limit 1

I get these fields as columns: callsign, country, iata, icao, id, name, type. There is no field airlineid in this list which matches with route.airlineid

Is it that this key is not returned as part of SELECT and there is different way to know this field?

I tried running query: select META().id from travel-sample
But it gives result as:

{
“id”: “airline_10”
},
{
“id”: “airline_10123”
},
.
.
.

Here also field is just “id” and not “airlineid”.

ON KEYS route.airlineid

Evaluate ON KEYS expression and gets all the documents that matched with document key of right side of key spaces and joins them. If it is scalar field it single document key. If it is array each element of array consider document key.

In following query i projected route.airlineid, META(airline).id airline_dockey, META(route).id route_dockey
In this case airline_dockey matches with route.airlineid

META(route).id is document key and it is not part of the document. If you need to get the document key you need to reference explicitly.

SELECT airline.name, airline.callsign, route.destinationairport, route.stops, route.airline,
route.airlineid, META(airline).id airline_dockey, META(route).id route_dockey
FROM `travel-sample` route JOIN `travel-sample` airline ON KEYS route.airlineid
WHERE route.type = "route" AND airline.type = "airline" AND route.sourceairport = "SFO" AND route.stops = 0 LIMIT 4;

"results": [
        {
            "airline": "DL",
            "airline_dockey": "airline_2009",
            "airlineid": "airline_2009",
            "callsign": "DELTA",
            "destinationairport": "SLC",
            "name": "Delta Air Lines",
            "route_dockey": "route_21762",
            "stops": 0
        },
        {
            "airline": "FL",
            "airline_dockey": "airline_1316",
            "airlineid": "airline_1316",
            "callsign": "CITRUS",
            "destinationairport": "MKE",
            "name": "AirTran Airways",
            "route_dockey": "route_25483",
            "stops": 0
        },
        {
            "airline": "UA",
            "airline_dockey": "airline_5209",
            "airlineid": "airline_5209",
            "callsign": "UNITED",
            "destinationairport": "FRA",
            "name": "United Airlines",
            "route_dockey": "route_57649",
            "stops": 0
        },
        {
            "airline": "UA",
            "airline_dockey": "airline_5209",
            "airlineid": "airline_5209",
            "callsign": "UNITED",
            "destinationairport": "LMT",
            "name": "United Airlines",
            "route_dockey": "route_57664",
            "stops": 0
        }
    ]

As above links explained Example 15

ON KEY route.airlineid is equivalent RDBMS ON route.airlineid = META(airline).id

If route.airlineids is Array
ON KEY route.airlineids is equivalent RDBMS ON META(airline).id IN route.airlineids

META().id is expression. During projection it inherits alias name as last dotted identifier in the expression. If it is not identifier it assigns “$1”,“$2”,… . you can override by specifying explicit alias name.