PostgreSQL jsonb_populate_record() Function

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

jsonb_populate_record() Syntax

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

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

Return value

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

The fields of the JSONB 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 JSONB object that do not match the column names in the custom type will be ignored.

The jsonb_populate_record() function converts JSONB values ​​to SQL type values ​​according to the following rules:

  1. JSONB null will be converted to SQL null.
  2. If the output column is of type JSONB or JSONB, the JSONB value will be copied exactly.
  3. If the output column is a composite type, and the JSONB 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 JSONB value is a JSONB array, then convert the elements of the JSONB array to the elements of the output array by applying these rules recursively.
  5. Otherwise, if the JSONB 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 JSONB 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 JSONB 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.

jsonb_populate_record() Examples

This example shows how to use the PostgreSQL jsonb_populate_record() function to convert a JSONB 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 JSONB object to the type person we just created using the following statement:

SELECT
  *
FROM
  jsonb_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
  jsonb_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 JSONB object, the columns address in the output is populated with the values ​​in the parameter base.