{}

PostgreSQL JSON & JSONB Functions

Complete reference for PostgreSQL JSON and JSONB functions covering construction, querying, modification, and path operations. Every function includes syntax, parameters, real SQL examples, and performance notes on JSONB indexing. Updated for PostgreSQL 16.

22 functions

What are PostgreSQL JSON & JSONB Functions?

PostgreSQL JSON functions are built-in functions for storing, querying, and manipulating semi-structured data using JSON and JSONB column types. JSONB is the binary storage format that supports GIN indexing and is preferred for most use cases. Common PostgreSQL JSON functions include json_build_object(), jsonb_set(), jsonb_agg(), jsonb_each(), and the -> and ->> extraction operators.

to_json

PG 9.2+→ json

Converts any SQL value to JSON. Composite types and arrays become JSON objects and arrays respectively.

DeveloperData Eng

Signature

to_json ( anyelement ) → json

Parameters

Parameter	Type	Description
value	any	Value to convert to JSON

Examples

sql

SELECT to_json(42);

→42

sql

SELECT to_json('hello'::text);

→"hello"

sql

SELECT to_json(ARRAY[1,2,3]);

→[1,2,3]

sql

SELECT to_json(now());

→"2025-03-07T14:23:01.123456+00:00"

⚠Anti-Pattern— Using JSON type for queryable data

Storing data as `json` (not `jsonb`) prevents you from using GIN indexes, the `@>` containment operator, and JSONPath queries. Every lookup requires a full sequential scan and text parsing.

✓ Instead: Use `jsonb` for any column you will filter, index, or query. Reserve `json` only when you need to preserve exact whitespace/key order for round-trip fidelity.

Use `to_jsonb` when you need the result stored as JSONB or when you'll query it with JSONB operators. `to_json` produces a JSON string, `to_jsonb` produces the binary JSONB type.

example

SELECT to_jsonb(row(id, name, created_at)) FROM users LIMIT 1;

→JSONB representation of the row

to_jsonb

PG 9.4+→ jsonb

Converts any SQL value to JSONB. Like to_json but returns the binary JSONB type.

DeveloperData Eng

Signature

to_jsonb ( anyelement ) → jsonb

Parameters

Parameter	Type	Description
value	any	Value to convert to JSONB

Examples

sql

SELECT to_jsonb(42);

→42

sql

SELECT to_jsonb(ARRAY['a','b']);

→["a","b"]

sql

SELECT to_jsonb(row(1, 'Alice', true));

→{"f1":1,"f2":"Alice","f3":true}

sql

UPDATE events SET payload = to_jsonb(NEW) WHERE id = NEW.id;

→Whole row stored as JSONB

⚠Anti-Pattern— Converting large composite types repeatedly in a loop

Calling `to_jsonb` inside a PL/pgSQL loop for each row of a large result set is slow. The conversion happens row-by-row without the set-based optimizations available in plain SQL.

✓ Instead: Use `SELECT to_jsonb(t.*) FROM my_table t` as a single set-returning query, or use `json_agg(to_jsonb(t.*))` to aggregate in one pass.

Wrap a record in `to_jsonb(row.*) FROM table row` to get a JSONB object per row, suitable for building API JSON responses directly in SQL.

example

SELECT to_jsonb(u.*) AS user_json FROM users u WHERE id = 1;

→{"id": 1, "name": "Alice", "email": "..."}

row_to_json

PG 9.2+→ json

Converts a row (composite type) to a JSON object. Optionally pretty-prints with newlines.

DeveloperData Eng

Signature

row_to_json ( record [, pretty_bool boolean] ) → json

Parameters

Parameter	Type	Description
record	record or composite type	Row to convert
pretty	boolean	If true, adds newlines between fields for readability

Examples

sql

SELECT row_to_json(t.*) FROM users t WHERE id = 1;

→{"id":1,"name":"Alice","email":"alice@example.com"}

sql

SELECT row_to_json(row(1, 'hello', true));

→{"f1":1,"f2":"hello","f3":true}

sql

SELECT row_to_json(t.*, true) FROM products t WHERE id = 5;

→Pretty-printed JSON with newlines between fields

sql

SELECT json_agg(row_to_json(o.*)) AS orders FROM orders o WHERE user_id = 1;

→[{"id":10,"total":99.99,...},{...}]

⚠Anti-Pattern— Using row_to_json when column selection or renaming is needed

`row_to_json(t.*)` always includes every column with its schema name. This can accidentally expose internal fields (e.g., `password_hash`, `deleted_at`) and uses database column names as API keys.

✓ Instead: Use `json_build_object('key', col, ...)` to explicitly select and rename fields, or select into a CTE/subquery with only the desired columns before calling `row_to_json`.

