Thread: [GENERAL] Strange SQL result - any ideas.
<CREATE TABLE and INSERT INTO statements at end of post>
I have a table (fred) that I want to transform into JSON and
I use the following command (ignore the backslash stuff):
SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g')
FROM
(
SELECT * FROM fred
ORDER BY mary, jimmy, paulie
) AS t;
which gives
regexp_replace
------------------------------------------------------
{"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"}
(5 rows)
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!
So, I tried this query:
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)
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.
Anybody got any logical explanations as to what's going on?
TIA & Rgs,
Paul...
-- CREATE TABLE and INSERT INTO statements.
CREATE TABLE fred (
mary integer PRIMARY KEY,
jimmy integer,
paulie character varying(20)
);
INSERT INTO fred (mary, jimmy, paulie) VALUES (2, 43, 'asfasfasfd');
INSERT INTO fred (mary, jimmy, paulie) VALUES (3, 435, 'ererere');
INSERT INTO fred (mary, jimmy, paulie) VALUES (6, 43343, 'eresdfssfsfasfae');
INSERT INTO fred (mary, jimmy, paulie) VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred (mary, jimmy, paulie) VALUES (3, 44545, '\sdfs\\\sfs\\gf');
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
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
--