How to Update Data in the JSON Field in PostgreSQL
- JSON Data Type in PostgreSQL
- Insert a New Key-Value Pair in the JSON Field in PostgreSQL
-
Use
JSONB_SET()to Update Existing Data in the JSON Field in PostgreSQL
This article will discuss updating a JSON field using PostgreSQL.
JSON Data Type in PostgreSQL
There are two JSON data types in PostgreSQL: JSON and JSONB. Let’s say you have the following table populated with the following data:
postgres=# select * from product;
id | product | details
----+---------+---------------------------------------------------
1 | PC1 | {"CPU": "4.1 GHz", "HDD": "2TB", "RAM": "16GB"}
2 | PC2 | {"CPU": "3.1 GHz", "HDD": "1TB", "RAM": "8GB"}
3 | PC3 | {"CPU": "2.5 GHz", "HDD": "2.5TB", "RAM": "32GB"}
4 | PC4 | {"CPU": "3.5 GHz", "HDD": "2TB", "RAM": "64GB"}
5 | PC5 | {"CPU": "2.8 GHz", "HDD": "2TB", "RAM": "4GB"}
(5 rows)
To create the following table, we run the SQL query below:
CREATE TABLE product (
id INT,
product VARCHAR,
details JSONB
);
INSERT INTO product VALUES (1, 'PC1','{"CPU":"4.1 GHz", "RAM":"16GB", "HDD":"2TB"}');
INSERT INTO product VALUES (1, 'PC2','{"CPU":"3.1 GHz", "RAM":"8GB", "HDD":"1TB"}');
INSERT INTO product VALUES (1, 'PC3','{"CPU":"2.5 GHz", "RAM":"32GB", "HDD":"2.5TB"}');
INSERT INTO product VALUES (1, 'PC4','{"CPU":"3.5 GHz", "RAM":"64GB", "HDD":"2TB"}');
INSERT INTO product VALUES (1, 'PC5','{"CPU":"2.8 GHz", "RAM":"4GB", "HDD":"2TB"}');
Insert a New Key-Value Pair in the JSON Field in PostgreSQL
Now, let’s say you want to add GPU to PC1 in the table. The key will be GPU, and the value will be RTX 3060.
We can insert this using the UPDATE command like the regular UPDATE query.
Syntax:
UPDATE table_name
SET json_field_name = json_field_name || new_key_value
The || operator will concatenate the new JSON object with the previous one. Now, to insert {"GPU":"RTX 3060"} to PC1, the SQL query will be:
UPDATE product
SET details = details || '{"GPU":"RTX 3060"}'
WHERE product='PC1';
Now the table will have the following:
postgres=# select * from product;
id | product | details
----+---------+--------------------------------------------------------------------
2 | PC2 | {"CPU": "3.1 GHz", "HDD": "1TB", "RAM": "8GB"}
3 | PC3 | {"CPU": "2.5 GHz", "HDD": "2.5TB", "RAM": "32GB"}
4 | PC4 | {"CPU": "3.5 GHz", "HDD": "2TB", "RAM": "64GB"}
5 | PC5 | {"CPU": "2.8 GHz", "HDD": "2TB", "RAM": "4GB"}
1 | PC1 | {"CPU": "4.1 GHz", "GPU": "RTX 3060", "HDD": "2TB", "RAM": "16GB"}
(5 rows)
You can see that PC1 got a new key-value "GPU": "RTX 3060".
Use JSONB_SET() to Update Existing Data in the JSON Field in PostgreSQL
Suppose you’ve been asked to update the PC1 processor to Ryzen 9. To modify an existing value in JSON data type, you need to use the JSONB_SET() method.
It takes 3 parameters: the JSON data type column, key, and value. So, updating the CPU in PC1 with Ryzen 9, we need to perform the following query:
UPDATE product
SET details = JSONB_SET(details,'{CPU}','"Ryzen 9"')
WHERE product='PC1';
After updating, the table has the following data:
postgres=# select * from product;
id | product | details
----+---------+--------------------------------------------------------------------
2 | PC2 | {"CPU": "3.1 GHz", "HDD": "1TB", "RAM": "8GB"}
3 | PC3 | {"CPU": "2.5 GHz", "HDD": "2.5TB", "RAM": "32GB"}
4 | PC4 | {"CPU": "3.5 GHz", "HDD": "2TB", "RAM": "64GB"}
5 | PC5 | {"CPU": "2.8 GHz", "HDD": "2TB", "RAM": "4GB"}
1 | PC1 | {"CPU": "Ryzen 9", "GPU": "RTX 3060", "HDD": "2TB", "RAM": "16GB"}
(5 rows)
We can see that the CPU is successfully updated.
When using JSONB_SET(), we need to make sure of the following things:
- The first parameter will be the JSON data type column.
- The second parameter will be the key name, wrapped in curly braces (inside single quotes).
- The third parameter will be the updated value placed inside single quotes.
Know more about the JSON data type for PostgreSQL in this blog. Also, you can visit the official documentation.