Combine `row_to_json(t.*)` with `json_agg` to get an array of objects: `json_agg(row_to_json(t.*))`. This builds nested JSON responses without application-side assembly.

example

SELECT u.id, json_agg(row_to_json(o.*)) AS orders FROM users u JOIN orders o ON o.user_id = u.id GROUP BY u.id;

→User with nested array of order objects

json_build_object

PG 9.4+→ json

Builds a JSON object from alternating key-value pairs.

DeveloperData Eng

Signature

json_build_object ( VARIADIC "any" ) → json

Parameters

Parameter	Type	Description
key	text	Object key (even-numbered arguments)
value	any	Object value (odd-numbered arguments)

Examples

sql

SELECT json_build_object('name', 'Alice', 'age', 30);

→{"name": "Alice", "age": 30}

sql

SELECT json_build_object('id', id, 'label', name, 'ts', now()) FROM products;

→Custom JSON object per row

sql

SELECT json_build_object('user', row_to_json(u.*), 'roles', json_agg(r.name)) FROM users u JOIN user_roles r ON r.user_id = u.id GROUP BY u.id, u.*;

→Nested JSON with user and their roles array

sql

SELECT json_build_object('ok', true, 'data', null) WHERE false;

→(no rows — illustrates zero-row safety)

⚠Anti-Pattern— Building JSON in application code from SQL result sets

Fetching raw rows and constructing JSON objects in the application layer adds a round-trip and forces the app to assemble nested structures manually.

✓ Instead: Use `json_build_object` (or `jsonb_build_object`) server-side to shape the response in SQL, reducing data transfer and application complexity.

Use `json_build_object` to include only specific columns with custom key names — unlike `row_to_json` which includes all columns with their schema names.

example

SELECT json_build_object('userId', id, 'displayName', first_name || ' ' || last_name, 'email', email) FROM users;

→JSON with custom keys and computed fields

jsonb_build_object

PG 9.4+→ jsonb

Builds a JSONB object from alternating key-value pairs.

DeveloperData EngDBA

Signature

jsonb_build_object ( VARIADIC "any" ) → jsonb

Parameters

Parameter	Type	Description
key	text	Object key
value	any	Object value

Examples

sql

SELECT jsonb_build_object('name', 'Alice', 'scores', ARRAY[95, 87, 92]);

→{"name": "Alice", "scores": [95, 87, 92]}

sql

UPDATE users SET metadata = jsonb_build_object('plan', 'pro', 'seats', 10, 'trial', false) WHERE id = 42;

→JSONB metadata column updated

sql

SELECT jsonb_build_object('product', name, 'price', price, 'stock', inventory_count) FROM products WHERE active;

→One JSONB object per active product

sql

SELECT jsonb_build_object('summary', jsonb_agg(jsonb_build_object('id', id, 'total', total))) FROM orders WHERE status = 'pending';

→Nested JSONB: {"summary": [{"id":1,"total":49.99}, ...]}

⚠Anti-Pattern— Storing highly relational data as JSONB objects

Using `jsonb_build_object` to pack many foreign-key relationships (e.g., order + customer + address + line items) into a single JSONB blob trades queryability and referential integrity for perceived convenience.

✓ Instead: Keep relational data in normalized tables. Use `jsonb_build_object` for genuinely semi-structured, variable, or document-oriented data where the schema is not fixed.

If the resulting JSON will be stored in a `jsonb` column or queried with JSONB operators, use `jsonb_build_object` — it produces JSONB directly without an extra cast.

example

UPDATE users SET metadata = jsonb_build_object('plan', plan_name, 'seats', seat_count) WHERE id = $1;

→JSONB metadata set directly

json_build_array

PG 9.4+→ json

Builds a JSON array from the provided arguments.

DeveloperData Eng

Signature

json_build_array ( VARIADIC "any" ) → json

Parameters

Parameter	Type	Description
val	any	Values to include in the array

Examples

sql

SELECT json_build_array(1, 2, 3);

→[1, 2, 3]

sql

SELECT json_build_array('a', true, null, 42);

→["a", true, null, 42]

sql

SELECT json_build_array(id, name, email) FROM users WHERE id = 7;

→[7, "Alice", "alice@example.com"]

sql

SELECT json_build_array(json_build_object('x', 1), json_build_object('x', 2));

→[{"x": 1}, {"x": 2}]

⚠Anti-Pattern— Building arrays of uniform typed values with json_build_array instead of array_to_json

When all values share the same SQL type, using `json_build_array(v1, v2, ...)` with hard-coded arguments is verbose and inflexible compared to SQL arrays.

✓ Instead: For uniform-type collections, collect into a SQL array first (`ARRAY[v1, v2, ...]` or `array_agg`) then use `array_to_json`. Reserve `json_build_array` for heterogeneous or dynamically shaped outputs.

