PostgreSQL JSONB API Reference

`JSONB`

5 methods

Functions and operators for the binary JSON type.

jsonb_set(target jsonb, path text[], new_value jsonb, create_missing bool = true) -> jsonb

Returns target with the field at the given path set to new_value, optionally creating it.

Name	Type	Description
target	jsonb	Source JSON document.
path	text[]	Array of keys/indices to the target field.
new_value	jsonb	Replacement value.
create_missing	bool	If true, creates missing keys.

jsonb

postgresql

UPDATE profile
SET data = jsonb_set(data, '{address,city}', '"Berlin"', true)
WHERE id = 1;

jsonb_build_object(key text, value any, ...) -> jsonb

Builds a JSON object from alternating key/value arguments.

Name	Type	Description
key	text	Object key.
value	any	Value converted to JSON.

jsonb

postgresql

SELECT jsonb_build_object('id', id, 'name', name, 'admin', is_admin) FROM users;

jsonb @> jsonb -> bool

Returns true if the left JSON value contains the right value (top-level containment).

Name	Type	Description
left	jsonb	Container document.
right	jsonb	Contained document.

bool

postgresql

SELECT * FROM items WHERE tags @> '["sale"]';

jsonb ? text -> bool

Returns true if the JSON object has the given top-level key, or array contains the string.

Name	Type	Description
key	text	Key or array element to test.

bool

postgresql

SELECT * FROM config WHERE data ? 'feature_x';

jsonb_agg(expr ORDER BY ...) -> jsonb

Aggregates values into a JSON array, optionally ordered.

Name	Type	Description
expr	any	Expression aggregated per group.

jsonb

postgresql

SELECT user_id, jsonb_agg(id ORDER BY created_at) AS order_ids
FROM orders GROUP BY user_id;