{}

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.

32 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.

array_to_json

PG 9.2+→ json

Converts a SQL array to a JSON array. With pretty_bool = true, line feeds are added between top-level elements. Multi-dimensional arrays become nested JSON arrays.

Developer

Signatures

array_to_json ( anyarray ) → json

array_to_json ( anyarray, pretty_bool boolean ) → json

Parameters

Parameter	Type	Description
anyarray	anyarray	SQL array to convert
pretty_bool	boolean	If true, add line feeds between top-level elements

Examples

Integer array to JSON array

sql

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

→[1,2,3]

Text array with proper JSON quoting

sql

SELECT array_to_json(ARRAY['a','b','c']);

→"a","b","c"]

2D array becomes nested JSON array

sql

SELECT array_to_json(ARRAY[[1,2],[3,4]]);

→[[1,2],[3,4]]

Aggregate IDs into a JSON array

sql

SELECT array_to_json(array_agg(id)) FROM products WHERE active;

→[1,5,9,...]

Pretty-printed with line feeds

sql

SELECT array_to_json(ARRAY[1,2,3], true);

→

[1,
2,
3]

⚠Anti-Pattern— Using array_to_json on composite type arrays

array_to_json() on a composite type array serialises each element as a JSON object but without field names — just positional values. This produces unreadable JSON.

✓ Instead: Use json_agg(row_to_json(t)) or json_agg(jsonb_build_object('key', val)) to get proper key-value JSON objects.

array_to_json(array_agg(x)) and json_agg(x) produce the same result, but json_agg() is simpler and slightly more efficient for aggregation use cases.

example

SELECT json_agg(id ORDER BY id) FROM products; -- cleaner than array_to_json(array_agg(id ORDER BY id))

→[1,2,3,...]

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

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_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

Common Gotchas

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

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

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

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

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_extract_path

PG 9.3+→ json

Extracts a JSON sub-object at the specified path. Equivalent to the #> operator. Each path element navigates one level deeper into the JSON structure.

Developer

Signature

json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json

Parameters

Parameter	Type	Description
from_json	json	Source JSON value
path_elems	text[]	One or more keys or array indices forming the path

Examples

Navigate two levels deep

sql

SELECT json_extract_path('{"a":{"b":1}}'::json, 'a', 'b');

→1

Array index navigation (0-based)

sql

SELECT json_extract_path('[1,[2,3]]'::json, '1', '0');

→2

Extract nested field

sql

SELECT json_extract_path(data, 'address', 'city') FROM users WHERE id = 1;

→"London"

First element of a JSON array

sql

SELECT json_extract_path(data, 'tags', '0') FROM articles;

→"postgresql"

#> operator: equivalent, shorter syntax

sql

SELECT data #> '{address,city}' AS city FROM users;

→"London"

⚠Anti-Pattern— Chaining multiple ->> operators for deep extraction

(doc->>'a')::json->>'b' breaks type safety and is verbose for deep paths.

✓ Instead: Use json_extract_path_text(doc, 'a', 'b') or doc #>> '{a,b}' for clean deep text extraction.

json_extract_path(doc, 'a', 'b') and doc #> '{a,b}' are identical. The #> operator is shorter in interactive queries; the function form is more readable in application code with variable paths.

example

SELECT data #> '{user,address,city}' FROM orders; -- same as json_extract_path(data,'user','address','city')

→"London"

json_extract_path_text

PG 9.3+→ text

Extracts a JSON sub-object at the specified path and returns it as text (with outer quotes stripped for strings). Equivalent to the #>> operator. Returns NULL if path does not exist.

Developer

Signature

json_extract_path_text ( from_json json, VARIADIC path_elems text[] ) → text

Parameters

Parameter	Type	Description
from_json	json	Source JSON value
path_elems	text[]	Path elements

Examples

Returns text without JSON quotes

sql

SELECT json_extract_path_text('{"a":{"b":"hello"}}'::json, 'a', 'b');

→hello

Direct text extraction for string fields

sql

SELECT json_extract_path_text(data, 'user', 'email') FROM sessions WHERE id = 1;

→user@example.com

Equivalent #>> operator

sql

