Introduction to the PostgreSQL jsonb_populate_record() function

Thejsonb_populate_record() function expands the top-level JSON object of type JSONB to a row of a specified composite type.

In other words, thejsonb_populate_record() function converts a JSON object into a row of a specified composite type.

Here’s the basic syntax of thejsonb_populate_record() function:

jsonb_populate_record (   target anyelement,   json_object jsonb) → anyelement

In this syntax:

• target is a composite type to which you want to expand the JSONB value.
• json_object is a JSON object of the JSONB type that you want to expand.

Thejsonb_populate_record() function returns a record of the specified type with its fields populated using the key-value pairs from the JSON object.

PostgreSQL jsonb_populate_record() function examples

Let’s explore some examples of using thejsonb_populate_record() function.

1) Basic jsonb_populate_record() function example

First,create a new type calledperson:

CREATE TYPE person AS (  idINT,  name VARCHAR,  ageINT);

Second, use thejsonb_populate_record() function to expand the JSON object to a row of theperson type:

SELECT  jsonb_populate_record(    null :: person,   '{"id": 1, "name": "John", "age": 22}' :: jsonb  );

Output:

jsonb_populate_record----------------------- (1,John,22)(1 row)

2) Using the jsonb_populate_record() function with table data

First,create a new table calledemployees:

CREATE TABLE employees (    idSERIAL PRIMARY KEY,    name VARCHAR(100)NOT NULL,    ageINT NOT NULL,    salaryNUMERIC NOT NULL);

Second,insert some rows into theemployees table:

INSERT INTO employees (name, age, salary)VALUES  ('John Doe',25,70000),  ('Jane Smith',22,80000);

Third, usejsonb_populate_record() to query the data from theemployees table in a structured format:

SELECT  jsonb_populate_record(    null :: employees,    jsonb_build_object(      'id', id,'name',name,'age', age,'salary',      salary    )  )AS employeesFROM  employees;

Output:

employees--------------------------- (1,"John Doe",25,70000) (2,"Jane Smith",22,80000)(2 rows)

Summary

• Use thejsonb_populate_record() function to populate the fields of a record type or a custom composite type from a JSON object.

Last updated onFebruary 26, 2024

On this page

• Introduction to the PostgreSQL jsonb_populate_record() function
• PostgreSQL jsonb_populate_record() function examples
• Summary