PostgreSQL json_each_text() Function

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

json_each_text() Syntax

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

json_each_text(any_object JSON) -> SETOF RECORD(key TEXT, value TEXT)

Parameters

any_object

Required. A JSON object.

Return value

The PostgreSQL json_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 JSON object respectively.

json_each_text() Examples

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

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

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

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

SELECT * FROM json_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 JSON 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
    json_each_text('{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"]}')
    AS x(k, v);

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