DEV Community

Deepika Banoth
Deepika Banoth

Posted on

How to: use jsonb_set function in PostgreSQL

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)

Collapse
 
yankzy profile image
Yankuba Kuyateh

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":{...},
....
}

Collapse
 
jvnlwn profile image
Joe Van Leeuwen

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.