How to update a JSON object in Postgres?

About

This page shows you how to update a JSON object stored in a JSONB column in Postgres.

Operation

Get

• Json Postgres data type

select
json_column->'property_name', -- as object
json_column->>'property_name' -- as text
FROM your_table;

• Json text: If the Json data is an escaped json string, you need to transform it first. Example:

"{\"name\":\"foo\"}"

(trim( replace(json_text::text,'\',''),'"'))::jsonb->'name'

Add/Update a property

The function used to manipulate JSON data is the jsonb_set function ¹⁾ that takes as argument:

• a literal JSON or the JSONB column
• The JSON path where to update the JSON.
  • Example: in {“a”: {“b”:{“c”: “foo”}}}, the JSON path {a,b} will return {“c”: “foo”}
• The new JSON value
• a boolean if the property should be created if missing

Example:

• Add or replace

select jsonb_set('{ "foo": 1 }' , '{bar}', 1::text::jsonb, true)

• output

{"bar": 1, "foo": 1}

Merge two objects

select '{ "foo": 1 }'::jsonb || '{ "foo":2, "bar":2}'::jsonb;

{"bar": 2, "foo": 2}

Advanced Examples Statement to add/set a property

Update

UPDATE foo
SET jsonb_col = jsonb_set(jsonb_col, '{bar}', 1::text::jsonb, true);

Update Aggregate

This example shows you how to update JSON data of a city that has resident.

We add the count of residents to the JSON object.

UPDATE city
SET json_data = jsonb_set(city.json_data, '{residentCount}', residentCount, true)
from (select city_resident.city_id as city_id, count(1)::text::jsonb as userCount
            from city_resident
            group by city_resident.city_id) as residentCountSelect
WHERE city.city_id= residentCountSelect.city_id;