Unlike SQL arrays (which require uniform types), `json_build_array` accepts mixed types: strings, numbers, booleans, nulls, and even nested JSON objects all in one array.

example

SELECT json_build_array(id, name, is_active, created_at) AS record_tuple FROM users;

→Mixed-type JSON array per row

json_object

PG 9.2+→ json

Builds a JSON object from an array of alternating key-value pairs, or from two separate key and value arrays.

DeveloperData Eng

Signatures

json_object ( text[] ) → json

json_object ( keys text[], values text[] ) → json

Parameters

Parameter	Type	Description
text[]	text[]	Array of alternating key/value strings, or keys array
values	text[]	Values array (when using two-argument form)

Examples

sql

SELECT json_object('{a,1,b,2}');

→{"a":"1","b":"2"}

sql

SELECT json_object(ARRAY['k1','k2'], ARRAY['v1','v2']);

→{"k1":"v1","k2":"v2"}

sql

SELECT json_object(ARRAY['host','port','db'], ARRAY[hostname, port::text, dbname]) FROM servers;

→Connection config as JSON per server

sql

SELECT json_object(array_agg(key), array_agg(value)) FROM settings WHERE app_id = 3;

→Aggregated key-value pairs as a JSON object

⚠Anti-Pattern— All values are coerced to text — numeric types lose type information

`json_object` accepts only `text[]` — all values are stored as JSON strings, even numbers. `{"count":"42"}` is not the same as `{"count":42}` for downstream consumers.

✓ Instead: Use `json_build_object` when values have distinct SQL types (int, boolean, numeric) to preserve the correct JSON type in the output.

When you have two parallel arrays (keys and values), `json_object(keys, values)` is cleaner than calling `json_build_object` with individual arguments. Useful when building JSON from dynamic column lists.

example

SELECT json_object(ARRAY['host','port','db'], ARRAY[hostname, port::text, dbname]) AS conn_json FROM servers;

→Connection config as JSON object

json_array_length

PG 9.4+→ integer

Returns the number of elements in the outermost JSON array.

DeveloperData Eng

Signatures

json_array_length ( json ) → integer

jsonb_array_length ( jsonb ) → integer

Parameters

Parameter	Type	Description
json_array	json or jsonb	A JSON/JSONB array value

Examples

sql

SELECT json_array_length('[1,2,3,4]');

→4

sql

SELECT jsonb_array_length(tags) FROM posts WHERE id = 1;

→Number of tags

sql

SELECT * FROM posts WHERE jsonb_array_length(images) > 3;

→Posts with more than 3 images

sql

SELECT id, jsonb_array_length(COALESCE(line_items, '[]'::jsonb)) AS item_count FROM orders ORDER BY item_count DESC LIMIT 10;

→Top 10 orders by number of line items

⚠Anti-Pattern— Extracting the array into application code to count its length

Fetching a JSONB column into the application and calling `.length` on the parsed array is wasteful — the full array must be serialized and transferred over the network.

✓ Instead: Use `jsonb_array_length(col)` directly in the WHERE or SELECT clause to let the database compute the count without transferring data.

Use `jsonb_array_length(col) > 0` to check for non-empty JSON arrays. This is faster than extracting and counting elements.

example

SELECT id, jsonb_array_length(items) AS item_count FROM carts WHERE jsonb_array_length(items) > 0;

→Non-empty carts with item count

json_array_elements

PG 9.4+→ setof json (or setof jsonb)

Expands a JSON array to a set of JSON values, one row per element.

DeveloperData Eng

Signatures

json_array_elements ( json ) → setof json

jsonb_array_elements ( jsonb ) → setof jsonb

Parameters

Parameter	Type	Description
json_array	json or jsonb	JSON array to expand

Examples

sql

SELECT json_array_elements('[1,2,3]');

→1, 2, 3 (3 rows)

sql

SELECT p.id, e.value AS tag FROM posts p, jsonb_array_elements(p.tags) AS e(value);

→One row per tag per post

sql

SELECT e ->> 'name' FROM jsonb_array_elements('[{"name":"Alice"},{"name":"Bob"}]'::jsonb) AS e;

→Alice, Bob

sql

SELECT o.id, SUM((item->>'price')::numeric * (item->>'qty')::int) AS total FROM orders o, jsonb_array_elements(o.line_items) AS item GROUP BY o.id;

→Order total computed from JSONB line items

⚠Anti-Pattern— Using json_each in a loop to iterate array elements

Calling `json_array_elements` inside a PL/pgSQL `FOR` loop processes one element at a time in procedural fashion, losing the benefits of set-based SQL execution.

✓ Instead: Use `json_array_elements` in a FROM clause (lateral join) so the database can process all elements in a single set-based operation.

