Re: [GENERAL] Strange SQL result - any ideas.

Paul Linehan <linehanp@tcd.ie> writes:
> I have a table (fred) that I want to transform into JSON and
> I use the following command (ignore the backslash stuff):
> ...
> 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!

Well, proper JSON would also require commas between the array elements,
no?  I think what you're really after is

=# SELECT json_agg(ROW_TO_JSON(t))
FROM
(
  SELECT * FROM fred
  ORDER BY mary, jimmy, paulie
) AS t;
                                        json_agg
                                                 

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 [{"mary":2,"jimmy":43,"paulie":"asfasfasfd"}, {"mary":3,"jimmy":435,"paulie":"ererere"},
{"mary":3,"jimmy":44545,"paulie":"\\sdfs\\\\\\sfs\\\\gf"},{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"},
{"mary":35,"jimmy":5,"paulie":"wrew\\sdfsd"}]
(1 row)

As far as that UNION query goes, I think you misunderstand
what UNION does.  It doesn't promise to preserve ordering.
You might have gotten the results you expected with UNION
ALL (but they still wouldn't have constituted a valid
JSON array).

            regards, tom lane