Re: BUG #10728: json_to_recordset with nested json objects NULLs columns

On Sun, Jun 22, 2014 at 8:34 AM, <matti(dot)hameister(at)technologygroup(dot)de>
wrote:
> The following bug has been logged on the website:
>
> Bug reference: 10728
> Logged by: Matti Hameister
> Email address: matti(dot)hameister(at)technologygroup(dot)de
> PostgreSQL version: 9.4beta1
> Operating system: Linux
> Description:
>
> This query:
>
> --
> SELECT X.* FROM
> json_to_record(
> '
> {"a":2,"c":3,"b":{"z":4}, "d":6}
> ',true
> ) AS X(a int, b json, c int, d int);
> --
>
> returns as expected
> a: 2
> b: {"z":4}
> c: 3
> d: 6
>
>
> Now I changed the query a bit (using recordset):
>
> --
> SELECT X.* FROM
> json_to_recordset(
> '[
> {"a":2,"c":3,"b":{"z":4}, "d":6}
> ]
> ',true
> ) AS X(a int, b json, c int, d int);
> --
>
> the result is surprising:
> a: NULL
> b: {"z":4}
> c: NULL
> d: 6
Interesting. I would have expected the same result as well. It is worth
noticing that jsonb_to_recordset works as expected:
=# SELECT X.* FROM json_to_recordset('[{"a":2,"c":3,"b":{"z":4}, "d":6}]',
true)
AS X(a int, b json, c int, d int);
a | b | c | d
------+---------+------+---
null | {"z":4} | null | 6
(1 row)
=# SELECT X.* FROM jsonb_to_recordset('[{"a":2,"c":3,"b":{"z":4}, "d":6}]',
true)
AS X(a int, b json, c int, d int);
a | b | c | d
---+----------+---+---
2 | {"z": 4} | 3 | 6
(1 row)

Digging more into it, you can see this error happens iff only one of the
fields is a json itself, and that it deletes all the values prior to it.
For example in this case a json value is set as the 3rd return element,
note that the two ones prior to it get deleted:
=# SELECT X.* FROM json_to_recordset('[{"a":2,"b":3,"c":{"z":4}, "d":6}]',
true) as X(a int, b int, c json, d int);
a | b | c | d
------+------+----------+---
null | null | {"z": 4} | 6
(1 row)
The error is as well independent on the order of the elements in the alias
clause, but in their order in the json field:
=# SELECT X.* FROM json_to_recordset('[{"a":2,"b":3,"c":{"z":4}, "d":6}]',
true)
AS X(a int, c json, b int, d int);
a | c | b | d
------+---------+------+---
null | {"z":4} | null | 6
(1 row)

Finally, the last json value deletes all the prior values, even other json:
=# SELECT X.* FROM
json_to_recordset('[{"a":2,"b":{"v":4},"c":6,"d":{"x":6},"e":7}]', true)
AS X(a int, b json, c int, d json, e int);
a | b | c | d | e
------+------+------+---------+---
null | null | null | {"x":6} | 7
(1 row)

I am guessing that the bug origin is in pg_parse_json in the way nested
json is managed, it is the only code path of populate_recordset_worker
where a switch on JSON[B]OID is used.
Regards,
--
Michael