Re: jsonb array-style subscripting

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