SQLearnAcademy
←Function reference
JSONPostgreSQL only

JSONB_EACH

Expands a JSON object into rows — one row per key–value pair.

Syntax

JSONB_EACH(<json object>)
JSONB_EACH_TEXT(<json object>)

Returns two columns: key — the key name, and value — its value as jsonb, or as text in JSONB_EACH_TEXT. Handy when the keys aren't known in advance: settings, product attributes, arbitrary properties.

Key–value pairs
SELECT *
FROM JSONB_EACH('{"color": "black", "storage": 256, "wireless": true}');
keyvalue
colorblack
storage256
wirelesstrue
Values as text — for filtering
SELECT key
FROM JSONB_EACH_TEXT('{"color": "black", "storage": 256, "wireless": true}')
WHERE value = 'black';
key
color

With the ordinary JSONB_EACH, the comparison value = 'black' would throw an error: there value is jsonb.

Watch out

Only for objects: an array throws cannot call jsonb_each on a non-object.

In JSONB_EACH, value is jsonb, so WHERE value = 'black' throws invalid input syntax for type json. For text, use JSONB_EACH_TEXT.

Nested objects aren't expanded: the value is the whole nested object. To go deeper, call JSONB_EACH on it again.