PostgreSQL jsonb_populate_recordset() Function

The PostgreSQL jsonb_populate_recordset() function converts the specified top-level JSONB array to a set of specified SQL-typed values.

jsonb_populate_recordset() Syntax

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

jsonb_populate_recordset(base ANYELEMENT, from_json JSONB) -> SETOF ANYELEMENT

Parameters

base

Required. A value of any data type. It indicates the type of value the JSONB object is to be converted to.

from_json

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

Return value

The PostgreSQL jsonb_populate_recordset() function returns a set of custom SQL-typed values ​​converted from the specified JSONB array. Each JSONB object in the JSONB array is converted into a value of a SQL type according to the jsonb_populate_record() function.

We can use to CREATE TYPE create a custom type that defines the columns to output and the type of each column.

jsonb_populate_recordset() Examples

This example shows how to use the PostgreSQL jsonb_populate_recordset() function to convert a JSONB array to a value of a custom SQL type.

First, let’s create a custom SQL type:

CREATE TYPE my_type as (x TEXT, y INT, z TEXT);

Here, we create a custom type my_type with 3 columns x, y, and z.

Then, let’s convert the JSONB array to a my_type value:

SELECT
  *
FROM
  jsonb_populate_recordset(
    null::my_type,
    '[{"x": "A", "y": 1}, {"x": "B", "y": 2}]'
  );

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

Here, since the column z has no matching field in the JSONB object, the value in the column z is NULL. If you want to specidy a value for column z, you can use a non-null base parameter:

SELECT
  *
FROM
  jsonb_populate_recordset(
    ('x', 0, 'z')::my_type,
    '[{"x": "A", "y": 1}, {"x": "B", "y": 2}]'
  );

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

Here, since the parameter base is not null and the column z has no matching field in the JSONB object, the value of the column z in base is populated into the output z column .