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