Re: jsonb array-style subscripting - Mailing list pgsql-hackers

On 08/17/2015 10:57 AM, Dmitry Dolgov wrote:
> Hi,
> 
> Some time ago the array-style subscripting for the jsonb data type was
> discussed in this mailing list. I think it will be quite convenient to
> have a such nice syntax to update jsonb objects, so I'm trying to
> implement this. I created a patch, that allows doing something like this:

Yaaay!

> =# create TEMP TABLE test_jsonb_subscript (
>        id int,
>        test_json jsonb
> );
> 
> =# insert into test_jsonb_subscript values
> (1, '{}'),
> (2, '{}');
> 
> =# update test_jsonb_subscript set test_json['a']['a1']['a2'] = 42;
> =# select * from test_jsonb_subscript;
>  id |        test_json         
> ----+--------------------------
>   1 | {"a": {"a1": {"a2": 42}}}
>   2 | {"a": {"a1": {"a2": 42}}}
> (2 rows)

So, both perl and python do not allow "deep nesting" of assignments.
For example:

>>> d = { "a" : { } }
>>> d["a"]["a1"]["a2"] = 42
Traceback (most recent call last): File "<stdin>", line 1, in <module>
KeyError: 'a1'

... you have to append one key level at a time.  Your approach, on the
other hand, feels more user-friendly to me; I can't tell you the number
of "if 'a2' in dic[key]" tests I've written.

So, is there any reason why consistency with perl/python behavior would
be more desirable than user-friendliness?  I'm thinking no, but figured
that it's something which needs to come up.

There is one ambiguous case you need to address:

testjson = '{ "a" : { } }'

SET testjson['a']['a1']['1'] = 42

... so in this case, is '1' a key, or the first item of an array?  how
do we determine that? How does the user assign something to an array?

> 
> =# select test_json['a']['a1'] from test_jsonb_subscript;
>  test_json  
> ------------
>  {"a2": 42}
>  {"a2": 42}
> (2 rows)

Again, how do we handle missing keys?  Just return NULL?  or ERROR?  I'd
prefer the former, but there will be arguments the other way.

> This patch has a status "work in progress" of course. Generally
> speaking, this implementation extends the `ArrayRef` usage for the jsonb.
> And I need some sort of advice about several questions:
> 
> * is it interesting for the community?
> * is that a good idea to extend the `ArrayRef` for jsonb? If it's
> appropriate, probably we can rename it to `ArrayJsonbRef` of something.
> * what can be improved in the code at the top level (function placement,
> probably, functionality duplication, etc.)?
> * are there any special cases, that I should take care of in this
> implementation?

array/key ambiguity is going to be painful.


-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com