Description
The following query on the beer-sample return error:
SELECT "Mybeer": "My Beer is " || beer.name || ".",
Array_agg({"name":brewery.name}) brewery,
Array_agg({"name":brewery.name, "state":brewery.state, "city":brewery.city, "location":brewery.geo}) locations,
Array_count(Array_agg(brewery.name)) AS brewery_count
FROM `beer-sample` brewery LEFT OUTER JOIN `beer-sample` beer
ON meta(brewery).id = beer.brewery_id
WHERE beer.type = 'beer'
AND brewery.type = 'brewery'
AND brewery.state = 'California'
GROUP BY beer.name
ORDER BY array_count(array_agg(brewery.name)) DESC,
beer.name ASC limit 5;
I get the following error:
[
{
"code": 3000,
"msg": "syntax error - at :",
"query_from_user": "SELECT \"Mybeer\": \"My Beer is \" || beer.name || \".\", \nArray_agg({\"name\":brewery.name}) brewery, \nArray_agg({\"name\":brewery.name, \"state\":brewery.state, \"city\":brewery.city, \"location\":brewery.geo}) locations, \nArray_count(Array_agg(brewery.name)) AS brewery_count\nFROM `beer-sample` brewery LEFT OUTER JOIN `beer-sample` beer\nON meta(brewery).id = beer.brewery_id\nWHERE beer.type = 'beer'\nAND brewery.type = 'brewery'\nAND brewery.state = 'California'\nGROUP BY beer.name\nORDER BY array_count(array_agg(brewery.name)) DESC,\nbeer.name ASC limit 5;"
}
]
If I only project beer.name, then it works
SELECT beer.name,
Array_agg({"name":brewery.name}) brewery,
Array_agg({"name":brewery.name, "state":brewery.state, "city":brewery.city, "location":brewery.geo}) locations,
Array_count(Array_agg(brewery.name)) AS brewery_count
FROM `beer-sample` brewery LEFT OUTER JOIN `beer-sample` beer
ON meta(brewery).id = beer.brewery_id
WHERE beer.type = 'beer'
AND brewery.type = 'brewery'
AND brewery.state = 'California'
GROUP BY beer.name
ORDER BY array_count(array_agg(brewery.name)) DESC,
beer.name ASC limit 5;