Обсуждение: [ADMIN] Some questions on PostgreSQL 9.6 JSONB
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
Ang Wei Shan
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
On Mon, Aug 28, 2017 at 9:46 AM, Wei Shan <weishan.ang@gmail.com> wrote: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.
keith@keith=# UPDATE json_data SET data = jsonb_set(data, '{account_balance}', '1'::jsonb, false);
This. Note still that in a MVCC (multi-version concurrency controlling) database you never really just update something in place since the old something has to continue to exist at the same time as the new something - at least for a little while.
David J.
Stupid question really,
If I have to specify the entire JSON value, this means that the update query replaces the entire row instead of updating a single field. Is this correct?
On 28 August 2017 at 16:20, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Aug 28, 2017 at 9:46 AM, Wei Shan <weishan.ang@gmail.com> wrote: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.
keith@keith=# UPDATE json_data SET data = jsonb_set(data, '{account_balance}', '1'::jsonb, false);This. Note still that in a MVCC (multi-version concurrency controlling) database you never really just update something in place since the old something has to continue to exist at the same time as the new something - at least for a little while.David J.
Regards,
Ang Wei Shan
Ang Wei Shan
On Wednesday, August 30, 2017, Wei Shan <weishan.ang@gmail.com> wrote:
Stupid question really,If I have to specify the entire JSON value, this means that the update query replaces the entire row instead of updating a single field. Is this correct?
The whole row is always replaced. Any values not specified in the update set list are simply copied over to the new record as-is.
David J.