SELECT data #>> '{user,email}' FROM sessions;

→user@example.com

Extract numeric as text then cast

sql

SELECT json_extract_path_text(data, 'count')::int AS count FROM stats;

→42

NULL when path absent

sql

SELECT json_extract_path_text(data, 'missing') IS NULL AS not_found FROM docs;

→true

⚠Anti-Pattern— Using json_extract_path() then trimming JSON quotes manually

json_extract_path() returns '"London"' (with JSON quotes); stripping them manually with trim() or replace() is fragile.

✓ Instead: Use json_extract_path_text() or the #>> operator to get the text value directly without JSON quoting.

data #>> '{a,b}' is the shorthand for json_extract_path_text(data,'a','b'). Use #>> in SQL WHERE/SELECT for readability; use the function form in application code where variable path lists are built programmatically.

example

SELECT data #>> '{shipping,city}' AS city FROM orders WHERE status = 'shipped';

→City as plain text

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_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

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_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

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

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

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

Common Gotchas

⚠

Foreign keys don't auto-create an index on the referencing column

PostgreSQL creates an index on the primary key side of a FK, but NOT on the foreign key column. Missing FK indexes cause slow lookups and lock escalation on DELETE.

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

jsonb_extract_path

PG 9.4+→ jsonb

JSONB variant of json_extract_path(). Extracts a JSONB sub-object at the specified path. Equivalent to the #> operator on jsonb columns. Keys in jsonb are stored sorted, so extraction is faster than json.

Developer

Signature

jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) → jsonb

Parameters

Parameter	Type	Description
from_json	jsonb	Source JSONB value
path_elems	text[]	One or more keys or array indices forming the path

Examples

Two-level extraction

sql

SELECT jsonb_extract_path('{"a":{"b":42}}'::jsonb, 'a', 'b');

→42

Config value as jsonb integer

sql

SELECT jsonb_extract_path(data, 'config', 'timeout') FROM apps WHERE id = 1;

→30

Equivalent #> operator syntax

sql

SELECT data #> '{config,timeout}' FROM apps;

→30

Array element + object key navigation

sql

SELECT jsonb_extract_path(profile, 'addresses', '0', 'city') FROM users;

→"Berlin"

Returns NULL when path does not exist

sql

SELECT jsonb_extract_path(data, 'nested', 'missing') IS NULL AS absent FROM docs;

→true

⚠Anti-Pattern— Using string casting to navigate jsonb

(data->>'config')::jsonb->>'timeout' works but creates an intermediate json/text parse for each level.

✓ Instead: Use jsonb_extract_path_text(data, 'config', 'timeout') for direct deep text extraction in a single operation.

JSONB stores keys in sorted binary form, making path extraction faster and enabling GIN index usage. If your column is jsonb, always use the jsonb_ variant.

example

SELECT jsonb_extract_path(data, 'user', 'id') FROM events WHERE jsonb_extract_path(data, 'type') = '"login"';

→User IDs from login events (can use GIN index on data)

jsonb_extract_path_text

PG 9.4+→ text

JSONB variant of json_extract_path_text(). Extracts a value at the specified path and returns it as plain text. Equivalent to the #>> operator on jsonb. Preferred over the json variant for jsonb columns.

Developer

Signature

jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[] ) → text

Parameters

Parameter	Type	Description
from_json	jsonb	Source JSONB value
path_elems	text[]	Path elements

Examples

Clean text extraction, no JSON quotes

sql

SELECT jsonb_extract_path_text('{"user":{"name":"Alice"}}'::jsonb, 'user', 'name');

→Alice

Nested field as text

sql

SELECT jsonb_extract_path_text(data, 'address', 'postcode') FROM customers;

→SW1A 1AA

Equivalent operator form

sql

SELECT data #>> '{address,postcode}' FROM customers;

→SW1A 1AA

Numeric config value cast to integer

sql

SELECT jsonb_extract_path_text(config, 'limits', 'max_requests')::int FROM apps;

→1000

Use for filtering in WHERE clauses

sql

SELECT jsonb_extract_path_text(payload, 'event', 'type') = 'purchase' FROM events;

→true/false

⚠Anti-Pattern— Chaining ->> on cast intermediate results

