PostgreSQL jsonb_each Function

The jsonb_each() function in PostgreSQL expands the top-level JSON object into a set of key-value pairs, with one row per key. It's essential for transforming semi-structured JSON data into relational rows for analysis and reporting.

Syntax

-- Returns set of (key TEXT, value JSONB)
jsonb_each(jsonb_object)

-- Text variant - returns set of (key TEXT, value TEXT)
jsonb_each_text(jsonb_object)

-- JSON variant (less efficient)
json_each(json_object)
json_each_text(json_object)

Official Documentation

Example usage

-- Sample data
SELECT jsonb_each('{"name": "John", "age": 30, "city": "NYC"}'::JSONB);
-- Result:
-- key  | value
-- -----|-------
-- name | "John"
-- age  | 30
-- city | "NYC"

-- Expand JSON object into rows
SELECT key, value
FROM jsonb_each('{"a": 1, "b": 2, "c": 3}'::JSONB);

-- Use with table data
CREATE TABLE settings (
    user_id INT,
    preferences JSONB
);

SELECT
    user_id,
    key AS setting_name,
    value AS setting_value
FROM settings, jsonb_each(preferences);

-- Get all keys from JSON
SELECT DISTINCT key
FROM products, jsonb_each(metadata)
ORDER BY key;

-- Filter by specific keys
SELECT
    product_id,
    key,
    value
FROM products, jsonb_each(attributes)
WHERE key IN ('color', 'size', 'material');

-- Convert JSON object to table
SELECT
    key AS property,
    value::TEXT AS property_value
FROM jsonb_each('{"brand": "Apple", "model": "iPhone", "year": 2025}'::JSONB);

-- Use jsonb_each_text for text values
SELECT key, value
FROM jsonb_each_text('{"name": "Alice", "role": "Admin"}'::JSONB);
-- Result values are text, not JSONB

-- Combine with other JSON functions
SELECT
    user_id,
    key AS tag_key,
    jsonb_array_length(value) AS tag_count
FROM users, jsonb_each(tags)
WHERE jsonb_typeof(value) = 'array';

-- Create normalized table from JSON
SELECT
    id,
    (jsonb_each(data)).key AS attribute_name,
    (jsonb_each(data)).value AS attribute_value
FROM products;

-- Count occurrences of each key
SELECT
    key,
    COUNT(*) AS occurrence_count
FROM products, jsonb_each(metadata)
GROUP BY key
ORDER BY occurrence_count DESC;

-- Find products with specific attribute
SELECT product_id, product_name
FROM products
WHERE EXISTS (
    SELECT 1
    FROM jsonb_each(attributes) AS attrs
    WHERE attrs.key = 'warranty' AND attrs.value::TEXT LIKE '%lifetime%'
);

-- Pivot JSON object into columns
SELECT
    user_id,
    MAX(CASE WHEN key = 'theme' THEN value::TEXT END) AS theme,
    MAX(CASE WHEN key = 'language' THEN value::TEXT END) AS language,
    MAX(CASE WHEN key = 'timezone' THEN value::TEXT END) AS timezone
FROM users, jsonb_each(preferences)
GROUP BY user_id;

Common issues

1. Only top-level: jsonb_each only expands the top level, not nested objects.
2. Not for arrays: Use jsonb_array_elements for arrays instead.
3. Function in FROM: Must use in FROM clause or with LATERAL join.
4. Value type: jsonb_each returns JSONB values; use jsonb_each_text for text values.

Best practices

1. Use jsonb_each for JSONB columns (more efficient than json_each).
2. Use jsonb_each_text when you need text values directly without casting.
3. Combine with WHERE to filter specific keys before expanding.
4. Use LATERAL join for correlating with other table columns.

Frequently Asked Questions

Q: What's the difference between jsonb_each and jsonb_each_text?
A: jsonb_each returns values as JSONB type, while jsonb_each_text returns values as TEXT. Use jsonb_each_text when you need plain text values and don't need to preserve JSON structure.

Q: How do I expand nested JSON objects?
A: jsonb_each only expands the top level. For nested objects, extract the nested object first: SELECT * FROM jsonb_each(data -> 'nested') or use recursive queries for arbitrary depth.

Q: Can I use jsonb_each with arrays?
A: No, jsonb_each is for objects only. For arrays, use jsonb_array_elements to expand array elements into rows.

Q: How do I use jsonb_each in a JOIN?
A: Use LATERAL join: FROM table t, LATERAL jsonb_each(t.json_column) or FROM table t CROSS JOIN LATERAL jsonb_each(t.json_column).

Q: What does jsonb_each return for NULL or non-object JSON?
A: For NULL input, it returns an empty set (zero rows). For non-object JSON (like arrays or primitives), it raises an error. Check type with jsonb_typeof first.