Re: jsonb and nested hstore

On 01/31/2014 09:53 AM, Merlin Moncure wrote:
> On Fri, Jan 31, 2014 at 8:45 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>> On 01/31/2014 08:57 AM, Merlin Moncure wrote:
>>> On Fri, Jan 31, 2014 at 4:03 AM, Oleg Bartunov <obartunov(at)gmail(dot)com>
>>> wrote:
>>>> 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 ?
>>> Andrew Gierth wrote the current implementation of htsore
>>> populate_record IIRC. Unfortunately the plan for jsonb was to borrow
>>> hstore's (I don't think hstore can use the jsonb implementation
>>> because you'd be taking away the ability to handle internally nested
>>> structures it currently has). Of my two complaints upthread, the
>>> second one, not being able to populate from and internally well formed
>>> structure, is by far the more serious one I think.
>>>
>>
>> Umm, I think at least one of us is seriously confused.
>>
>> I am going to look at dealing with these issues in a way that can be used by
>> both - at least the populate_record case.
>>
>> As far as populate_record goes, there is a bit of an impedance mismatch,
>> since json/hstore records are heterogenous and one-dimensional, whereas sql
>> arrays are homogeneous and multidimensional. Right now I am thinking I will
>> deal with arrays up to two dimensions, because I can do that relatively
>> simply, and after that throw in the towel. That will surely deal with 99.9%
>> of use cases. Of course this would be documented.
>>
>> Anyway, Let me see what I can do.
>>
>> If Andrew Gierth wants to have a look at fixing the hstore() side that might
>> help speed things up.
> (ah, you beat me to it.)
>
> Disregard my statements above. It works.
>
> postgres=# select jsonb_populate_record(null::x, hstore(row(1,
> array[row(1, array[row(1, array[1,2])::z])::y])::x)::jsonb);
> jsonb_populate_record
> -------------------------------------------------------------------------------------
> (1,"{""(1,\\""{\\""\\""(1,\\\\\\\\\\""\\""{1,2}\\\\\\\\\\""\\"")\\""\\""}\\"")""}")
>

Actually, there is a workaround to the limitations of hstore(record):

andrew=# 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]}]}]

I think we could just document that for now, or possibly just use it
inside hstore(record) if we encounter a nested composite or array.

cheers

andrew