Combine with a FROM clause to unnest a JSON array per row: `FROM orders o, jsonb_array_elements(o.items) AS item`. Then query `item ->> 'sku'` or `(item ->> 'price')::numeric`.

example

SELECT o.id, (item ->> 'name') AS product, (item ->> 'qty')::int AS qty FROM orders o, jsonb_array_elements(o.line_items) AS item;

→One row per line item per order

json_array_elements_text

PG 9.4+→ setof text

Expands a JSON array of strings to a set of text values (JSON quotes stripped).

DeveloperData EngSecurity

Signatures

json_array_elements_text ( json ) → setof text

jsonb_array_elements_text ( jsonb ) → setof text

Parameters

Parameter	Type	Description
json_array	json or jsonb	JSON array of string values

Examples

sql

SELECT json_array_elements_text('["a","b","c"]');

→a, b, c (3 rows)

sql

SELECT jsonb_array_elements_text(tags) FROM posts;

→Individual tag strings

sql

SELECT id FROM users WHERE 'admin' = ANY(ARRAY(SELECT jsonb_array_elements_text(roles)));

→Users who have the 'admin' role

sql

SELECT count(*) FROM orders, jsonb_array_elements_text(tags) t WHERE t = 'urgent';

→Count of orders tagged urgent

⚠Anti-Pattern— Trusting unnested user-supplied JSON strings without sanitisation

Unnesting user-supplied JSON string arrays with `jsonb_array_elements_text` and embedding the results directly into dynamic SQL or shell commands can enable injection.

✓ Instead: Always treat unnested text values as untrusted input. Use parameterised queries and validate values against an allowlist before further processing.

Use `json_array_elements_text` instead of `json_array_elements` when the array contains strings — it returns plain `text` instead of JSON-quoted values, saving you the extra `->>` or `#>>` call.

example

SELECT jsonb_array_elements_text(permissions) AS perm FROM roles WHERE role_name = 'admin';

→Permission strings as plain text

json_each

PG 9.4+→ setof record with columns (key text, value json/jsonb)

Expands a JSON object to a set of key-value rows.

DeveloperData EngDBA

Signatures

json_each ( json ) → setof (key text, value json)

jsonb_each ( jsonb ) → setof (key text, value jsonb)

Parameters

Parameter	Type	Description
json_object	json or jsonb	JSON object to expand

Examples

sql

SELECT * FROM json_each('{"a":1,"b":2}');

→

key | value
a   | 1
b   | 2

sql

SELECT key, value FROM jsonb_each(metadata) WHERE key LIKE 'pref_%' FROM users;

→Preference keys and values

sql

SELECT key, jsonb_typeof(value) AS val_type FROM jsonb_each('{"id":1,"active":true,"name":"Alice"}'::jsonb);

→

key    | val_type
id     | number
active | boolean
name   | string

sql

SELECT u.id, e.key, e.value FROM users u, jsonb_each(u.settings) AS e WHERE u.active;

→All settings key-value pairs for active users

⚠Anti-Pattern— Using json_each in a PL/pgSQL loop for row-by-row processing

Iterating over `json_each` results inside a procedural loop forces row-at-a-time processing and prevents query parallelism.

✓ Instead: Use `json_each` in a lateral FROM clause to let the planner optimise the full query as a set-based join.

Use `json_each` to iterate over dynamic JSON keys — useful when the set of keys is not known in advance. Combine with `jsonb_each_text` to get plain text values.

example

SELECT key, value::text FROM jsonb_each(config) FROM app_settings WHERE app_id = 1;

→Each config key-value pair as a separate row

json_each_text

PG 9.4+→ setof record with columns (key text, value text)

Expands a JSON object to key-value rows with both key and value as text (JSON quoting removed).

DeveloperData Eng

Signatures

json_each_text ( json ) → setof (key text, value text)

jsonb_each_text ( jsonb ) → setof (key text, value text)

Parameters

Parameter	Type	Description
json_object	json or jsonb	JSON object to expand

Examples

sql

SELECT * FROM json_each_text('{"a":"hello","b":42}');

→

key | value
a   | hello
b   | 42

sql

SELECT key, value FROM jsonb_each_text(attributes) WHERE value ILIKE '%organic%' FROM products;

→Attribute keys containing 'organic'

sql

SELECT key, value FROM jsonb_each_text(config) FROM app_settings WHERE app_id = 1 ORDER BY key;

→All config entries as plain text key-value pairs

sql

SELECT key, length(value) AS val_len FROM jsonb_each_text(description_fields) FROM articles WHERE id = 10;

→Character length of each description field value

⚠Anti-Pattern— Using json_each_text when values are nested objects or arrays

`json_each_text` converts all values to their text representation. Nested objects become strings like `{"x":1}` which you then have to re-parse, losing type safety.

