How to Use jsonb_insert in PostgreSQL: Syntax and Examples

Use jsonb_insert in PostgreSQL to insert a new value into a JSONB column at a specified path without replacing existing data. The function takes the JSONB data, a path array to the insertion point, the new value as JSONB, and an optional boolean to control insertion behavior.

Syntax

The jsonb_insert function inserts a new JSONB value into an existing JSONB document at a specified path.

• jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])
• target: The original JSONB data.
• path: An array of keys/indices showing where to insert.
• new_value: The JSONB value to insert.
• insert_after (optional): If true, insert after the path location; if false or omitted, insert before.

sql

jsonb_insert(target jsonb, path text[], new_value jsonb, [insert_after boolean])

Example

This example shows how to insert a new key-value pair into a JSONB object inside a table.

sql

CREATE TABLE example (data jsonb);

INSERT INTO example VALUES ('{"name": "Alice", "hobbies": ["reading", "cycling"]}');

-- Insert a new hobby before "cycling"
SELECT jsonb_insert(data, '{hobbies,1}', '"swimming"'::jsonb) AS updated_data FROM example;

Output

{"name": "Alice", "hobbies": ["reading", "swimming", "cycling"]}

Common Pitfalls

Common mistakes when using jsonb_insert include:

• Using an incorrect path array that does not match the JSON structure.
• Forgetting to wrap string values in quotes when converting to JSONB.
• Not specifying insert_after correctly, which changes where the new value is inserted.
• Expecting jsonb_insert to replace existing keys instead of only inserting new values.

Example of wrong and right usage:

sql

-- Wrong: path points to a key that does not exist
SELECT jsonb_insert('{"a":1}'::jsonb, '{b}', '2'::jsonb);

-- Right: path points to existing key or array index
SELECT jsonb_insert('{"a":[1]}'::jsonb, '{a,1}', '2'::jsonb, true);

Output

{"a": 1}

{"a": [1, 2]}

Quick Reference

Parameter	Description
target	Original JSONB data to modify
path	Array of keys/indices to insertion point
new_value	JSONB value to insert
insert_after (optional)	Boolean to insert after (true) or before (false) the path location; defaults to false

Key Takeaways

Use jsonb_insert to add new values into JSONB data without replacing existing keys.

The path parameter is an array that specifies where to insert the new value inside the JSON structure.

Wrap string values in quotes when converting to JSONB for insertion.

The optional insert_after boolean controls whether insertion is before or after the specified path.

jsonb_insert does not update existing keys; it only inserts new elements.