9.3: bug related to json

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general