Closed simonw closed 4 months ago
I upgraded to Sonnet for this experiment. The question I tried was:
4 most popular licenses and the top repos for each one, use json_object
Against the https://datasette.io/content/licenses table
Eventually got it working. The debug output looked like this:
--
explain select l.name, l.key, json_group_object (
'repos', json_group_array(
json_object(
'id', r.id,
'full_name', r.full_name,
'stargazers', r.stargazers_count
)
)
)
from licenses l
join repos r on r.license = l.key
group by l.name, l.key
order by sum(r.stargazers_count) desc
limit 4;
--
ERROR: conn=<sqlite3.Connection object at 0x10720c840>, sql = "explain select l.name, l.key, json_group_object (\n 'repos', json_group_array(\n json_object(\n 'id', r.id,\n 'full_name', r.full_name,\n 'stargazers', r.stargazers_count\n )\n )\n)\nfrom licenses l\njoin repos r on r.license = l.key\ngroup by l.name, l.key\norder by sum(r.stargazers_count) desc\nlimit 4;\n", params = None: misuse of aggregate function json_group_array()
--
explain select l.name, l.key,
json_group_array(
json_object(
'id', r.id,
'full_name', r.full_name,
'stargazers', r.stargazers_count
)
)
from licenses l
join repos r on r.license = l.key
group by l.name, l.key
order by sum(r.stargazers_count) desc
limit 4;
Note the error there: misuse of aggregate function json_group_array()
I found a query that gave a SQL parse error first time and needed a retry and got this error: