Thread: BUG #13937: 'src' -> jsonb_each() -> jsonb_object() -> 'dst' does not recreate 'src' as valid jsonb

The following bug has been logged on the website:

Bug reference:      13937
Logged by:          Xtra Coder
Email address:      xtracoder@gmail.com
PostgreSQL version: 9.5.0
Operating system:   Windows7
Description:

Steps to reproduce:
-------------------

DO LANGUAGE plpgsql $$
DECLARE
  jsonb_src jsonb;
  jsonb_dst jsonb;
BEGIN
  jsonb_src = '{
    "key1": {"data1": [1, 2, 3]},
    "key2": {"data2": [3, 4, 5]}
  }';
  raise notice 'jsonb_src = %', jsonb_src;

  with t_data as (select * from jsonb_each(jsonb_src))
  select jsonb_object(
    array(select key from t_data),
    array(select value::text from t_data) )
  into jsonb_dst;
  raise notice 'jsonb_dst = %', jsonb_dst;
END $$;


Actual result:
--------------

NOTICE:  jsonb_src = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3, 4,
5]}}
NOTICE:  jsonb_dst = {"key1": "{\"data1\": [1, 2, 3]}", "key2": "{\"data2\":
[3, 4, 5]}"}

What's wrong? - values in 'dst' are represented as text. Reason -
jsonb_object() has arguments as jsonb_object(keys text[], values text[]) and
there is no way to pass values as 'jsonb'. Conversion to 'text' looses JSON
structure.


Expected result:
----------------

NOTICE:  jsonb_src = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3, 4,
5]}}
NOTICE:  jsonb_dst = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3, 4,
5]}}

'src' and 'dst' JSON objects should be identical/equal.

On Monday, February 8, 2016, <xtracoder@gmail.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13937
> Logged by:          Xtra Coder
> Email address:      xtracoder@gmail.com <javascript:;>
> PostgreSQL version: 9.5.0
> Operating system:   Windows7
> Description:
>
> Steps to reproduce:
> -------------------
>
> DO LANGUAGE plpgsql $$
> DECLARE
>   jsonb_src jsonb;
>   jsonb_dst jsonb;
> BEGIN
>   jsonb_src = '{
>     "key1": {"data1": [1, 2, 3]},
>     "key2": {"data2": [3, 4, 5]}
>   }';
>   raise notice 'jsonb_src = %', jsonb_src;
>
>   with t_data as (select * from jsonb_each(jsonb_src))
>   select jsonb_object(
>     array(select key from t_data),
>     array(select value::text from t_data) )
>   into jsonb_dst;
>   raise notice 'jsonb_dst = %', jsonb_dst;
> END $$;
>
>
> Actual result:
> --------------
>
> NOTICE:  jsonb_src = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3,
> 4,
> 5]}}
> NOTICE:  jsonb_dst = {"key1": "{\"data1\": [1, 2, 3]}", "key2":
> "{\"data2\":
> [3, 4, 5]}"}
>
> What's wrong? - values in 'dst' are represented as text. Reason -
> jsonb_object() has arguments as jsonb_object(keys text[], values text[])
> and
> there is no way to pass values as 'jsonb'. Conversion to 'text' looses JSON
> structure.
>
>
> Expected result:
> ----------------
>
> NOTICE:  jsonb_src = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3,
> 4,
> 5]}}
> NOTICE:  jsonb_dst = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3,
> 4,
> 5]}}
>
> 'src' and 'dst' JSON objects should be identical/equal.
>
>
While I see the value of your species behavior there is no explicit promise
to evaluate the provided text for json-ness and convert it.  This is also
not going to change now that it has been released.  New functions would be
needed that would enable round-tripping of json in the manner you describe.

Someone else may have advice regarding a work-around until someone commits
such capabilities into a future release.

David J.

xtracoder@gmail.com writes:
> Expected result:
> ----------------

> NOTICE:  jsonb_src = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3, 4,
> 5]}}
> NOTICE:  jsonb_dst = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3, 4,
> 5]}}

> 'src' and 'dst' JSON objects should be identical/equal.

Considering that you explicitly casted the "values" to text, it would
absolutely be a bug if you got that result from this code.  I don't think
this is a bug at all; it's a feature request for some new variant of
jsonb_object().  But can't you already get what you want from
jsonb_object_agg()?

DO LANGUAGE plpgsql $$
DECLARE
  jsonb_src jsonb;
  jsonb_dst jsonb;
BEGIN
  jsonb_src = '{
    "key1": {"data1": [1, 2, 3]},
    "key2": {"data2": [3, 4, 5]}
  }';
  raise notice 'jsonb_src = %', jsonb_src;

  with t_data as (select * from jsonb_each(jsonb_src))
  select jsonb_object_agg(key, value) into jsonb_dst
  from t_data;
  raise notice 'jsonb_dst = %', jsonb_dst;
END $$;

NOTICE:  jsonb_src = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3, 4, 5]}}
NOTICE:  jsonb_dst = {"key1": {"data1": [1, 2, 3]}, "key2": {"data2": [3, 4, 5]}}
DO

I think your original coding is kinda broken anyway; there's no hard
guarantee that those two array sub-selects will give results in the
same order.

            regards, tom lane

Re: I think your original coding is kinda broken anyway; there's no
hard guarantee
that those two array sub-selects will give results in the same order.
----------------------------------

Order of the keys is, of course, of no point here (and is not relevant for
normal JSON). Conversion to string is just for 'representational/debugging'
purposes.

Re: I don't think this is a bug at all ...
------------------------------------------------------

I would not agree. This issue can be treated as a bug from
various perspective, and I just can't tell which perspective should be
treated as bug. For example:

Bug in implementation of jsonb_object(keys text[], values text[]) method
....

... since it is 'jsonb' method - regular user would expect that values are
converted to 'jsonb' just like in other cases of 'string' ->
'jsonb' conversion, but in this cases it is treated just like string.

Bug in documentation
http://www.postgresql.org/docs/current/static/functions-json.html ...

...  which may potentially need to explicitly say - "values are treated as
plain strings, with no attempt to convert to jsonb. For other use-cases -
use ...".

Bug in design/architecture ...

... current behavior is by design and method which accepts 'values' as  '
jsonb[]' is missing for some reason (and this efficiently means there is no
'normal' way to construct jsonb object).

So ... it is up to someone from PostgreSQL dev team to decide what is bug
and what is not bug here. If above mentioned 'jsonb_object_agg' method is
actually intended for such use-case - then probably yes, the only bug is in
documentation - it is really hardly possible to get to 'jsonb_object_agg'
while reading section ''JSON Creation Functions" at
*http://www.postgresql.org/docs/current/static/functions-json.html
<http://www.postgresql.org/docs/current/static/functions-json.html>*.