In SQLite I can get a single row whose value is a JSON array of objects of each row.
SELECT
json_group_array(json_object('col1', col1, 'col2', col2)) as data
FROM table1;
returns
|data|
|'[{"col1": ..., "col2": ...}, {"col1": ..., "col2": ...}, {"col1": ..., "col2": ...}]'|
However, the json_object('col1', col1, 'col2', col2)
is a little bit unwieldy and gets worse when there is more columns in the query.
- Why isn’t is possible to use
json_object(*)
(just returns ‘{}’)? - Is there an alternative?