Either I am misusing the JSON functions in a way I do not understand,
or this is a bug:
SQLite version 3.10.2 2016-01-20 15:27:19
...
sqlite> CREATE TABLE t (n INTEGER PRIMARY KEY, s1 TEXT);
sqlite> INSERT INTO t (n, s1) VALUES (0, NULL), (1, 'foo');
sqlite> SELECT n, json_group_array(s1),
json_group_array(json_object('v', s1)) FROM t GROUP BY n;
n json_group_array(s1) json_group_array(json_object('v', s1))
---------- -------------------- --------------------------------------
0 [null] [{"v":null}]
1 [foo] [{"v":"foo"}]
"[foo]" is not valid JSON: SQLite is not quoting the string.
If I make small changes to the query that should not affect "[foo]",
SQLite does quote the string:
sqlite> SELECT n, json_group_array(s1) FROM t GROUP BY n;
n json_group_array(s1)
---------- --------------------
0 [null]
1 ["foo"]
sqlite> SELECT n, json_group_array(s1),
json_group_array(json_object('v', s1)) FROM t WHERE n == 1 GROUP BY n;
n json_group_array(s1) json_group_array(json_object('v', s1))
---------- -------------------- --------------------------------------
1 ["foo"] [{"v":"foo"}]
This inconsistent behavior makes me suspect there is a bug in SQLite,
although it is possible I am doing something so badly wrong the
results are undefined.
>From reading the documentation, the string should get quoted: the
aggregate functions take "VALUE arguments" and what I pass to the
first json_group_array() is not the result of a json1 function.
Does someone see the problem (either in my query or in SQLite)?
Thanks!
--
Marien Zwart
