Re: jsonb and nested hstore

On Thu, Jan 30, 2014 at 1:07 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 01/29/2014 04:56 PM, Andrew Dunstan wrote:
>>
>>
>> On 01/29/2014 01:03 PM, Andrew Dunstan wrote:
>>>
>>>
>>> On 01/27/2014 10:43 PM, Andrew Dunstan wrote:
>>>>
>>>>
>>>> On 01/26/2014 05:42 PM, Andrew Dunstan wrote:
>>>>>
>>>>>
>>>>> Here is the latest set of patches for nested hstore and jsonb.
>>>>>
>>>>> Because it's so large I've broken this into two patches and compressed
>>>>> them. The jsonb patch should work standalone. The nested hstore patch
>>>>> depends on it.
>>>>>
>>>>> All the jsonb functions now use the jsonb API - there is no more
>>>>> turning jsonb into text and reparsing it.
>>>>>
>>>>> At this stage I'm going to be starting cleanup on the jsonb code
>>>>> (indentation, error messages, comments etc.) as well get getting up some
>>>>> jsonb docs.
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>> Here is an update of the jsonb part of this. Charges:
>>>>
>>>> * there is now documentation for jsonb
>>>> * most uses of elog() in json_funcs.c are replaced by ereport().
>>>> * indentation fixes and other tidying.
>>>>
>>>> No changes in functionality.
>>>>
>>>
>>>
>>> Further update of jsonb portion.
>>>
>>> Only change in functionality is the addition of casts between jsonb and
>>> json.
>>>
>>> The other changes are the merge with the new json functions code, and
>>> rearrangement of the docs changes to make them less ugly. Essentially I
>>> moved the indexterm tags right out of the table as is done in some other
>>> parts pf the docs. That makes the entry tags much clearer to read.
>>>
>>>
>>>
>>
>>
>> Updated to apply cleanly after recent commits.
>>
>>
>
> Updated patches for both pieces. Included is some tidying done by Teodor,
> and fixes for remaining whitespace issues. This now passes "git diff --check
> master" cleanly for me.

Something seems off:

postgres=# create type z as (a int, b int[]);
CREATE TYPE
postgres=# create type y as (a int, b z[]);
CREATE TYPE
postgres=# create type x as (a int, b y[]);
CREATE TYPE

-- test a complicated construction
postgres=# select row(1, array[row(1, array[row(1, array[1,2])::z])::y])::x;
row
-------------------------------------------------------------------------------------
(1,"{""(1,\\""{\\""\\""(1,\\\\\\\\\\""\\""{1,2}\\\\\\\\\\""\\"")\\""\\""}\\"")""}")

postgres=# select hstore(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
hstore
----------------------------------------------------------------------------------------------
"a"=>1, "b"=>"{\"(1,\\\"{\\\"\\\"(1,\\\\\\\\\\\"\\\"{1,2}\\\\\\\\\\\"\\\")\\\"\\\"}\\\")\"}"

here, the output escaping has leaked into the internal array
structures. istm we should have a json expressing the internal
structure. It does (weirdly) map back however:

postgres=# select populate_record(null::x, hstore(row(1, array[row(1,
array[row(1, array[1,2])::z])::y])::x));
populate_record
-------------------------------------------------------------------------------------
(1,"{""(1,\\""{\\""\\""(1,\\\\\\\\\\""\\""{1,2}\\\\\\\\\\""\\"")\\""\\""}\\"")""}")

OTOH, if I go via json route:

postgres=# select row_to_json(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x);
row_to_json
-----------------------------------------------
{"a":1,"b":[{"a":1,"b":[{"a":1,"b":[1,2]}]}]}

so far, so good. let's push to hstore:
postgres=# select row_to_json(row(1, array[row(1, array[row(1,
array[1,2])::z])::y])::x)::jsonb::hstore;
row_to_json
-------------------------------------------------------
"a"=>1, "b"=>[{"a"=>1, "b"=>[{"a"=>1, "b"=>[1, 2]}]}]

this ISTM is the 'right' behavior. but what if we bring it back to
record object?

postgres=# select populate_record(null::x, row_to_json(row(1,
array[row(1, array[row(1, array[1,2])::z])::y])::x)::jsonb::hstore);
ERROR: malformed array literal: "{{"a"=>1, "b"=>{{"a"=>1, "b"=>{1, 2}}}}}"

yikes. The situation as I read it is that (notwithstanding my comments
upthread) there is no clean way to slide rowtypes to/from hstore and
jsonb while preserving structure. IMO, the above query should work
and the populate function record above should return the internally
structured row object, not the text escaped version.

merlin