Skip to main content
Documents
Basic SyntaxMath FunctionsDate FunctionsJSON FunctionsDatabasesTables & Schema ManagementString FunctionsTriggersIndexes

PostgreSQL jsonb_each()

PostgreSQL's JSON functions, such as `jsonb_each()`, allow for efficient manipulation and retrieval of data stored in JSONB format. The `jsonb_each()` function specifically returns a set of key-value pairs from a JSONB object. Using `jsonb_each()` can simplify processing JSONB data, offering advantages like easier iteration over JSONB objects and compatibility with PostgreSQL's robust indexing capabilities.

Usage

The `jsonb_each()` function is utilized to decompose a JSONB object into individual key-value pairs, making it easier to process and query JSONB data. It is especially useful when you need to iterate over JSONB data stored in a PostgreSQL table. The function returns a set of records, with `key` as text and `value` as JSONB.

SELECT jsonb_each(jsonb_column)
FROM table_name;

In this syntax, `jsonb_each(jsonb_column)` extracts each key-value pair from the JSONB column specified.

Examples

1. Basic Extraction

SELECT jsonb_each('{"name": "John", "age": 30}');

This example breaks down a simple JSONB object into individual key-value pairs, returning a set with "name" as "John" and "age" as 30. The function is applied directly to a JSONB literal, useful for demonstration purposes.

2. Extracting from a Table

SELECT jsonb_each(data)
FROM users_data;

Here, the function extracts key-value pairs from the `data` column of the `users_data` table, which stores JSONB objects for each row. The `data` column is expected to contain well-formed JSONB objects.

3. Using jsonb_each() with WHERE Clause

SELECT key, value
FROM jsonb_each('{"name": "John", "age": 30, "city": "New York"}') AS kv
WHERE kv.key = 'city';

This example uses `jsonb_each()` in conjunction with a `WHERE` clause to filter the results, returning only the key-value pair where the key is "city". The `AS kv` part assigns an alias to the result set for easier reference.

Tips and Best Practices

  • Use with JSONB data types. Ensure your JSON data is stored in the `jsonb` format to leverage indexing and performance benefits.
  • Combine with `LATERAL` joins. When working with tables, consider using `LATERAL` joins to process JSONB data efficiently.
  • Filter on keys. Use the `WHERE` clause to filter specific keys when you only need certain data points.
  • Index JSONB columns. To optimize performance, consider indexing frequently queried JSONB keys. Use `GIN` indexes for JSONB data to speed up key-value pair searches.
  • Performance considerations. Be aware of potential performance implications when dealing with large JSONB objects, as `jsonb_each()` might affect query performance.