Re: [PATH] Jsonb, insert a new value into an array at arbitrary position

On 18/02/16 15:38, Dmitry Dolgov wrote:
> Hi
>
> As far as I see there is one basic update function for jsonb, that can't be
> covered by `jsonb_set` - insert a new value into an array at arbitrary
> position.
> Using `jsonb_set` function we can only append into array at the end/at the
> beginning, and it looks more like a hack:
>
> ```
> =# select jsonb_set('{"a": [1, 2, 3]}', '{a, 100}', '4');
> jsonb_set
> ---------------------
> {"a": [1, 2, 3, 4]}
> (1 row)
>
>
> =# select jsonb_set('{"a": [1, 2, 3]}', '{a, -100}', '4');
> jsonb_set
> ---------------------
> {"a": [4, 1, 2, 3]}
> (1 row)
> ```
>
> I think the possibility to insert into arbitrary position will be quite
> useful,
> something like `json_array_insert` in MySql:
>
> ```
> mysql> set @j = '["a", {"b": [1, 2]}, [3, 4]]';
> mysql> select json_array_insert(@j, '$[1].b[0]', 'x');
>
> json_array_insert(@j, '$[1].b[0]', 'x')
> +-----------------------------------------+
> ["a", {"b": ["x", 1, 2]}, [3, 4]]
> ```
>
> It can look like `jsonb_insert` function in our case:
>
> ```
> =# select jsonb_insert('{"a": [0,1,2]}', '{a, 1}', '"new_value"');
> jsonb_insert
> -------------------------------
> {"a": [0, "new_value", 1, 2]}
> (1 row)
> ```
>

I think it makes sense to have interface like this, I'd strongly prefer
the jsonb_array_insert naming though.

> I attached possible implementation, which is basically quite small (all
> logic-related
> modifications is only about 4 lines in `setPath` function). This
> implementation
> assumes a flag to separate "insert before"/"insert after" operations, and an
> alias to `jsonb_set` in case if we working with a jsonb object, not an
> array.
>

I don't think it's a good idea to use set when this is used on object, I
think that we should throw error in that case.

Also this patch needs documentation.

--
Petr Jelinek http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services