PostgreSQL jsonb_each_text() Function

The PostgreSQL jsonb_each_text() function expands a specified top-level member of a JSONB object into a collection of key-value pairs (both keys and values ​​are of type text).

jsonb_each_text() Syntax

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

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

Parameters

any_object

Required. A JSONB object.

Return value

The PostgreSQL jsonb_each_text() function returns a set of key-value pairs record (both keys and values ​​are of text type), 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_text() Examples

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

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

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

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

SELECT * FROM jsonb_each_text('{"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 the value column is TEXT. This is the difference from the json_each() function.

You can use customized column names like:

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

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