bug in json_group_array when using order by

(1) By Matt (iffycan2) on 2023-11-28 17:56:40 [source]

Adding an ORDER BY clause within json_group_array produces json strings rather than json. I'm seeing this on 3.44.2. Here's some code that illustrates the problem:

create table tab (a text);
insert into tab (a) values ('ape'), ('cat'), ('boy');

select
  json_group_array(json_object('a', a))
from tab
union all
select
  json_group_array(json_object('a', a)
    order by a
  )
from tab;

Produces the following rows:

[{"a":"ape"},{"a":"cat"},{"a":"boy"}]
["{\"a\":\"ape\"}","{\"a\":\"boy\"}","{\"a\":\"cat\"}"]

(2) By Richard Hipp (drh) on 2023-11-28 19:39:12 in reply to 1 [link] [source]

I have not verified this problem, but it does make sense.

The ORDER BY clause separates the json_object() from the json_group_array(), inserting a sorter in between them. But the sorter destroys the sub-type coming out of json_object() that indicates that the result is JSON and not plain text.

I do not see a reasonable way to fix this. It might end up just being a documented behavioral quirk.

(3) By anonymous on 2023-11-28 21:10:51 in reply to 2 [link] [source]

Do pointer values survive this kind of sorting?

(4) By Richard Hipp (drh) on 2023-11-28 21:36:31 in reply to 3 [link] [source]

Probably not.

(5) By Vitalije (vitalije) on 2023-11-29 12:19:14 in reply to 1 [link] [source]

You can select from already ordered select statement like so:

select json_group_array(json_object('a', a))
from (select a from tab order by a);

which gives the expected answer:

[{"a":"ape"},{"a":"boy"},{"a":"cat"}]

(6) By Richard Hipp (drh) on 2023-12-14 15:15:29 in reply to 1 [link] [source]

This should now be fixed, as of check-in d302a389460d0c15. Please post a follow-up if you disagree.

(7.1) Originally by nora (noramila) with edits by Dan Kennedy (dan) on 2023-12-16 13:36:26 from 7.0 in reply to 1 [link] [source]

You can perform the ordering outside the json_group_array function.

here is the example.

SELECT
  json_group_array(j)
FROM (
  SELECT
    json_object('a', a) AS j
  FROM tab
  ORDER BY a
);

I hope it will help.