Re: jsonb and nested hstore

>> ok, great. This is really fabulous. So far most everything feels
>> natural and good.
>>
>> I see something odd in terms of the jsonb use case coverage. One of
>> the major headaches with json deserialization presently is that
>> there's no easy way to easily move a complex (record- or array-
>> containing) json structure into a row object. For example,
>>
>> create table bar(a int, b int[]);
>> postgres=# select jsonb_populate_record(null::bar, '{"a": 1, "b":
>> [1,2]}'::jsonb, false);
>> ERROR: cannot populate with a nested object unless use_json_as_text
>> is true
>>
>> If find the use_json_as_text argument here to be pretty useless
>> (unlike in the json_build to_record variants where it least provides
>> some hope for an escape hatch) for handling this since it will just
>> continue to fail:
>>
>> postgres=# select jsonb_populate_record(null::bar, '{"a": 1, "b":
>> [1,2]}'::jsonb, true);
>> ERROR: missing "]" in array dimensions
>>
>> OTOH, the nested hstore handles this no questions asked:
>>
>> postgres=# select * from populate_record(null::bar, '"a"=>1,
>> "b"=>{1,2}'::hstore);
>> a | b
>> ---+-------
>> 1 | {1,2}
>>
>> So, if you need to convert a complex json to a row type, the only
>> effective way to do that is like this:
>> postgres=# select* from populate_record(null::bar, '{"a": 1, "b":
>> [1,2]}'::json::hstore);
>> a | b
>> ---+-------
>> 1 | {1,2}
>>
>> Not a big deal really. But it makes me wonder (now that we have the
>> internal capability of properly mapping to a record) why *both* the
>> json/jsonb populate record variants shouldn't point to what the nested
>> hstore behavior is when the 'as_text' flag is false. That would
>> demolish the error and remove the dependency on hstore in order to do
>> effective rowtype mapping. In an ideal world the json_build
>> 'to_record' variants would behave similarly I think although there's
>> no existing hstore analog so I'm assuming it's a non-trival amount of
>> work.
>>
>> Now, if we're agreed on that, I then also wonder if the 'as_text'
>> argument needs to exist at all for the populate functions except for
>> backwards compatibility on the json side (not jsonb). For non-complex
>> structures it does best effort casting anyways so the flag is moot.
>>
>
> Well, I could certainly look at making the populate_record{set} and
> to_record{set} logic handle types that are arrays or composites inside
> the record. It might not be terribly hard to do - not sure.
>
>

A quick analysis suggests that this is fixable with fairly minimal
disturbance in the jsonb case. In the json case it would probably
involve reparsing the inner json. That's probably doable, because the
routines are all reentrant, but not likely to be terribly efficient. It
will also be a deal more work.

cheers

andrew