(data->'config')->>'timeout' is equivalent but creates an intermediate jsonb parse step. For deeper paths this compounds.

✓ Instead: Use jsonb_extract_path_text(data, 'config', 'timeout') to navigate to the text value in one call.

jsonb_extract_path_text() returns text, making it the ideal gateway for casting to typed SQL values. Chain with ::integer, ::boolean, ::timestamptz etc. for typed extraction without intermediate steps.

example

SELECT jsonb_extract_path_text(data,'created_at')::timestamptz FROM events;

→Timestamp parsed directly from nested JSON string

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

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

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

jsonb_path_match

PG12+→ boolean

Returns the boolean result of a jsonpath predicate check against a JSONB value. The path must return a single boolean. Returns NULL (not false) if no match. Use @? operator for existence check.

Developer

Signature

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

Parameters

Parameter	Type	Description
target	jsonb	JSONB document to evaluate the path against
path	jsonpath	SQL/JSON path predicate expression returning a boolean
vars	jsonb	Optional variables referenced in the path as $name
silent	boolean	If true, suppress errors; default false

Examples

Check specific field value

sql

SELECT jsonb_path_match('{"a":1}'::jsonb, '$.a == 1');

→true

Numeric comparison predicate

sql

SELECT jsonb_path_match('{"price":99}'::jsonb, '$.price < 100');

→true

Check if array contains value

sql

SELECT jsonb_path_match('{"tags":["sql","pg"]}'::jsonb, '$.tags[*] == "sql"');

→true

Parameterised path with vars

sql

SELECT jsonb_path_match(data, '$.status == $s', jsonb_build_object('s','active')) FROM orders;

→(rows)

Complex predicate for filtering

sql

SELECT id FROM products WHERE jsonb_path_match(attrs, '$.stock > 0 && $.price < 50');

→(rows)

⚠Anti-Pattern— Using jsonb_path_match for existence checks

jsonb_path_match requires the path to return a single boolean. For checking whether a path exists at all (returns any value), use jsonb_path_exists() or the @? operator instead.

✓ Instead: Use jsonb_path_exists(doc, '$.field') or doc @? '$.field' to test existence; use jsonb_path_match for boolean predicate evaluation.

jsonb_path_match(doc, path) is equivalent to doc @@ path. The @@ operator is shorter and indexable when used with a GIN index on the jsonb column.

example

SELECT * FROM products WHERE attrs @@ '$.price < 100 && $.stock > 0';

→Products under $100 with stock available (can use GIN index)

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_query_array

PG12+→ jsonb

Executes a jsonpath query against a JSONB value and returns all results as a JSON array. Unlike jsonb_path_query() which returns a set of rows, this returns a single JSONB array value.

Developer

Signature

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

Parameters

Parameter	Type	Description
target	jsonb	JSONB document to query
path	jsonpath	SQL/JSON path expression
vars	jsonb	Optional named variables for the path
silent	boolean	Suppress errors if true

Examples

Filter array elements > 2 into a new array

sql

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

→[3, 4, 5]

Extract all item names into an array

sql

SELECT jsonb_path_query_array(data, '$.items[*].name') FROM orders WHERE id = 1;

→["Widget","Gadget"]

Flatten nested arrays

sql

SELECT jsonb_path_query_array('{"a":[1,2],"b":[3,4]}'::jsonb, '$.*.[]');

→[1, 2, 3, 4]

Filter array elements by string prefix

sql

SELECT jsonb_path_query_array(tags, '$[*] ? (@ starts with "pg")') FROM articles;

→["pg","postgresql"]

Parameterised query with vars

sql

SELECT jsonb_path_query_array(data, '$.orders[*].total ? (@ > $min)', jsonb_build_object('min', 100)) FROM customers;

→[150.0, 200.5]

⚠Anti-Pattern— Using jsonb_path_query + array_agg for simple array extraction

SELECT array_agg(val) FROM jsonb_path_query(doc, path) requires a lateral join or subquery. jsonb_path_query_array() does it in one call.

✓ Instead: Use jsonb_path_query_array(doc, path) instead of (SELECT array_agg(v) FROM jsonb_path_query(doc, path) v) for cleaner syntax.

