Group by returns code 4210


#1

I am trying to create a query that lists beers by brewery. I created the following N1QL:

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.city = "Philadelphia"
group by br.name;

and I got the following error:

[
  {
    "code": 4210,
    "msg": "Expression must be a group key or aggregate: (`b`.`name`)",
    "query_from_user": "select br.name brewery\n, b.name beer\n, b.style style \nfrom `beer-sample` b\njoin `beer-sample` br on keys b.brewery_id\nwhere br.city = \"Philadelphia\"\ngroup by br.name;"
  }
]

How would I fix this query to return an JSON object for each brewery containing an array of beers?


#2

You need ARRAY_AGG.

select br.name brewery
, ARRAY_AGG( { "beer": b.name, "style": b.style } ) AS beers
from `beer-sample` b
join `beer-sample` br on keys b.brewery_id
where br.city = "Philadelphia"
group by br.name;

There is also an alternative version that can be indexed.


#3

That was what I was looking for. Thanks for the quick response.