Postgres jsonb_each() function

The jsonb_each function in Postgres is used to expand a JSONB object into a set of key-value pairs.

It is useful when you need to iterate over a JSONB object's keys and values, such as when you're working with dynamic JSONB structures where the schema is not fixed. Another important use case is performing data transformations and analytics.

Function signature

jsonb_each(json JSON) -> SETOF record(key text, value json)

The function returns a set of rows, each containing a key and the corresponding value for each field in the input JSONB object. The key is of type text, while the value is of type JSONB.

Example usage

Consider a JSONB object representing a user's profile information. The JSONB data will have multiple attributes and might look like this:

{
  "username": "johndoe",
  "age": 30,
  "email": "johndoe@example.com"
}

We can go over all the fields in the profile JSONB object using jsonb_each, and produce a row for each key-value pair.

SELECT key, value
FROM jsonb_each('{"username": "johndoe", "age": 30, "email": "johndoe@example.com"}');

This query returns the following results:

| key      | value                 |
|----------|-----------------------|
| username | "johndoe"             |
| age      | 30                    |
| email    | "johndoe@example.com" |

Advanced examples

Assign custom names to columns output by jsonb_each

You can use AS to specify custom column names for the key and value columns.

SELECT attr_name, attr_value
FROM jsonb_each('{"username": "johndoe", "age": 30, "email": "johndoe@example.com"}')
AS user_data(attr_name, attr_value);

This query returns the following results:

| attr_name | attr_value            |
|-----------|-----------------------|
| username  | "johndoe"             |
| age       | 30                    |
| email     | "johndoe@example.com" |

Use jsonb_each output as a table or row source

Since jsonb_each returns a set of rows, you can use it as a table source in a FROM clause. This lets us join the expanded JSONB data in the output with other tables.

Here, we're joining each row in the user_data table with the output of jsonb_each:

CREATE TABLE user_data (
    id INT,
    profile JSON
);
INSERT INTO user_data (id, profile)
VALUES
    (123, '{"username": "johndoe", "age": 30, "email": "johndoe@example.com"}'),
    (140, '{"username": "mikesmith", "age": 40, "email": "mikesmith@example.com"}');

SELECT id, key, value
FROM user_data, jsonb_each(user_data.profile);

This query returns the following results:

| id  | key      | value                   |
|-----|----------|-------------------------|
| 123 | username | "johndoe"               |
| 123 | age      | 30                      |
| 123 | email    | "johndoe@example.com"   |
| 140 | username | "mikesmith"             |
| 140 | age      | 40                      |
| 140 | email    | "mikesmith@example.com" |

Additional considerations

Performance implications

When working with large JSONB objects, jsonb_each may lead to performance overhead, as it expands each key-value pair into a separate row.

Alternative functions

• jsonb_each_text - Similar functionality to jsonb_each but returns the value as a text type instead of JSONB.
• jsonb_object_keys - It returns only the set of keys in the JSONB object, without the values.
• json_each - It provides the same functionality as jsonb_each, but accepts JSON input instead of JSONB.

Resources

• PostgreSQL documentation: JSON functions