Expand JSON attributes into rows ​

json_each turns the immediate children of a JSON object or array into queryable rows. It is useful when each attribute should become an observation, rather than remaining inside one result value.

SELECT key, value, type
FROM json_each('{"region":"west","retries":2,"enabled":true}')
ORDER BY key
FORMAT csv;

For an object, key identifies a field. For an array, it identifies an element position. The type column helps distinguish text, numeric, boolean and nested values before interpreting them. A nested object is still one immediate child; use json_tree when the task requires descendants too.

Expanding children changes cardinality: one document can produce many rows. Count the expanded attributes separately from the number of original documents. Restrict the keys or types in WHERE before aggregating values that should be comparable. Do not sum an attribute merely because its textual representation looks numeric.

For a fixed schema, use jsonb_to_record or jsonb_to_recordset with declared output types instead of treating every field as a generic attribute. For native arrays, membership queries often need only has, without any expansion.

See JSON functions and array functions.