PostgreSQL jsonb_each() Function

The PostgreSQL jsonb_each() function expands a specified JSONB object into a set of key-value pairs (the keys are of type text and the values ​​are of type JSONB).

jsonb_each() Syntax

This is the syntax of the PostgreSQL jsonb_each() function:

jsonb_each(any_object JSONB) -> SETOF RECORD(key TEXT, value JSONB)

Parameters

any_object

Required. a JSONB object.

Return value

The PostgreSQL jsonb_each() function returns a set of key-value pairs (the keys are of type text and the values ​​are of type JSONB), it has two columns key and value, corresponding to the key and value of the top-level key-value pair in the JSONB object respectively.

jsonb_each() Examples

This example shows how to use the PostgreSQL jsonb_each() function to expand a JSONB object into a set of key-value pairs.

SELECT jsonb_each('{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"]}');

             jsonb_each
------------------------------------
 (age,20)
 (name,"""Tom""")
 (hobbies,"[""sports"", ""cars""]")

Since the jsonb_each() function return value is of type SETOF, you can use jsonb_each() as a temporary table in the SELECT * FROM statement:

SELECT
    *
FROM
    jsonb_each('{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"]}');

   key   |       value
---------+--------------------
 name    | "Tom"
 age     | 20
 hobbies | ["sports", "cars"]

This result set has 2 columns key and value, corresponding to the key and value of the key-value pair in the JSONB object, respectively.

Note that the type of value column is JSONB. This is the difference from the json_each_text() function.

You can use customized column names like:

SELECT
    *
FROM
    jsonb_each('{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"]}')
    AS x(k, v);

    k    |         v
---------+--------------------
 name    | "Tom"
 age     | 20
 hobbies | ["sports", "cars"]