PostgreSQL json_populate_recordset() Function

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

json_populate_recordset() Syntax

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

json_populate_recordset(base ANYELEMENT, from_json JSON) -> SETOF ANYELEMENT

Parameters

base

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

from_json

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

Return value

The PostgreSQL json_populate_recordset() function returns a set of custom SQL-typed values ​​converted from the specified JSON array. Each JSON object in the JSON array is converted into a value of a SQL type according to the json_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.

json_populate_recordset() Examples

This example shows how to use the PostgreSQL json_populate_recordset() function to convert a JSON 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 JSON array to a my_type value:

SELECT
  *
FROM
  json_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 JSON 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
  json_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 JSON object, the value of the column z in base is populated into the output z column .