Re: Some questions on PostgreSQL 9.6 JSONB

On Mon, Aug 28, 2017 at 9:46 AM, Wei Shan <weishan(dot)ang(at)gmail(dot)com> wrote:

> Hi all,
>
> I have some questions on JSONB on PostgreSQL 9.6.
>
> I have a table with just a single column of JSONB datatype.
>
> The following record was inserted into the table:
>
> INSERT INTO json_data values ( '{"id": "00043","name": "i am a
> boy","national_id": "SZ123123123","address": {"current":
> "current_address","prev":"prev_address"} ,"phone_number":
> "+44d765223424","dob": 123213123, "age": "87", "nationality": "british",
> "account_status": false, "account_type": "123", "account_balance":
> "123120392183091.99", "sub_account": "[00042,00043]",
> "days_till_next_bill": "1503513254", "photo": "bytes"} ');
>
> I would like to update the account_balance to 1.
>
> UPDATE json_data set "data" = jsonb_set('{"id": "00043","name": "i am a
> boy","national_id": "SZ123123123","address": {"current":
> "current_address","prev":"prev_address"} ,"phone_number":
> "+44d765223424","dob": 123213123, "age": "87", "nationality": "british",
> "account_status": false, "account_type": "123", "account_balance":
> "123120392183091.99", "sub_account": "[00042,00043]",
> "days_till_next_bill": "1503513254", "photo": "bytes"}'::jsonb,
> '{account_balance}', '1'::jsonb, false);
>
> Is this the most efficient way to do it? I'm not sure because this is
> basically replacing the entire JSON object instead of updating a single
> field.
>
> Thanks!
>
>
>
>
> --
> Regards,
> Ang Wei Shan
>

If you're just updating a plain json object independent of any table, yes,
you have to provide the whole json value. That is why the example in the
documentation has an entire json value for the first argument. If you're
updating a column in a table, the first value to jsonb_set() is any target
jsonb value, so you can place the column name here.

keith(at)keith=# create table json_data (data jsonb);
CREATE TABLE
Time: 34.661 ms
keith(at)keith=# INSERT INTO json_data values ( '{"id": "00043","name": "i am
a boy","national_id": "SZ123123123","address": {"current":
"current_address","prev":"prev_address"} ,"phone_number":
"+44d765223424","dob": 123213123, "age": "87", "nationality": "british",
"account_status": false, "account_type": "123", "account_balance":
"123120392183091.99", "sub_account": "[00042,00043]",
"days_till_next_bill": "1503513254", "photo": "bytes"} ');
INSERT 0 1
Time: 7.522 ms

keith(at)keith=# UPDATE json_data SET data = jsonb_set(data,
'{account_balance}', '1'::jsonb, false);
UPDATE 1
Time: 8.184 ms

keith(at)keith=# select * from json_data;

data

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------
{"id": "00043", "age": "87", "dob": 123213123, "name": "i am a boy",
"photo": "bytes", "address": {"prev": "prev_address", "current":
"current_address"}, "national_id": "SZ12
3123123", "nationality": "british", "sub_account": "[00042,00043]",
"account_type": "123", "phone_number": "+44d765223424", "account_status":
false, "account_balance": 1, "day
s_till_next_bill": "1503513254"}
(1 row)

Time: 0.272 ms

Keith