Definition:
jsonb_set(target jsonb, path text[], new_value jsonb, [create_missing boolean])
Returns target with the section designated by path replaced by new_value, or with new_value added if create_missing is true (default is true) and the item designated by path does not exist. As with the path oriented operators, negative integers that appear in path count from the end of JSON arrays.
Let's take the below data set as an example:
[
{
"id": 1,
"type": "Place",
"details":'{ "name": "Taj Mahal", "url": "images/0001.jpg"}'
},
{
"id": 2,
"type": "Place",
"details":'{ "name": "Eiffel Tower", "url": "images/0002.jpg"}'
},
{
"id": 3,
"type": "Food",
"details":'{ "name": "Biryani", "url": "images/0003.jpg"}'
},
{
"id": 4,
"type": "Food",
"details":'{ "name": "Pizza", "url": "images/0004.jpg"}'
}
]
The model Example of the object in postgresql is something like this:
{
"id" smallint,
"type" character varying(50),
"details" text
}
Now, here are the following queries you can use in postgres to parse, update the object:
1. How to parse JSON value of text column
Let's fetch object of name Biryani from the above data set
SELECT * FROM "Example" WHERE "details"::json->>'name'='Biryani';
The above query will return:
-[ RECORD 1 ]---------------+---------------------------------------------
id | 3
type | Food
details | { "name": "Biryani", "url": "images/0003.jpg"}
2. How to update JSON value of text column
Here is the query to update url of Eiffel Tower object:
UPDATE "Example" SET "details"=jsonb_set("details"::jsonb, '{url}', '"images/0005.jpg"'
WHERE "details"::json->>'name'='Eiffel Tower';
now the updated object will look like:
-[ RECORD 1 ]---------------+---------------------------------------------
id | 2
type | Place
details | { "name": "Eiffel Tower", "url": "images/0005.jpg"}
Top comments (2)
Awesome article! Just what I'm looking for. One question if you may;
How to insert objects into jsonb column it such that after I insert others, the column
will look like
{
"2021-08-10":{...},
"2021-08-11":{...},
....
}
Thanks for this. Was confused why my string value was being "transformed" (or so it seemd) to a number, then saw the '"images/0005.jpg"' example and realized I needed to quote the string.