PostgreSQL jsonb_set() Function
Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_set() function to replace an existing value specified by a path with a new value in a JSON document.
Introduction to the PostgreSQL jsonb_set() function
The jsonb_set() function allows you to replace an existing value specified by a path with a new value in a JSON document of the JSONB type.
More specifically, the jsonb_set() function allows you to replace an array element or key/value in a JSON object, or nested combinations of them.
Here’s the syntax of the jsonb_set() function:
jsonb_set(
target jsonb,
path text[],
new_value jsonb
[, create_missing boolean]
)Code language: SQL (Structured Query Language) (sql)In this syntax:
target: This is the original JSON document of the JSONB type that you want to modify.path: This is an array of text elements that specifies the path to the key where you want to insert or update the data.new_valueis the new JSONB value that you want to set at the specified path.create_missing: This is an optional boolean parameter indicating whether you want to create missing keys if they do not exist. It defaults to true, meaning that the function will create a new key if you attempt to set it for a key that does not exist.
The jsonb_set() function returns the modified JSON document with the new_value set at a specified path.
PostgreSQL jsonb_set() function examples
Let’s explore some examples of using the PostgreSQL jsonb_set() function
1) Updating an existing element in a JSON array
The following example uses the jsonb_set() function to update an existing element in a JSON array:
SELECT jsonb_set('[1,2,3]', '{0}', '-1');Code language: SQL (Structured Query Language) (sql)Output:
jsonb_set
------------
[-1, 2, 3]
(1 row)In this example:
- The original array is
[1,2,3]. - The path
{0}indicates the first element of the array. - The number
-1is the new value.
The jsonb_set() function sets the first element of the array to -1 and returns the modified document.
To insert the number 4 after the 3rd element, you use a non-existing path to the 4th element as follows:
SELECT jsonb_set('[1,2,3]', '{4}', '4');Code language: SQL (Structured Query Language) (sql)Output:
jsonb_set
--------------
[1, 2, 3, 4]
(1 row)2) Updating an element in a nested JSON array
The following example uses the jsonb_set() function to update an element in a nested array:
SELECT
jsonb_set(
'[1,2,[4,5],6]', '{2,0}', '3'
);Output:
jsonb_set
-------------------
[1, 2, [3, 5], 6]
(1 row)In this example:
- The original array is [1,2,[4,5],6].
- The path {2, 0}, 2 specifies the second element of the array which is the nested array [4,5], and 0 specifies the first element of the nested array.
- 3 is the new value.
Therefore the jsonb_set() function changes the number 4 as the first element of the nested array [4,5] to 3.
3) Updating data in a JSON object
The following example uses the jsonb_set() to update the value of a key in a JSON object:
SELECT
jsonb_set('{"name": "Jane Doe"}', '{name}', '"Jane Smith"');Code language: SQL Output:
jsonb_set
------------------------
{"name": "Jane Smith"}
(1 row)In this example:
- {“name”: “Jane Doe”} is the original object.
- {name} is the path that indicates the name property (or key).
- “Jane Smith” is the new value to update.
Therefore, the jsonb_set() set the value of the name key in the JSON object to “Jane Smith”.
Note that if you attempt to set a key that does not exist, you’ll get an error, the jsonb_set will insert it. For example:
SELECT jsonb_set('{"name": "Jane Doe"}', '{age}', '25');Code language: JavaScript (javascript)Output:
jsonb_set
---------------------------------
{"age": 25, "name": "Jane Doe"}
(1 row)But if you set the create_missing parameter to false, the function will not insert a new key/value pair:
SELECT
jsonb_set(
'{"name": "Jane Doe"}', '{age}',
'25',
false
);Output:
jsonb_set
----------------------
{"name": "Jane Doe"}
(1 row)4) Updating a value in a nested JSON object
The following example uses the jsonb_set() to modify a key/value pair in a nested JSON object:
SELECT
jsonb_set(
'{"name":"John Doe", "address" : { "city": "San Francisco"}}',
'{address,city}', '"San Jose"'
);Output:
jsonb_set
-------------------------------------------------------
{"name": "John Doe", "address": {"city": "San Jose"}}
(1 row)In this example:
{"name":"John Doe", "address" : { "city": "San Francisco"}}is the original JSON object.{address, city}is a path that specifies theaddresskey whose value is an object and thecityis the key of theaddressobject that will be modified."San Jose"is the value of thecitykey.
Therefore, the jsonb_set() function updates the city with the value San Jose in the address object of the JSON document.
5) Updating an element in an array of a nested object
The following example uses the jsonb_set() to update an element in an array of a nested object
SELECT
jsonb_set(
'{"name": "John", "skills" : ["PostgreSQL", "API"]}',
'{skills,1}',
'"Web Dev"'
);Output:
jsonb_set
-------------------------------------------------------
{"name": "John", "skills": ["PostgreSQL", "Web Dev"]}
(1 row)
In this example:
{"name": "John", "skills" : ["PostgreSQL", "API"]}is the original JSON object.{skills,1}is a path that specifies the skills key, which is an array, and 1 specifies the second element of the array."Web Dev"is the new value to update.
The jsonb_set() function sets the second element of the skills array to "Web Dev".
6) Using the PostgreSQL jsonb_set() function with table data
We’ll show you how to use the jsonb_set() function to insert a new value into a JSON document and update it back to a table.
First, create a new table called employee_skills:
CREATE TABLE employee_skills(
id INT PRIMARY KEY,
data JSONB
);Second, insert rows into the employee_skills table:
INSERT INTO employee_skills(id, data)
VALUES
(1, '{"name": "John", "skills" : ["PostgreSQL", "API"]}'),
(2, '{"name": "Jane", "skills" : ["SQL","Java"]}')
RETURNING *;Output:
id | data
----+---------------------------------------------------
1 | {"name": "John", "skills": ["PostgreSQL", "API"]}
2 | {"name": "Jane", "skills": ["SQL", "Java"]}
(2 rows)Third, replace the first skill in the skills array of the employee id 1 with the new skill "Web Dev":
UPDATE
employee_skills
SET
data = jsonb_set(
data, '{skills,0}', '"Web Dev"'
)
WHERE
id = 1
RETURNING *;Code language: SQL (Structured Query Language) (sql)Output:
id | data
----+------------------------------------------------
1 | {"name": "John", "skills": ["Web Dev", "API"]}
(1 row)Summary
- Use the
jsonb_set()function to update a JSON document of the type JSONB.
mirror=> SELECT id, name, properties
FROM products;
id | name | properties
—-+———————–+—————————————————
1 | Ink Fusion T-Shirt | {“size”: [“S”, “M”, “L”, “XL”], “color”: “white”}
2 | ThreadVerse T-Shirt | {“size”: [“S”, “M”, “L”, “XL”], “color”: “black”}
3 | Design Dynamo T-Shirt | {“size”: [“S”, “M”, “L”, “XL”], “color”: “blue”}
(3 rows)
mirror=> UPDATE products SET properties = jsonb_set(properties, ‘{color}’, ‘”purple”‘) WHERE id = 3 RETURNING *;
id | name | properties
—-+———————–+—————————————————-
3 | Design Dynamo T-Shirt | {“size”: [“S”, “M”, “L”, “XL”], “color”: “purple”}
(1 row)
UPDATE 1
weicode 