From: | Dmitry Dolgov <9erthalion6(at)gmail(dot)com> |
---|---|
To: | PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | [PATH] Jsonb, insert a new value into an array at arbitrary position |
Date: | 2016-02-18 14:38:53 |
Message-ID: | CA+q6zcWqhkGzNQs9GK+x_3ecOkZ=ufRMux6H0brmHLfUQJ0KLw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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 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.
What do you think about this?
Attachment | Content-Type | Size |
---|---|---|
jsonb_insert.patch | application/octet-stream | 14.9 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Constantin S. Pan | 2016-02-18 15:10:59 | Re: [WIP] speeding up GIN build with parallel workers |
Previous Message | Amit Kapila | 2016-02-18 14:33:26 | Re: [COMMITTERS] pgsql: Introduce group locking to prevent parallel processes from deadl |