Thanks. I think I may have encountered a "real" bug while ordering in a subquery. I have simplified it in the following exemples:
select json_group_array(o) from ( select json_object( 'id', sb.id ) o from ( select 1 id, 2 field ) sb ) the json_group_array returns an array of JSON object, ie. [{"id":1}] while in select json_group_array(o) from ( select json_object( 'id', sb.id ) o from ( select 1 id, 2 field ) sb order by sb.field desc ) so with an added order by in the subquery, it returns and array of JSON strings, ie. ["{\"id\":1}"] In my particular case, I can work around the issue by using group_concat() rather than json_group_array() Can someone confirm whether this is a bug ? Thanks! Le mar. 8 janv. 2019 à 10:18, Hick Gunter <h...@scigames.at> a écrit : > I don't recall that any (aggregate) function is concerned at all about the > order in which rows are visited. The effect is only visible in > non-commutative aggregates (e.g. concatenation). > > If you want the arguments presented to an aggregate function in a specific > order, then you need to enforce that order, with an order by clause in a > subselect if necessary. > > If you have an order by clause which is already fulfilled by the > visitation order, SQLite will not sort again. > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Eric Grange > Gesendet: Dienstag, 08. Jänner 2019 09:17 > An: General Discussion of SQLite Database < > sqlite-users@mailinglists.sqlite.org> > Betreff: [EXTERNAL] [sqlite] json_group_array() and sorting > > Hi, > > Is json_group_array() supposed to honor a sorting clause or not ? (and > concatenation aggregates in general) > > I have a query like > > select json_group_array(json_object( > 'id', st.id, > 'num', st.numeric_field, > ...bunch of fields here... > )) > from some_table st > ...bunch of joins here... > where ...bunch of conditions... > order by st.numeric_field desc > limit 50 > > > but the resulting JSON array is not ordered according to the "order by", > but AFAICT by the st.id field (a primary key) When not aggregating, the > records are in the correct order. > > Is it a bug or something expected ? > > I can get the proper order when I use a subquery for the joins & filters, > and aggregate in a top level query, but that is rather more verbose, and I > am not sure the ordering being preserved in that case is not just > 'circumstancial' and could be affected by future SQLite query optimizations. > > Thanks! > > Eric > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick | Software Engineer | Scientific Games International GmbH | > Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) > +43 1 80100 - 0 > > May be privileged. May be confidential. Please delete if not the addressee. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users