How to Use jsonb_each in PostgreSQL: Syntax and Examples

In PostgreSQL, jsonb_each is a function that expands a JSONB object into a set of key-value pairs, returning each key and its corresponding value as separate rows. You use it in the FROM clause to turn JSONB data into a table format for easy querying.

Syntax

The jsonb_each function takes a single jsonb object as input and returns a set of rows with two columns: key and value. Each row represents one key-value pair from the JSONB object.

• jsonb_each(jsonb): Expands the JSONB object into key-value pairs.
• key: The key from the JSONB object as text.
• value: The value from the JSONB object as JSONB.

sql

SELECT * FROM jsonb_each('{"name": "Alice", "age": 30}'::jsonb);

Output

    key    | value 
-----------+-------
 name      | "Alice"
 age       | 30
(2 rows)

Example

This example shows how to use jsonb_each to extract keys and values from a JSONB column in a table. It demonstrates turning JSONB data into rows for easier filtering and analysis.

sql

CREATE TEMP TABLE users(id serial, info jsonb);

INSERT INTO users(info) VALUES
('{"name": "Bob", "city": "Paris"}'),
('{"name": "Carol", "city": "Berlin", "age": 25}');

SELECT id, key, value
FROM users, jsonb_each(info);

Output

 id |  key  |  value  
----+-------+---------
  1 | name  | "Bob"  
  1 | city  | "Paris"
  2 | name  | "Carol"
  2 | city  | "Berlin"
  2 | age   | 25
(5 rows)

Common Pitfalls

One common mistake is using jsonb_each on a JSONB array instead of an object, which causes an error because jsonb_each only works with JSONB objects. Another pitfall is forgetting to use jsonb_each in the FROM clause with a comma or LATERAL join, which is necessary to expand the JSONB data into rows.

sql

/* Wrong: Using jsonb_each on a JSONB array */
SELECT * FROM jsonb_each('[1, 2, 3]'::jsonb);

/* Right: Use jsonb_array_elements for arrays */
SELECT * FROM jsonb_array_elements('[1, 2, 3]'::jsonb);

Output

ERROR:  cannot call jsonb_each on a non-object

 value 
-------
 1 
 2 
 3 
(3 rows)

Quick Reference

Function	Description	Input Type	Output Columns
jsonb_each(jsonb)	Expands JSONB object into key-value pairs	jsonb object	key (text), value (jsonb)
jsonb_array_elements(jsonb)	Expands JSONB array into elements	jsonb array	value (jsonb)
json_each(json)	Expands JSON object into key-value pairs	json object	key (text), value (json)
json_array_elements(json)	Expands JSON array into elements	json array	value (json)

Key Takeaways

Use jsonb_each to turn a JSONB object into rows of key-value pairs for easy querying.

jsonb_each only works with JSONB objects, not arrays; use jsonb_array_elements for arrays.

Always use jsonb_each in the FROM clause with a comma or LATERAL join to expand JSONB data.

The output columns are key (text) and value (jsonb), allowing flexible data access.

jsonb_each is useful for analyzing or filtering JSONB data stored in PostgreSQL tables.