Обсуждение: 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

Torsten Förtsch wrote
>  cross join lateral (
>      select string_agg(', ', syms.sym ->> 'x') as xx
>        from json_array_elements(t.el -> 's')
>             syms(sym)

I'm doubting you intended to join a bunch of commas using the field value as
the delimiter...methinks your got the argument order reversed for
string_agg.

David J.




--
View this message in context: http://postgresql.nabble.com/9-3-bug-related-to-json-tp5839261p5839310.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Torsten Förtsch wrote
> Is there anything similar for JSON scalars?

IDK, but have you tried "::text"?

David J.






--
View this message in context: http://postgresql.nabble.com/9-3-bug-related-to-json-tp5839261p5839311.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 25/02/15 07:22, David G Johnston wrote:
> I'm doubting you intended to join a bunch of commas using the field value as
> the delimiter...methinks your got the argument order reversed for
> string_agg.

OMG, I am so stupid. Thanks.

On 25/02/15 07:34, David G Johnston wrote:
> Torsten Förtsch wrote
>> > Is there anything similar for JSON scalars?
> IDK, but have you tried "::text"?

yes. Here is the difference

select * from (values (('{"a":"b"}'::json -> 'a')::text),
                       ('{"a":"b"}'::json ->> 'a')) t;
 column1
---------
 "b"
 b

Torsten

=?UTF-8?B?VG9yc3RlbiBGw7ZydHNjaA==?= <torsten.foertsch@gmx.net> writes:
> On 25/02/15 07:34, David G Johnston wrote:
>> Torsten Förtsch wrote
>>> Is there anything similar for JSON scalars?
>> IDK, but have you tried "::text"?

> yes. Here is the difference
> select * from (values (('{"a":"b"}'::json -> 'a')::text),
>                        ('{"a":"b"}'::json ->> 'a')) t;
>  column1
> ---------
>  "b"
>  b

As of 9.4, there's a function json_array_elements_text()
which does what I think you're looking for.

            regards, tom lane