How to update a JSON object in Postgres?

Postgres Elephant


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



  • Json Postgres data type
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:
(trim( replace(json_text::text,'\',''),'"'))::jsonb->'name'

Add/Update a property

The function used to manipulate JSON data is the jsonb_set function 1) 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


  • 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


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.

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;

Share this page:
Follow us:
Task Runner