✓ Instead: Use `json_each` (not `_text`) when values may be nested objects or arrays so they remain as `json`/`jsonb` and can be passed to further JSON functions without re-casting.

`json_each_text` returns values as plain `text` (JSON unquoted), while `json_each` returns them as `json`. Use `_text` when you'll do string operations like LIKE or length() on the values.

example

SELECT key, value FROM jsonb_each_text(attributes) WHERE value ILIKE '%organic%' FROM products;

→Attribute keys containing 'organic'

json_object_keys

PG 9.4+→ setof text

Returns the set of keys in the outermost JSON object.

DeveloperData EngDBA

Signatures

json_object_keys ( json ) → setof text

jsonb_object_keys ( jsonb ) → setof text

Parameters

Parameter	Type	Description
json_object	json or jsonb	JSON object to inspect

Examples

sql

SELECT json_object_keys('{"a":1,"b":2,"c":3}');

→a, b, c (3 rows)

sql

SELECT jsonb_object_keys(settings) FROM app_config;

→Names of all config keys

sql

SELECT DISTINCT key FROM user_profiles, jsonb_object_keys(profile_data) AS key ORDER BY 1;

→All unique keys present across the profile_data column

sql

SELECT id, key FROM products, jsonb_object_keys(attributes) AS key WHERE key NOT IN ('color','size','weight');

→Products with unexpected attribute keys

⚠Anti-Pattern— Checking for a specific key existence using json_object_keys

Running `SELECT key FROM jsonb_object_keys(doc) WHERE key = 'target'` to test whether a key exists is unnecessarily expensive — it expands all keys then filters.

✓ Instead: Use the `?` operator: `doc ? 'target'`. This is direct, O(log n) in JSONB, and can be supported by a GIN index.

Use `json_object_keys` with `array_agg` to collect all unique keys across rows: `SELECT DISTINCT key FROM products, jsonb_object_keys(attributes) AS key`. This reveals the schema of a semi-structured column.

example

SELECT DISTINCT key FROM user_profiles, jsonb_object_keys(profile_data) AS key ORDER BY 1;

→All unique profile data keys in the table

jsonb_set

PG 9.5+→ jsonb

Returns target with the item at the given path replaced by new_value, or creates it if create_if_missing is true.

DeveloperData EngDBA

Signature

jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean] ) → jsonb

Parameters

Parameter	Type	Description
target	jsonb	Source JSONB value
path	text[]	Array of keys/indices forming the path to the target field
new_value	jsonb	New value to set at the path
create_if_missing	boolean	If true, create missing path segments (default true)

Examples

sql

SELECT jsonb_set('{"a":1,"b":2}'::jsonb, '{b}', '42');

→{"a": 1, "b": 42}

sql

SELECT jsonb_set(data, '{user,email}', '"new@example.com"') FROM profiles;

→Updates nested email field

sql

UPDATE users SET metadata = jsonb_set(metadata, '{last_login}', to_jsonb(now())) WHERE id = $1;

→Updates last_login in metadata

sql

UPDATE products SET attributes = jsonb_set(attributes, '{stock}', to_jsonb(stock_count), true) FROM inventory WHERE inventory.product_id = products.id;

→Upserts stock count from inventory into product JSONB attributes

⚠Anti-Pattern— Not indexing JSONB columns that are queried with @>

After updating JSONB with `jsonb_set`, queries that filter with `@>` (containment) on the same column will do sequential scans if no GIN index exists, making queries slow at scale.

✓ Instead: Create a GIN index: `CREATE INDEX ON table USING gin(jsonb_col)`. This makes `@>` and `?` operators index-backed and dramatically faster.

To update multiple JSONB fields in one UPDATE, chain `jsonb_set` calls: `jsonb_set(jsonb_set(data, '{a}', val_a), '{b}', val_b)`. Alternatively use `||` (concatenation) for top-level key replacement.

example

UPDATE items SET props = props || jsonb_build_object('updated_at', now(), 'version', props->>'version'::int + 1) WHERE id = $1;

→Multiple top-level JSONB fields updated atomically

jsonb_insert

PG 9.6+→ jsonb

Inserts new_value into target at the specified path. For arrays, inserts before (or after with insert_after=true) the indexed element. For objects, behaves like jsonb_set.

DeveloperData Eng

Signature

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

Parameters

Parameter	Type	Description
target	jsonb	Source JSONB value
path	text[]	Path to the insertion point (last element is array index)
new_value	jsonb	Value to insert
insert_after	boolean	If true, insert after rather than before (default false)

Examples

sql

SELECT jsonb_insert('[1,2,3]', '{1}', '42');

→[1, 42, 2, 3]

sql

SELECT jsonb_insert('[1,2,3]', '{1}', '42', true);

→[1, 2, 42, 3]

sql

SELECT jsonb_insert(cart, '{items,-1}', item_json::jsonb, true) FROM carts;

