PostgreSQL json_each() Function

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

json_each() Syntax

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

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

Parameters

any_object

Required. a JSON object.

Return value

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

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

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

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

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

SELECT
    *
FROM
    json_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 JSON object, respectively.

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

You can use customized column names like:

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

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