PostgreSQL json_to_recordset() Function

The PostgreSQL json_to_recordset() function expands the specified top-level JSON array (its elements are objects) into a set of rows with the type defined in the AS clause.

json_to_recordset() Syntax

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

json_to_recordset(from_json JSON) -> SETOF RECORD

Parameters

from_json

Required. The JSON array to be converted, the elements of which are JSON objects.

Return value

The PostgreSQL json_to_recordset() function returns a set of values ​​of the RECORD type converted from the specified JSON array. The JSON objects in the array are converted into RECORD values ​​of type according to the json_to_record() function.

json_to_recordset() Examples

This example shows how to use the PostgreSQL json_to_recordset() function to convert a JSON array to a set of a custom type.

SELECT
  *
FROM
  json_to_recordset(
    '[{"x": "A", "y": 1}, {"x": "B", "y": 2}]'
  ) AS x(x TEXT, y INT);

 x | y
---+---
 A | 1
 B | 2

Here, we define the type of row to return in the AS clause : AS x(x TEXT, y INT). It contains two columns x and y, which match the keys in the JSON object respectively.