Try using json_group_array(json(o))
On Tue, Jan 8, 2019, 1:50 AM Eric Grange <[email protected] wrote:
> 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 <[email protected]> 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:[email protected]]
> > Im Auftrag von Eric Grange
> > Gesendet: Dienstag, 08. Jänner 2019 09:17
> > An: General Discussion of SQLite Database <
> > [email protected]>
> > 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
> > [email protected]
> > 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
> > [email protected]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
