Hi,
I think I found a json related bug in 9.3.
Given this query:
select *
from json_array_elements('[{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]},
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]}]')
t(el)
cross join lateral (
select syms.sym ->> 'x' as x
from json_array_elements(t.el -> 's')
syms(sym)
) s;
It gives me this table:
el | x
---------------------------------------+---
{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]} | 1
{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]} | 2
{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]} | 5
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]} | 3
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]} | 4
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]} | 6
(6 rows)
So far so good. Now I want to aggregate all the x's:
select *
from json_array_elements('[{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]},
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]}]')
t(el)
cross join lateral (
select array_agg(syms.sym ->> 'x') as xx
from json_array_elements(t.el -> 's')
syms(sym)
) s;
el | xx
---------------------------------------+---------
{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]} | {1,2,5}
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]} | {3,4,6}
(2 rows)
Still works.
But if I want to string_agg them, I get this:
select *
from json_array_elements('[{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]},
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]}]')
t(el)
cross join lateral (
select string_agg(', ', syms.sym ->> 'x') as xx
from json_array_elements(t.el -> 's')
syms(sym)
) s;
el | xx
---------------------------------------+----------
{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]} | , 2, 5,
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]} | , 4, 6,
(2 rows)
Note, the first element of the resulting string is always missing.
If the xx is first aggregated as array and then converted to a string,
it works as expected:
select *
from json_array_elements('[{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]},
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]}]')
t(el)
cross join lateral (
select array_to_string(array_agg(syms.sym ->> 'x'), ', ') as xx
from json_array_elements(t.el -> 's')
syms(sym)
) s;
el | xx
---------------------------------------+---------
{"s":[{"x":"1"},{"x":"2"},{"x":"5"}]} | 1, 2, 5
{"s":[{"x":"3"},{"x":"4"},{"x":"6"}]} | 3, 4, 6
(2 rows)
One more question. Originally, my JSON data looked like this:
select *
from json_array_elements('[{"s":["1","2","5"]},
{"s":["3","4","6"]}]')
t(el)
cross join lateral (
select syms.sym as x -- problem
from json_array_elements(t.el -> 's')
syms(sym)
) s;
el | x
---------------------+-----
{"s":["1","2","5"]} | "1"
{"s":["1","2","5"]} | "2"
{"s":["1","2","5"]} | "5"
{"s":["3","4","6"]} | "3"
{"s":["3","4","6"]} | "4"
{"s":["3","4","6"]} | "6"
(6 rows)
The syms.sym field in the x column is a JSON scalar. How do I convert
that to simple TEXT? For JSON objects there is the ->> operator. Is
there anything similar for JSON scalars?
Torsten
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
