Re: jsonb and nested hstore

Hmm,
neither me, nor Teodor have experience and knowledge with
populate_record() and moreover hstore here is virgin and we don't know
the right behaviour, so I think we better take it from jsonb, once
Andrew realize it. Andrew ?

On Fri, Jan 31, 2014 at 4:52 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
> On 01/30/2014 07:21 PM, Merlin Moncure wrote:
>
>> 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.
>
>
> What has this to do with json at all? It's clearly a failure in the hstore()
> function.
>
>
>
>> 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.
>
>
>
> And this is a failure in populate_record().
>
> I think we possibly need to say that handling of nested composites and
> arrays is an area that needs further work. OTOH, the refusal of
> json_populate_record() and json_populate_recordset() to handle these in 9.3
> has not generated a flood of complaints, so I don't think it's a tragedy,
> just a limitation, which should be documented if it's not already. (And of
> course hstore hasn't handled nested anything before now.)
>
> Meanwhile, maybe Teodor can fix the two hstore bugs shown here.
>
> cheers
>
> andrew
>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers