Re: Strange SQL result - any ideas.

Paul Linehan <linehanp(at)tcd(dot)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