jsonb_each is a set-returning function: it takes a single JSON object and expands it into one row per (key, value) pair. It is the inverse of JSONB_OBJECT_AGG: where that folded rows into a map, this turns a map back into rows you can filter, join, and aggregate with ordinary SQL.
Syntax and a basic example
The function takes one jsonb argument and returns two columns: key of type text and value of type jsonb. The natural place to call it is in the FROM clause rather than the SELECT list, so each object entry becomes its own result row.
SELECT key, value
FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);
The result is two rows: ('theme', "dark") and ('lang', "en"). A few behaviors worth keeping in mind:
value stays jsonb, so string values come back quoted: "dark", not dark.- Row order is not guaranteed: an object is a map, not a list.
- If the argument is an array or scalar rather than an object, it raises an error. Expand an array with
jsonb_array_elements instead.
- On a
NULL input the function returns zero rows, not one row of NULL.
Expanding a JSON column into rows
Most often the object lives in a column, not a literal. Suppose users has a prefs jsonb field holding an arbitrary set of settings. To expand it into rows, put the function in FROM after a comma (this is an implicit LATERAL join):
SELECT u.id, u.email, e.key, e.value
FROM users AS u, jsonb_each(u.prefs) AS e
WHERE u.id = 1;
Each pair in prefs becomes its own row tied to the user. The real power is that the keys can be dynamic: you do not need to know their names in advance, the way you would with prefs->>'theme'. This is ideal for iterating over an unknown shape or counting which keys appear at all.
Gotcha: if prefs is ever NULL, the implicit join silently drops that user from the result. To keep the user row, use an explicit LEFT JOIN LATERAL ... ON true and feed it COALESCE(prefs, '{}'::jsonb).
Filtering and aggregating entries
Once object entries are rows, the whole ordinary SQL toolkit applies. Count how many keys each settings object has:
SELECT u.id, count(*) AS pref_count
FROM users AS u, jsonb_each(u.prefs) AS e
GROUP BY u.id;
Or find which keys are most common across the table, a classic way to survey "wild" JSON that was written without a schema:
SELECT e.key, count(*) AS used_by
FROM users AS u, jsonb_each(u.prefs) AS e
GROUP BY e.key
ORDER BY used_by DESC;
You can filter by key or by value. Find users with at least one setting equal to the string "on":
SELECT DISTINCT u.id, u.email
FROM users AS u, jsonb_each(u.prefs) AS e
WHERE e.value = '"on"'::jsonb;
Note the comparison: because value is jsonb, the literal must be valid JSON too, hence the quotes inside '"on"'.
The _text variant: when you do not want JSON values
If the values are really just text, the quotes around them only get in the way. That is what jsonb_each_text is for: it returns the same pair, but value is text, with no wrapping quotes.
SELECT key, value
FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);
The difference matters when you need to compare values or cast them to a number:
SELECT u.id, (e.value)::int AS limit_value
FROM users AS u, jsonb_each_text(u.prefs) AS e
WHERE e.key = 'rate_limit';
With jsonb_each the same thing would need a second cast to strip the type. Mind the gotcha: jsonb_each_text also stringifies nested objects and arrays, so they come back as a serialized JSON string, and a JSON null becomes a SQL NULL.
Notes on other databases
Expanding a JSON object into rows is squarely a PostgreSQL strength. MySQL has no direct jsonb_each equivalent: you typically pull keys with JSON_KEYS and then expand that array with JSON_TABLE, which is wordier. In ClickHouse, JSON objects are often stored as a Map type, and you iterate with mapKeys and mapValues plus arrayJoin rather than a dedicated function. When you need to turn a map back into rows and work with it in plain SQL, jsonb_each and jsonb_each_text in PostgreSQL stay the most direct tool.
jsonb_eachis a set-returning function: it takes a single JSON object and expands it into one row per(key, value)pair. It is the inverse ofJSONB_OBJECT_AGG: where that folded rows into a map, this turns a map back into rows you can filter, join, and aggregate with ordinary SQL.Syntax and a basic example
The function takes one
jsonbargument and returns two columns:keyof typetextandvalueof typejsonb. The natural place to call it is in theFROMclause rather than theSELECTlist, so each object entry becomes its own result row.SELECT key, value FROM jsonb_each('{"theme": "dark", "lang": "en"}'::jsonb);The result is two rows:
('theme', "dark")and('lang', "en"). A few behaviors worth keeping in mind:valuestaysjsonb, so string values come back quoted:"dark", notdark.jsonb_array_elementsinstead.NULLinput the function returns zero rows, not one row ofNULL.Expanding a JSON column into rows
Most often the object lives in a column, not a literal. Suppose
usershas aprefs jsonbfield holding an arbitrary set of settings. To expand it into rows, put the function inFROMafter a comma (this is an implicitLATERALjoin):SELECT u.id, u.email, e.key, e.value FROM users AS u, jsonb_each(u.prefs) AS e WHERE u.id = 1;Each pair in
prefsbecomes its own row tied to the user. The real power is that the keys can be dynamic: you do not need to know their names in advance, the way you would withprefs->>'theme'. This is ideal for iterating over an unknown shape or counting which keys appear at all.Filtering and aggregating entries
Once object entries are rows, the whole ordinary SQL toolkit applies. Count how many keys each settings object has:
SELECT u.id, count(*) AS pref_count FROM users AS u, jsonb_each(u.prefs) AS e GROUP BY u.id;Or find which keys are most common across the table, a classic way to survey "wild" JSON that was written without a schema:
SELECT e.key, count(*) AS used_by FROM users AS u, jsonb_each(u.prefs) AS e GROUP BY e.key ORDER BY used_by DESC;You can filter by key or by value. Find users with at least one setting equal to the string
"on":SELECT DISTINCT u.id, u.email FROM users AS u, jsonb_each(u.prefs) AS e WHERE e.value = '"on"'::jsonb;Note the comparison: because
valueisjsonb, the literal must be valid JSON too, hence the quotes inside'"on"'.The _text variant: when you do not want JSON values
If the values are really just text, the quotes around them only get in the way. That is what
jsonb_each_textis for: it returns the same pair, butvalueistext, with no wrapping quotes.-- value comes back as plain text: dark, not "dark" SELECT key, value FROM jsonb_each_text('{"theme": "dark", "lang": "en"}'::jsonb);The difference matters when you need to compare values or cast them to a number:
-- read a numeric flag straight from a dynamic key SELECT u.id, (e.value)::int AS limit_value FROM users AS u, jsonb_each_text(u.prefs) AS e WHERE e.key = 'rate_limit';With
jsonb_eachthe same thing would need a second cast to strip the type. Mind the gotcha:jsonb_each_textalso stringifies nested objects and arrays, so they come back as a serialized JSON string, and a JSONnullbecomes a SQLNULL.Notes on other databases
Expanding a JSON object into rows is squarely a PostgreSQL strength. MySQL has no direct
jsonb_eachequivalent: you typically pull keys withJSON_KEYSand then expand that array withJSON_TABLE, which is wordier. In ClickHouse, JSON objects are often stored as aMaptype, and you iterate withmapKeysandmapValuesplusarrayJoinrather than a dedicated function. When you need to turn a map back into rows and work with it in plain SQL,jsonb_eachandjsonb_each_textin PostgreSQL stay the most direct tool.