jsonb_path_query_array() returns results as a JSONB array in a single value — no aggregation needed. This simplifies queries where you need the results as a JSON array rather than a SQL result set.

example

SELECT jsonb_path_query_array(doc, '$.items[*].id') AS item_ids FROM orders;

→Each row gets a JSONB array of item IDs

Common Gotchas

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

jsonb_path_query_first

PG12+→ jsonb

Executes a jsonpath query and returns only the first result as a JSONB value, or NULL if no match. Useful when you expect at most one result or only need the first match.

Developer

Signature

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

Parameters

Parameter	Type	Description
target	jsonb	JSONB document to query
path	jsonpath	SQL/JSON path expression
vars	jsonb	Optional named variables for the path
silent	boolean	Suppress errors if true

Examples

Extract single field value

sql

SELECT jsonb_path_query_first('{"a":1,"b":2}'::jsonb, '$.a');

→1

First element matching filter

sql

SELECT jsonb_path_query_first('[10,20,30]'::jsonb, '$[*] ? (@ > 15)');

→20

First item name (as JSONB string)

sql

SELECT jsonb_path_query_first(data, '$.items[0].name') FROM orders WHERE id = 1;

→"Widget"

First login event timestamp

sql

SELECT jsonb_path_query_first(data, '$.events[*] ? (@.type == "login").timestamp') FROM sessions;

→"2026-01-15T08:00:00Z"

Extract and cast to SQL integer

sql

SELECT (jsonb_path_query_first(config, '$.timeout'))::int FROM apps;

→30

⚠Anti-Pattern— Using jsonb_path_query in a LIMIT 1 subquery

SELECT ... FROM jsonb_path_query(doc, path) LIMIT 1 requires a lateral subquery for inline use. jsonb_path_query_first() gives the same result directly.

✓ Instead: Use jsonb_path_query_first(doc, path) instead of (SELECT val FROM jsonb_path_query(doc, path) val LIMIT 1).

jsonb_path_query_first() is the cleanest way to extract a single scalar from JSONB. It returns NULL (not an error) when the path has no match, making it safe for nullable fields.

example

SELECT (jsonb_path_query_first(profile, '$.age'))::int AS age FROM users;

→Age as integer, NULL when absent

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_set_lax

PG14+→ jsonb

Like jsonb_set(), but adds control over how SQL NULL new_value is handled via null_value_treatment: raise_exception, use_json_null, delete_key, or return_target. Added in PostgreSQL 14.

Developer

Signature

jsonb_set_lax ( target jsonb, path text[], new_value jsonb [, create_missing boolean [, null_value_treatment text]] ) → jsonb

Parameters

Parameter	Type	Description
target	jsonb	The JSONB document to modify
path	text[]	Path to the key to set
new_value	jsonb	New value; can be SQL NULL
create_missing	boolean	If true, create the key if path does not exist; default true
null_value_treatment	text	One of: 'raise_exception', 'use_json_null', 'delete_key', 'return_target'; default 'use_json_null'

Examples

NULL → JSON null

sql

SELECT jsonb_set_lax('{"a":1}'::jsonb, '{b}', NULL, true, 'use_json_null');

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

NULL → remove the key entirely (delete_key)

sql

SELECT jsonb_set_lax('{"a":1}'::jsonb, '{a}', NULL, true, 'delete_key');

→{"a": 1}

NULL → leave target unchanged (return_target)

sql

SELECT jsonb_set_lax('{"a":1}'::jsonb, '{a}', NULL, true, 'return_target');

→{"a": 1}

Remove price key when override IS NULL, set otherwise

sql

SELECT jsonb_set_lax(doc, '{price}', price_override::jsonb, true, 'delete_key') FROM products;

→(rows)

Raise exception on NULL to catch data quality issues

sql

SELECT jsonb_set_lax('{}'::jsonb, '{x}', NULL, true, 'raise_exception');

→ERROR: JSON value must not be null

⚠Anti-Pattern— Using CASE to handle NULL in jsonb_set()

jsonb_set(doc, '{k}', NULL::jsonb) passes JSON null — not a no-op. To conditionally set-or-delete based on a SQL NULL, a CASE becomes verbose.

