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"}

Blog

How to: use jsonb_set function in PostgreSQL

Deepika Banoth

1. How to parse JSON value of text column

2. How to update JSON value of text column

Join Our Newsletter. No Spam, Only the good stuff.

Related