PostgreSQL jsonb_to_recordset() Function

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

jsonb_to_recordset() Syntax

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

jsonb_to_recordset(from_json JSONB) -> SETOF RECORD

Parameters

from_json

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

Return value

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

jsonb_to_recordset() Examples

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

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

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

Here, we defined 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 JSONB object respectively.