→Appends item to end of items array

sql

UPDATE audit_log SET changes = jsonb_insert(changes, '{0}', jsonb_build_object('ts', now(), 'user', $1)) WHERE session_id = $2;

→Prepends a new change entry to the front of the changes array

⚠Anti-Pattern— Using jsonb_insert to replace an existing value

`jsonb_insert` raises an error if the object key already exists (unlike `jsonb_set`). Mistakenly using it for updates instead of inserts causes runtime errors.

✓ Instead: Use `jsonb_set` when you want to update an existing key. Use `jsonb_insert` only when adding a new element to an array or a new key that does not yet exist.

Use path `{items,-1}` with `insert_after=true` to append to a JSONB array directly in an UPDATE statement — no need to read, modify in app code, and write back.

example

UPDATE sessions SET events = jsonb_insert(events, '{-1}', to_jsonb(now()), true) WHERE id = $1;

→New event appended to JSON array

jsonb delete (#- operator)

PG 9.4+→ jsonb

Deletes a key from a JSONB object, an index from an array, or a nested path (with #- operator).

DeveloperData EngSecurity

Signatures

jsonb - text → jsonb

jsonb - text[] → jsonb

jsonb - integer → jsonb

jsonb #- text[] → jsonb

Parameters

Parameter	Type	Description
jsonb	jsonb	Source JSONB value
key	text or integer	Key name or array index to remove
path	text[]	Path to the nested key to remove (with #- operator)

Examples

sql

SELECT '{"a":1,"b":2,"c":3}'::jsonb - 'b';

→{"a": 1, "c": 3}

sql

SELECT '[1,2,3]'::jsonb - 1;

→[1, 3]

sql

SELECT '{"a":{"b":1,"c":2}}'::jsonb #- '{a,b}';

→{"a": {"c": 2}}

sql

UPDATE users SET profile = profile - ARRAY['ssn','dob','internal_score'] WHERE id = $1;

→Removes multiple sensitive keys from the profile JSONB in one statement

⚠Anti-Pattern— Relying on key deletion as a security boundary without allowlisting

Using `-` to remove a known list of sensitive keys (blocklist approach) is fragile — if a new sensitive key is added to the JSONB document, it will be exposed until the deletion list is updated.

✓ Instead: Use an allowlist approach: construct the response object explicitly with `jsonb_build_object('safe_key', doc->>'safe_key', ...)` to include only what clients should see.

Use the `-` operator to strip sensitive fields like `password_hash` or `internal_notes` from JSONB objects before returning them in API responses.

example

SELECT data - 'password_hash' - 'internal_id' AS safe_data FROM users WHERE id = $1;

→User data without sensitive fields

json_typeof

PG 9.4+→ text

Returns the type of a JSON value as a text string: 'object', 'array', 'string', 'number', 'boolean', or 'null'.

DeveloperData Eng

Signatures

json_typeof ( json ) → text

jsonb_typeof ( jsonb ) → text

Parameters

Parameter	Type	Description
value	json or jsonb	JSON value to inspect

Examples

sql

SELECT json_typeof('42'::json);

→number

sql

SELECT json_typeof('"hello"'::json);

→string

sql

SELECT json_typeof('[1,2]'::json);

→array

sql

SELECT jsonb_typeof(payload) FROM events WHERE id = 1;

→object

⚠Anti-Pattern— Skipping type checks before casting JSON values

Casting a JSON value directly (e.g., `(col->>'price')::numeric`) without first checking `jsonb_typeof(col->'price') = 'number'` can produce errors or unexpected NULLs when the value is missing or of the wrong type.

✓ Instead: Guard casts with a type check: `CASE WHEN jsonb_typeof(col->'price') = 'number' THEN (col->>'price')::numeric END`, or use `jsonb_path_query` with a typed filter.

Use `json_typeof` to validate that a stored JSON column contains the expected type before operating on it. Prevents errors when code assumes an array but finds an object.

example

SELECT id FROM events WHERE jsonb_typeof(payload) != 'object';

→Events with unexpected payload structure

json_strip_nulls

PG 9.4+→ json or jsonb

Removes all object fields with null values from the JSON value recursively. Null array elements are preserved.

DeveloperData Eng

Signatures

json_strip_nulls ( json ) → json

jsonb_strip_nulls ( jsonb ) → jsonb

Parameters

Parameter	Type	Description
json	json or jsonb	JSON value to strip nulls from

Examples

sql

SELECT json_strip_nulls('{"a":1,"b":null,"c":3}');

→{"a": 1, "c": 3}

sql

SELECT jsonb_strip_nulls('{"name":"Alice","phone":null}');

→{"name": "Alice"}

sql

UPDATE profiles SET data = jsonb_strip_nulls(data || $1::jsonb) WHERE user_id = $2;

→Merges patch and strips nulls in one step

sql

SELECT jsonb_strip_nulls(to_jsonb(t.*)) FROM optional_fields_table t;

→Row as JSONB with all NULL columns removed

⚠Anti-Pattern— Assuming null array elements are stripped

`json_strip_nulls` only removes object keys whose value is null. Null values inside arrays (e.g., `[1, null, 3]`) are preserved intentionally. Relying on it to clean arrays will produce unexpected results.

✓ Instead: To remove nulls from JSON arrays, use `jsonb_array_elements` to unnest, filter out nulls with `WHERE value != 'null'::jsonb`, then re-aggregate with `jsonb_agg`.

When performing a PATCH-style update, use `jsonb_strip_nulls(patch) || existing_data` — or vice versa — to merge only non-null fields from the patch into the stored document.

example

UPDATE profiles SET data = data || jsonb_strip_nulls($1::jsonb) WHERE user_id = $2;

→Null fields in the patch are ignored, not overwritten

jsonb_path_query

PG 12+→ setof jsonb

Returns all JSONB items returned by the jsonpath expression applied to the target.

DeveloperData Eng

Signature

jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent boolean]] ) → setof jsonb

