Can use join clause in the same bucket?

SQL++
1

I follow http://query.pub.couchbase.com/tutorial/#24 do join clause example.
But, in case, i have two sets of documents in a same bucket, they in two type of docs.
Can i use join clause with them?

2

you can self join on the same bucket, by using aliases:

SELECT beers.name AS beer_name, breweries.name AS brewery 
FROM `beer-sample` AS beers
JOIN `beer-sample` AS breweries ON KEYS beers.brewery_id
LIMIT 10;
1 Like
3

Thank @simonbasle It works for me.
An other thing, in case:

SELECT beers.name AS beer_name, breweries.name AS brewery 
FROM `beer-sample` AS beers
JOIN `beer-sample` AS breweries ON KEYS beers.brewery_id
where breweries.name = 'Bullfrog Brewery'

Results:

[
  {
    "beer_name": "Hands Off Maibock",
    "brewery": "Bullfrog Brewery"
  },
  {
    "beer_name": "Fast Eddies Pale Ale",
    "brewery": "Bullfrog Brewery"
  },
  {
    "beer_name": "Billtown Blonde",
    "brewery": "Bullfrog Brewery"
  },
  {
    "beer_name": "Susquehanna Oatmeal Stout",
    "brewery": "Bullfrog Brewery"
  },
  {
    "beer_name": "Inspiration Red",
    "brewery": "Bullfrog Brewery"
  },
  {
    "beer_name": "Smoked Porter",
    "brewery": "Bullfrog Brewery"
  },
  {
    "beer_name": "Edgar I.P.A.",
    "brewery": "Bullfrog Brewery"
  }
]

I want result like:

{
    "brewery": "Bullfrog Brewery",
    "beer_names": 
    [
        {"beer_name": "Hands Off Maibock"},
        {"beer_name": "Fast Eddies Pale Ale"},
        {"beer_name": "Billtown Blonde"},
        {"beer_name": "Susquehanna Oatmeal Stout"},
        {"beer_name": "Inspiration Red"},
        {"beer_name": "Smoked Porter"},
        {"beer_name": "Edgar I.P.A."}
    ]
}

I use UNNEST , but wrong…

4

HI,

SELECT breweries.name AS brewery, ARRAY_AGG(beers.name) AS beer_names
FROM beer-sample AS beers
JOIN beer-sample AS breweries ON KEYS beers.brewery_id
where breweries.name = 'Bullfrog Brewery’
GROUP BY breweries.name;

1 Like
5

@geraldss: Thank you very much!