✓ Instead: Use jsonb_set_lax(doc, '{k}', val, true, 'delete_key') to handle SQL NULL gracefully in one call.

jsonb_set_lax with 'delete_key' elegantly handles the pattern: set the field when a value is present, remove it when NULL. Replaces complex CASE expressions.

example

UPDATE docs SET data = jsonb_set_lax(data, '{expires}', $1::jsonb, true, 'delete_key') WHERE id = $2;

→expires field set when $1 is not NULL, removed when $1 is NULL

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

jsonb_strip_nulls

PG 9.5+→ jsonb

Returns the JSONB value with all object fields that have null values removed. Array elements are not affected — only null-valued object keys are stripped.

Developer

Signature

jsonb_strip_nulls ( from_json jsonb ) → jsonb

Parameters

Parameter	Type	Description
from_json	jsonb	JSONB value to strip null-valued keys from

Examples

Null key b removed

sql

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

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

Recursively strips nested null keys

sql

SELECT jsonb_strip_nulls('{"x":null,"y":{"z":null,"w":1}}'::jsonb);

→{"y": {"w": 1}}

Array nulls are NOT stripped (only object keys)

sql

SELECT jsonb_strip_nulls('[1,null,3]'::jsonb);

→[1, null, 3]

Strip nullable columns when serialising a row

sql

SELECT jsonb_strip_nulls(row_to_json(u)::jsonb) FROM users u WHERE id = 1;

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

Clean up old records in bulk

sql

UPDATE events SET metadata = jsonb_strip_nulls(metadata) WHERE date < current_date - 30;

→(rows updated)

⚠Anti-Pattern— Expecting array nulls to be stripped

jsonb_strip_nulls() only removes object keys whose values are null. JSON null values inside arrays (e.g. [1, null, 3]) are left intact.

✓ Instead: To remove nulls from arrays, use jsonb_path_query_array(arr, '$[*] ? (@ != null)') from PostgreSQL 12+.

Run jsonb_strip_nulls() before INSERT/UPDATE to avoid accumulating explicit null keys that waste storage and complicate queries. Especially valuable for event logs where most optional fields are absent.

example

INSERT INTO events (data) VALUES (jsonb_strip_nulls(jsonb_build_object('type', $1, 'user_id', $2, 'extra', $3)));

→Document without extra key when $3 is null

Common Gotchas

⚠

NULL kills comparisons silently — nothing equals NULL, not even NULL

NULL = NULL is not TRUE. It is NULL. Queries filtering for NULL with = will silently return zero rows.

⚠

COUNT(col) skips NULLs but COUNT(*) does not

COUNT(column_name) counts non-NULL values only. COUNT(*) counts all rows. These produce different results when the column has NULLs.

⚠

Arrays are 1-indexed in PostgreSQL, not 0-indexed

PostgreSQL arrays start at index 1 by default. array[0] returns NULL, not the first element — silently wrong.

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

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": "..."}

PostgreSQL JSON & JSONB Functions

Jump to function

What are PostgreSQL JSON & JSONB Functions?

array_to_json

Signatures

Parameters

Examples

Common Gotchas

json_array_elements

Signatures

Parameters

Examples

json_array_elements_text

Signatures

Parameters

Examples

json_array_length

Signatures

Parameters

Examples

Common Gotchas

json_build_array

Signature

Parameters

Examples

Common Gotchas

json_build_object

Signature

Parameters

Examples

json_each

Signatures

Parameters

Examples

json_each_text

Signatures

Parameters

Examples

json_extract_path

Signature

Parameters

Examples

json_extract_path_text

Signature

Parameters

Examples

json_object

Signatures

Parameters

Examples

json_object_keys

Signatures

Parameters

Examples

json_populate_record

Signatures

Parameters

Examples

json_strip_nulls

Signatures

Parameters

Examples

json_to_record

Signatures

Parameters

Examples

json_typeof

Signatures

Parameters

Examples

jsonb delete (#- operator)

Signatures

Parameters

Examples

Common Gotchas

jsonb_build_object

Signature

Parameters

Examples

jsonb_extract_path