Parameters

Parameter	Type	Description
target	jsonb	The JSONB document to query
path	jsonpath	JSONPath expression
vars	jsonb	Optional JSONB object providing named variables in the path
silent	boolean	If true, suppress errors; return empty set instead

Examples

sql

SELECT jsonb_path_query(doc, '$.items[*].name') FROM orders;

→All item names across all orders

sql

SELECT jsonb_path_query('[1,2,3,4,5]', '$[*] ? (@ > 2)');

→3, 4, 5

sql

SELECT jsonb_path_query(payload, '$.events[*] ? (@.type == $type)', '{"type":"click"}'::jsonb) FROM sessions;

→All click events extracted using a named variable

sql

SELECT jsonb_path_query(doc, '$.store.books[*] ? (@.price < 15).title') FROM catalog;

→Titles of books under $15

⚠Anti-Pattern— Using ->> and casting instead of jsonb_path_query for complex navigation

Chaining multiple `->>` and `->` operators to navigate deeply nested structures (`col->'a'->'b'->>'c'`) is verbose, error-prone, and returns NULL silently at any missing intermediate level.

✓ Instead: Use `jsonb_path_query(col, '$.a.b.c')` with the `silent` flag set to `true` to safely traverse nested paths and get an empty set (rather than NULL) when the path does not exist.

Use `? (@ condition)` in JSONPath to filter array elements: `$.items[*] ? (@.price > 100)` returns only expensive items without unnesting and re-aggregating.

example

SELECT jsonb_path_query(cart, '$.items[*] ? (@.qty > 1)') FROM carts;

→Items with quantity > 1

jsonb_path_exists

PG 12+→ boolean

Returns true if the jsonpath expression returns any items for the target document.

DeveloperData Eng

Signature

jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent boolean]] ) → boolean

Parameters

Parameter	Type	Description
target	jsonb	The JSONB document
path	jsonpath	JSONPath expression to check

Examples

sql

SELECT jsonb_path_exists(doc, '$.status ? (@ == "active")') FROM subscriptions;

→true if status is 'active'

sql

SELECT * FROM orders WHERE jsonb_path_exists(items, '$.* ? (@.qty > 10)');

→Orders with any high-quantity item

sql

SELECT id FROM products WHERE jsonb_path_exists(attributes, '$.certifications[*] ? (@ == "ISO9001")');

→Products with ISO9001 certification

sql

SELECT * FROM events WHERE NOT jsonb_path_exists(payload, '$.error');

→Events with no error field in payload

⚠Anti-Pattern— Not using a GIN index when filtering with @? or jsonb_path_exists

Filtering rows with `jsonb_path_exists` or the `@?` operator without a GIN index results in a sequential scan — every row's JSONB document is parsed for every query.

✓ Instead: Create a GIN index (`CREATE INDEX ON table USING gin(jsonb_col)`) so that `@>` and `@?` queries can use the index. For JSONPath, consider expression indexes on specific extracted values.

`jsonb_path_exists(doc, path)` is equivalent to `doc @? path`. The operator form is more concise in WHERE clauses. Use `@@` for `jsonb_path_match`.

example

SELECT * FROM products WHERE attributes @? '$.certifications[*] ? (@ == "organic")';

→Products with organic certification in their JSON attributes

json_populate_record

PG 9.2+→ anyelement

Populates a record of the specified type from a JSON object, mapping JSON keys to record fields by name.

DeveloperData EngSecurity

Signatures

json_populate_record ( base anyelement, from_json json ) → anyelement

jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement

Parameters

