2022/07/31
About creating json values using sqlite3’s built-in json value support.
Example:
SELECT json_object('foo', json_object('bar', 'baz'));
-- {"foo":{"bar":"baz"}}
SELECT json_object('foo', json_array('bar', 'baz'));
-- {"foo":["bar","baz"]}
-- works with nested queries too
SELECT json_object('foo', (SELECT json_array('bar', 'baz')));
-- {"foo":["bar","baz"]}
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
INSERT INTO t1 VALUES (1, 'A', 'one' ),
(2, 'B', 'two' ),
(3, 'C', 'three'),
(4, 'D', 'one' ),
(5, 'A', 'two' ),
(6, 'B', 'three'),
(7, 'C', 'one' );
SELECT json_group_array(b)
FROM t1
GROUP BY c;
-- ["A","D","C"]
-- ["C","B"]
-- ["B","A"]
Caveat: you sometimes have to wrap nested values from nested queries in json(), else they’ll be escaped as json string literals. See https://stackoverflow.com/questions/55421128/how-do-i-get-nested-json-data-out-of-sqlite-with-a-multi-level-group-by.