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

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.

merlin