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