Joins


Inner/Left Join, Embed function

Joins in JS/SQL are used to operate objects from different collections in the same query.

At the moment Clusterpoint provides possibility to execute LEFT JOIN, INNNER JOIN and EMBED functions. Compound JOIN operations should be executed in FROM clause of SELECT operator.

INNER JOIN - returns documents from both collections as long as there is a match of certain field between both collections.
LEFT JOIN - returns documents from the "main" collection with the matching fields from the "joined" collection.
EMBED function - creates grouped result set for selected fields.

Important requirements for successful JOIN execution:

  • "joined" collection should be hyper-replicated;

  • at the moment common field in "joined" hyper-replicated collection should be document "ID";

JOIN Syntax

As an example, we will use the following data:

1.
Hyper-replicated collection "people" with ID field named "_id".

Collection documents represented in "table" view:

_id name age address

19810615

Daniel Spring

35

Sarmiento 151, Piso 3, Buenos Aires, Argentina

19881022

Sabina Doray

27

Hoge Wei 27, Zaventem, Belgium

2. Sharded collection "sales".

Collection documents represented in "table" view:

_id person_id order_nr category price currency amount total_price

AgAAZSRTlAs=

19810615

006196

technique

399

EUR

6

2394

CQAAeMlblAs=

19770512

005984

technique

399

EUR

6

2394

CQAAIzRUlAs=

19881022

006191

technique

15
EUR
10
150

CAAACeRTlAs=

19810615

006199

technique

174
EUR
1
174

LEFT & INNER JOIN

Following request is executed in "sales" collection and is joined with "people" collection according to common values in fields "people._id" and "sales.person_id":

SELECT people.name as salesperson, sales.amount, sales.price
FROM sales
LEFT/INNER JOIN people ON people._id == sales.person_id WHERE typeof(people.name) != "undefined" ORDER BY people.name DESC, sales.amount DESC
LIMIT 0, 20

Response for LEFT JOIN will look like:

salesperson sales.amount sales.price _id

Sabina Doray

10

15

CQAAIzRUlAs=

Daniel Spring

6

399

AgAAZSRTlAs=

Daniel Spring

1

174

CAAACeRTlAs=

Please, note: if you get an error "Required indexes for joins are missing", it means that there are shards without documents in your collection. This situation may appear on a test phase, when document count in collection is very small. To avoid this situation, you can insert additional documents.

JOIN several collections

It is possible to JOIN several collections in one request with the limitation, that only LEFT JOIN or INNER JOIN could be used in one and the same query, both operations can not be executed. You should note, that common filed for "joinded" collections should be collections "ID" fields.
Here is an example how to JOIN several collections:

SELECT *
FROM sales
LEFT JOIN people, orders ON people._id == sales.person_id && orders._id == sales.person_id
LIMIT 0, 20

EMBED function

The way to get grouped results of certain person sales orders is to use EMBED function (common fields "people._id" and "sales.person_id"):

SELECT people.name, EMBED({amount: sales.amount, price: sales.price}) as sales 
FROM sales LEFT JOIN people ON people._id == sales.person_id
WHERE people.name == "Daniel Spring"
GROUP BY sales.person_id LIMIT 0, 20

Response is represented as an array of grouped values:

{
"results": [
{
"people.name": "Daniel Spring",
"sales": [
{
"amount": 6,
"price": 399
},
{
"amount": 1,
"price": 174
}
]
}
]
}


Or the same result in table view:

people.name sales
0 1
amount price amount price
Daniel Spring
6
399 1 174