Re: jsonb and nested hstore - Mailing list pgsql-hackers

On 01/29/2014 05:37 PM, Merlin Moncure wrote:
> On Wed, Jan 29, 2014 at 3:56 PM, Andrew Dunstan <andrew@dunslane.net> 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.
> 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.

cheers

andrew