JOIN in many to one mapping

SQL++
1

I’m attempting to write a query that returns all parent documents, each with all of it’s child documents in an array. The parent documents looks like this (I’ll attempt to use the beer notation):

{
    "_id": "GUID1234"
    "type": "brewery"
    "name": "Heinekin"
}

and the child documents look like this:

{
    "type": "mid-strength beer"
    "brewer": "GUID1234"
}

{
    "type": "full-strength beer"
    "brewer": "GUID1234"
}

I need a result that would have the brewer with a list of all the beers from that brewer. The closest I’ve gotten is this:

SELECT META(brewer).id , beer
FROM    `dev` beer
JOIN    `dev` brewer   ON    KEYS    beer.brewer

But this returns every beer in a new row. Any help putting them into a list per brewer is greatly appreciated.
Cheers.

2

You can apply filters too.

SELECT META(brewer).id , ARRAY_AGG(beer)
FROM    `dev` beer
JOIN    `dev` brewer   ON    KEYS    beer.brewer
GROUP BY META(brewer).id ;

OR you can use INDEX NEST, applying filters on right side is difficult.

CREATE INEDX ix1 ON dev(brewer);

SELECT META(brewer).id , beer
FROM    `dev` brewer
NEST    `dev` beer   ON    KEY    beer.brewer FOR brewer
WHERE brewer.type = "brewery" ;
1 Like