Re: jsonb and nested hstore

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