In SQLite I can get a single row whose value is a JSON array of objects of each row.
<code>SELECT
json_group_array(json_object('col1', col1, 'col2', col2)) as data
FROM table1;
</code>
<code>SELECT
json_group_array(json_object('col1', col1, 'col2', col2)) as data
FROM table1;
</code>
SELECT
json_group_array(json_object('col1', col1, 'col2', col2)) as data
FROM table1;
returns
<code>|data|
|'[{"col1": ..., "col2": ...}, {"col1": ..., "col2": ...}, {"col1": ..., "col2": ...}]'|
</code>
<code>|data|
|'[{"col1": ..., "col2": ...}, {"col1": ..., "col2": ...}, {"col1": ..., "col2": ...}]'|
</code>
|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?