Re: jsonb array-style subscripting

On Mon, Aug 17, 2015 at 12:57 PM, Dmitry Dolgov <9erthalion6(at)gmail(dot)com> 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:
>
>
> =# 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)
>
> =# select test_json['a']['a1'] from test_jsonb_subscript;
> test_json
> ------------
> {"a2": 42}
> {"a2": 42}
> (2 rows)
>
>
> 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?

I'm not sure if this:
update test_jsonb_subscript set test_json['a']['a1']['a2'] = 42;

...is a good idea. postgres operators tend to return immutable copies
of the item they are referring to. In other words, you'd never see a
column operator on the 'left' side of the equals in an update
statement. I think you need to look at a function to get the behavior
you want:

update test_jsonb_subscript set test_json = jsonb_modify(test_json,
'[a][a1][a2] = 42');]

...as a hypothetical example. The idea is you need to make a
function that provides the ability to make the complete json you want.
Update statements always make a copy of the record anyways.

merlin