JSONB modification functions in PostgreSQL

Introduction

JSONB modification functions let you change parts of JSON data stored in a database easily.

You want to update a specific value inside a JSON document without replacing the whole thing.

You need to add a new key and value to a JSON object stored in a table.

You want to remove a key from a JSON object inside your database.

You want to merge two JSON objects together inside a column.

You want to replace a nested value inside a JSONB column.

Syntax

PostgreSQL

jsonb_set(target jsonb, path text[], new_value jsonb, create_missing boolean DEFAULT true) RETURNS jsonb
jsonb_insert(target jsonb, path text[], new_value jsonb, insert_after boolean DEFAULT false) RETURNS jsonb
jsonb_strip_nulls(jsonb) RETURNS jsonb
jsonb_delete_path(target jsonb, path text[]) RETURNS jsonb

jsonb_set updates or adds a value at the specified path.

path is an array of keys or indexes to locate where to change inside the JSON.

Examples

Updates the value of age to 31.

PostgreSQL

SELECT jsonb_set('{"name": "John", "age": 30}'::jsonb, '{age}', '31'::jsonb);

Adds a new key city with value New York.

PostgreSQL

SELECT jsonb_set('{"name": "John"}'::jsonb, '{city}', '"New York"'::jsonb, true);

Inserts green before index 1 in the colors array.

PostgreSQL

SELECT jsonb_insert('{"colors": ["red", "blue"]}'::jsonb, '{colors,1}', '"green"'::jsonb, false);

Removes keys with null values.

PostgreSQL

SELECT jsonb_strip_nulls('{"a":1, "b":null}'::jsonb);

Sample Program

This query shows three JSONB modifications: updating age, adding a new key country, and removing null values.

PostgreSQL

WITH data AS (
  SELECT '{"user": {"name": "Alice", "age": 25, "city": "Boston"}}'::jsonb AS info
)
SELECT
  jsonb_set(info, '{user,age}', '26'::jsonb) AS updated_age,
  jsonb_set(info, '{user,country}', '"USA"'::jsonb, true) AS added_country,
  jsonb_strip_nulls('{"a":1, "b":null}'::jsonb) AS no_nulls
FROM data;

Important Notes

Paths in jsonb_set use curly braces and commas, like {user,age}.

Use jsonb_strip_nulls to clean JSON objects by removing keys with null values.

Remember that JSONB functions return new JSONB values; they do not change data in place unless used in an UPDATE statement.

Summary

JSONB modification functions help update, add, or remove parts of JSON data inside PostgreSQL.

They use paths to locate where to make changes inside nested JSON objects or arrays.

These functions return new JSONB values, so use them in SELECT or UPDATE queries to see changes.