Parameter	Type	Description
base	record type (can be NULL)	Prototype record determining the output type; use NULL cast to the desired type
from_json	json or jsonb	JSON object to populate from

Examples

sql

SELECT * FROM json_populate_record(NULL::users, '{"id":1,"name":"Alice"}');

→

id | name
1  | Alice

sql

SELECT (json_populate_record(NULL::address_type, addr_json)).* FROM deliveries;

→Typed address fields

sql

INSERT INTO products SELECT * FROM jsonb_populate_record(NULL::products, $1::jsonb);

→Inserts a row from a JSON payload with automatic type coercion

sql

SELECT (jsonb_populate_record(NULL::order_type, payload)).total FROM incoming_requests;

→Extracts typed 'total' field from JSON payload

⚠Anti-Pattern— Inserting user-supplied JSON directly via json_populate_record without validation

Passing raw user input to `json_populate_record` and inserting the result maps arbitrary JSON keys to table columns. A malicious payload with extra keys (e.g., `is_admin`, `role`) could populate sensitive fields if they exist on the target type.

✓ Instead: Validate the JSON structure against an allowlist of expected keys before calling `json_populate_record`. Alternatively, explicitly extract only the needed fields with `->` operators or use a restricted composite type.

Use `json_populate_record(NULL::table_type, json)` to parse JSON API payloads into typed rows for validation and insertion. Fields missing in the JSON get their column default or NULL.

example

INSERT INTO events SELECT * FROM json_populate_recordset(NULL::event_type, '[{...},{...}]'::json);

→Bulk JSON insert with type coercion

json_to_record

PG 9.4+→ record (column types defined in AS clause)

Expands a JSON object to a row with explicitly defined columns using a column definition list.

DeveloperData Eng

Signatures

json_to_record ( json ) → record

jsonb_to_record ( jsonb ) → record

Parameters

Parameter	Type	Description
json	json or jsonb	JSON object to expand

Examples

sql

SELECT * FROM json_to_record('{"a":1,"b":"two","c":null}') AS x(a integer, b text, c boolean);

→

a | b   | c
1 | two | NULL

sql

SELECT t.user_id, t.action, t.ts FROM jsonb_to_record('{"user_id":42,"action":"login","ts":"2025-01-01T00:00:00Z"}'::jsonb) AS t(user_id bigint, action text, ts timestamptz);

→

user_id | action | ts
42      | login  | 2025-01-01 00:00:00+00

sql

SELECT r.name, r.price FROM api_payloads, jsonb_to_record(body::jsonb) AS r(name text, price numeric, discontinued boolean) WHERE r.price < 50;

→Products under $50 extracted from raw API payloads

sql

INSERT INTO events (user_id, event_type, occurred_at) SELECT t.user_id, t.event_type, t.occurred_at FROM webhook_raw w, jsonb_to_record(w.payload::jsonb) AS t(user_id bigint, event_type text, occurred_at timestamptz);

→Typed insert from raw webhook JSONB payloads

⚠Anti-Pattern— Using json_to_record for bulk processing without json_to_recordset

Calling `json_to_record` in a loop or with `LATERAL` on every row of a JSON array individually is much slower than expanding the whole array at once.

✓ Instead: Use `json_to_recordset(json_array) AS t(col1 type, ...)` to expand an entire JSON array of objects into a typed result set in a single operation.

`json_to_record` lets you declare exactly which fields to extract and their types — useful when the JSON contains many fields but you only care about a few.

example

SELECT t.user_id, t.action FROM jsonb_to_record(event_payload::jsonb) AS t(user_id bigint, action text, ts timestamptz) FROM audit_log;

→Only desired fields extracted and typed

PostgreSQL JSON & JSONB Functions

Jump to function

What are PostgreSQL JSON & JSONB Functions?

to_json

Signature

Parameters

Examples

to_jsonb

Signature

Parameters

Examples

row_to_json

Signature

Parameters

Examples

json_build_object

Signature

Parameters

Examples

jsonb_build_object

Signature

Parameters

Examples

json_build_array

Signature

Parameters

Examples

json_object

Signatures

Parameters

Examples

json_array_length

Signatures

Parameters

Examples

json_array_elements

Signatures

Parameters

Examples

json_array_elements_text

Signatures

Parameters

Examples

json_each

Signatures

Parameters

Examples

json_each_text

Signatures

Parameters

Examples

json_object_keys

Signatures

Parameters

Examples

jsonb_set

Signature

Parameters

Examples

jsonb_insert

Signature

Parameters

Examples

jsonb delete (#- operator)

Signatures

Parameters

Examples

json_typeof

Signatures

Parameters

Examples

json_strip_nulls

Signatures

Parameters

Examples

jsonb_path_query

Signature

Parameters

Examples

jsonb_path_exists