PostgreSQL json_populate_record() Function

The PostgreSQL json_populate_record() function converts the specified top-level JSON object to a specified SQL type value.

json_populate_record() Syntax

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

json_populate_record(base ANYELEMENT, from_json JSON) -> 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 object to convert.

Return value

The PostgreSQL json_populate_record() function returns a custom SQL type value, which is converted from the specified JSON object.

The fields of the JSON object that match the column names in the custom type, their values ​​will be inserted into the corresponding output columns. And those fields in the JSON object that do not match the column names in the custom type will be ignored.

The json_populate_record() function converts JSON values ​​to SQL type values ​​according to the following rules:

  1. JSON null will be converted to SQL null.
  2. If the output column is of type JSON or JSONB, the JSON value will be copied exactly.
  3. If the output column is a composite type, and the JSON value is an object, the fields of the object are converted to columns of the output row type by applying these rules recursively.
  4. If the output column is an array type and the JSON value is a JSON array, then convert the elements of the JSON array to the elements of the output array by applying these rules recursively.
  5. Otherwise, if the JSON value is a string, the contents of the string will be sent to the input conversion function corresponding to the column data type.
  6. Otherwise, the plain text representation of the JSON value is passed to the input conversion function for the column data type.

In typical use, the value of base is NULL, which means that any output column that doesn’t match a field in the JSON object will be filled with nulls. If the value of base is not NULL, then the value it contains will be used in the unmatched column.

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

json_populate_record() Examples

This example shows how to use the PostgreSQL json_populate_record() function to convert a JSON object to a value of a custom SQL type.

First, let’s create two SQL types:

CREATE TYPE address as (country TEXT, city TEXT);
CREATE TYPE person as (name TEXT, age INT, hobbies TEXT[], address address);

Here, we create two SQL types address and person, and address is used in person.

Then, let’s convert the JSON object to the type person we just created using the following statement:

SELECT
  *
FROM
  json_populate_record(
    null::person,
    '{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"], "address": {"country": "CN", "city": "BeiJing"}}'
  );

 name | age |    hobbies    |   address
------+-----+---------------+--------------
 Tom  |  20 | {sports,cars} | (CN,BeiJing)

We can use a non-NULL base parameter so that the unmatched columns in the output are filled with a value, such as:

SELECT
  *
FROM
  json_populate_record(
    ('x', 0, ARRAY['sports'], ('CN', 'BeiJing'))::person,
    '{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"]}'
  );

 name | age |    hobbies    |   address
------+-----+---------------+--------------
 Tom  |  20 | {sports,cars} | (CN,BeiJing)

Here, our base parameter is ('x', 0, ARRAY['sports'], ('CN', 'BeiJing'))::person, where the value of the address column is ('CN', 'BeiJing'). Although there are not address field in the specified JSON object, the columns address in the output is populated with the values ​​in the parameter base.