jsonb_object_keys is a set-returning function: it emits one row per top-level key of a JSON object. It is indispensable when your data is semi-structured and you do not know up front which fields live in a column. With it you can explore the shape of the JSON, check whether expected fields are present, and build an inventory of keys across a whole table.
Syntax and a basic example
The signature is simple: jsonb_object_keys(jsonb). Because it returns a set, it produces rows rather than a single scalar. Say users has gained a prefs jsonb column holding profile settings.
SELECT jsonb_object_keys(prefs) AS key
FROM users
WHERE id = 42;
If prefs is {"theme": "dark", "lang": "en", "newsletter": true}, the query returns three rows: theme, lang, newsletter. A few behaviors worth knowing:
- Only top-level keys are returned. Nested objects are not unfolded.
- Key order is not guaranteed;
jsonb keeps its own internal ordering.
- For a non-object (array, number, string) you get zero rows or an error; only feed it objects.
- A
json_object_keys variant exists for the json type; same behavior, without normalization.
Discovering the shape of the data
When a jsonb column arrives from an external source, the first question is "which keys even appear in here?" Expanding the keys of every row and grouping them gives you a field map with frequencies.
SELECT key, COUNT(*) AS rows_with_key
FROM users u,
jsonb_object_keys(u.prefs) AS key
GROUP BY key
ORDER BY rows_with_key DESC;
This is the canonical pattern: a table and a set-returning function in the FROM clause form an implicit LATERAL join, so each user's keys expand into separate rows. The result immediately shows which fields are common and which are rare optional ones.
Gotcha: if prefs is NULL, that user's row simply vanishes from the result, because the function emits no rows. To keep such users, use an explicit LEFT JOIN LATERAL ... ON true, or filter WHERE prefs IS NOT NULL deliberately. Otherwise your survey silently skips part of the table.
Checking which optional fields are present
jsonb_object_keys helps you find rows that do (or do not) carry a particular field. For a single check the ? operator is handier, but iterating over keys is useful for dynamic conditions.
SELECT id, email
FROM users
WHERE prefs ? 'newsletter';
SELECT id, email
FROM users u
WHERE EXISTS (
SELECT 1
FROM jsonb_object_keys(u.prefs) AS key
WHERE key = 'newsletter'
);
The ? operator (contains a top-level key) is almost always faster and clearer for one field, especially with a GIN index on prefs. Iterating with jsonb_object_keys earns its keep when the key condition is non-trivial, for example "has at least one key with a flag_ prefix."
Collecting keys with array_agg
Often you want the opposite of an expansion: a compact list of keys per row. Pairing array_agg with a subquery over jsonb_object_keys folds the keys back into an array.
SELECT
u.id,
u.email,
(
SELECT array_agg(key ORDER BY key)
FROM jsonb_object_keys(u.prefs) AS key
) AS configured_keys
FROM users u
WHERE u.prefs IS NOT NULL;
ORDER BY key inside array_agg gives a stable, readable order instead of jsonb's internal one. Such an array is easy to compare across rows: find users with an unusual settings set, or build a "who has which fields filled in" report without dumping the whole JSON into the application.
Other databases
MySQL has no direct set-returning equivalent: JSON_KEYS(prefs) returns a JSON array of keys as a single value, not a set of rows. To expand it you reach for JSON_TABLE. In ClickHouse, JSON parsing uses JSONExtractKeys(prefs), which also yields an array; expand it into rows with arrayJoin. Remember the shared rule: in all three systems this is strictly about top-level keys, so nested objects must be parsed in a separate pass.
jsonb_object_keysis a set-returning function: it emits one row per top-level key of a JSON object. It is indispensable when your data is semi-structured and you do not know up front which fields live in a column. With it you can explore the shape of the JSON, check whether expected fields are present, and build an inventory of keys across a whole table.Syntax and a basic example
The signature is simple:
jsonb_object_keys(jsonb). Because it returns a set, it produces rows rather than a single scalar. Sayusershas gained aprefs jsonbcolumn holding profile settings.SELECT jsonb_object_keys(prefs) AS key FROM users WHERE id = 42;If
prefsis{"theme": "dark", "lang": "en", "newsletter": true}, the query returns three rows:theme,lang,newsletter. A few behaviors worth knowing:jsonbkeeps its own internal ordering.json_object_keysvariant exists for thejsontype; same behavior, without normalization.Discovering the shape of the data
When a
jsonbcolumn arrives from an external source, the first question is "which keys even appear in here?" Expanding the keys of every row and grouping them gives you a field map with frequencies.SELECT key, COUNT(*) AS rows_with_key FROM users u, jsonb_object_keys(u.prefs) AS key GROUP BY key ORDER BY rows_with_key DESC;This is the canonical pattern: a table and a set-returning function in the
FROMclause form an implicitLATERALjoin, so each user's keys expand into separate rows. The result immediately shows which fields are common and which are rare optional ones.Checking which optional fields are present
jsonb_object_keyshelps you find rows that do (or do not) carry a particular field. For a single check the?operator is handier, but iterating over keys is useful for dynamic conditions.-- users who configured a newsletter preference at all SELECT id, email FROM users WHERE prefs ? 'newsletter'; -- equivalent shape check via the keys set SELECT id, email FROM users u WHERE EXISTS ( SELECT 1 FROM jsonb_object_keys(u.prefs) AS key WHERE key = 'newsletter' );The
?operator (contains a top-level key) is almost always faster and clearer for one field, especially with a GIN index onprefs. Iterating withjsonb_object_keysearns its keep when the key condition is non-trivial, for example "has at least one key with aflag_prefix."Collecting keys with array_agg
Often you want the opposite of an expansion: a compact list of keys per row. Pairing
array_aggwith a subquery overjsonb_object_keysfolds the keys back into an array.SELECT u.id, u.email, ( SELECT array_agg(key ORDER BY key) FROM jsonb_object_keys(u.prefs) AS key ) AS configured_keys FROM users u WHERE u.prefs IS NOT NULL;ORDER BY keyinsidearray_agggives a stable, readable order instead ofjsonb's internal one. Such an array is easy to compare across rows: find users with an unusual settings set, or build a "who has which fields filled in" report without dumping the whole JSON into the application.Other databases
MySQL has no direct set-returning equivalent:
JSON_KEYS(prefs)returns a JSON array of keys as a single value, not a set of rows. To expand it you reach forJSON_TABLE. In ClickHouse, JSON parsing usesJSONExtractKeys(prefs), which also yields an array; expand it into rows witharrayJoin. Remember the shared rule: in all three systems this is strictly about top-level keys, so nested objects must be parsed in a separate pass.