Re: [ADMIN] Some questions on PostgreSQL 9.6 JSONB - Mailing list pgsql-admin
| From | Keith |
|---|---|
| Subject | Re: [ADMIN] Some questions on PostgreSQL 9.6 JSONB |
| Date | |
| Msg-id | CAHw75vuc43o-a8pphkWqZeupvvKtAkXS5HRw03817eUzf9fSrg@mail.gmail.com Whole thread Raw |
| In response to | [ADMIN] Some questions on PostgreSQL 9.6 JSONB (Wei Shan <weishan.ang@gmail.com>) |
| Responses |
Re: [ADMIN] Some questions on PostgreSQL 9.6 JSONB
("David G. Johnston" <david.g.johnston@gmail.com>)
|
| List | pgsql-admin |
On Mon, Aug 28, 2017 at 9:46 AM, Wei Shan <weishan.ang@gmail.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.
CREATE TABLE
Time: 34.661 ms
keith@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@keith=# UPDATE json_data SET data = jsonb_set(data, '{account_balance}', '1'::jsonb, false);
UPDATE 1
Time: 8.184 ms
keith@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
pgsql-admin by date: