Re: [GENERAL] Strange SQL result - any ideas. - Mailing list pgsql-general

On Fri, Sep 01, 2017 at 11:08:32PM +0100, Paul Linehan wrote:
> which is fine (note that the field "mary" is sorted correctly) but
> I want "proper" JSON - i.e. with open and close square brackets
> i.e. ([ - ]) before and after the fields!

I don't know what that means.  Do you mean that you want all the rows in
one large top-level array?

First, JSON no longer requires that texts be either objects or array at
the top level.  But it is true that only one value may be present at the
top level, though many DBs produce sequences of multiple texts separated
by newlines.

Anyways, the thing to do is to use json_agg() or jsonb_agg(), like so:

  SELECT json_agg(row_to_json(t)) FROM (SELECT *
                                        FROM fred
                                        ORDER BY mary, jimmy, paulie) t;

> So, I tried this query:

That's pretty hacky.  Of course, it's also online/streaming, which
aggregates are not.

> SELECT '[' AS my_data  -- <<-- added line
> UNION                          -- <<-- added line
> SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g')
> FROM
> (
>   SELECT * FROM fred
>   ORDER BY mary, jimmy, paulie
> ) AS t
> UNION                           -- <<-- added line
> SELECT ']';                    -- <<-- added line
>
> *_BUT_*, this gives
>
>
>                        my_data
> ------------------------------------------------------
>  ]
>  [
>  {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
>  {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
>  {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
>  {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
>  {"mary":3,"jimmy":435,"paulie":"ererere"}
> (7 rows)

The order of rows is undefined given that you don't have an ORDER BY in
the outer-most query.  If you used UNION ALL it might work the way you
want, but then again, it might not.

This might work better if you must have the online property:

  SELECT q.token FROM (
    SELECT '[' AS token, 0 AS n, NULL AS mary, NULL AS jimmy, NULL AS paulie

    UNION -- ALL or not ALL works equally well, but if fred has no dups
          -- then UNION ALL will be faster

    SELECT regexp_replace(row_to_json(fred)::TEXT, '\\\\', '\\', 'g'),
           1, fred.mary, fred.jimmy, fred.paulie
    FROM fred fred

    UNION -- ALL or not ALL works equally well, but if fred has no dups
          -- then UNION ALL will be faster

    SELECT ']', 2, NULL, NULL, NULL) q
  ORDERY BY q.n, q.mary, q.jimmy, q.paulie;

> Two problems with this  result - one is that my square brackets are not in
> the right place - this at least I understand - the first character of
> each line is sorted by its ASCII value - '[' comes before ']' (naturally)
> and '{' comes after them both - or have I got that right?
>
> But, I do *_not_* understand why my table data is now out
> of sort order - I've looked at it and can't see *_how_* the sort
> order in my table data has been determined.

UNION means "filter out duplicates", which may be implemented via a hash
table that doesn't preserve insertion order.

Nico
--