PostgreSQL jsonb_populate_record() Function

Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_populate_record() function to populate the fields of a record type from a JSON object.

Introduction to the PostgreSQL jsonb_populate_record() function

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

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

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

jsonb_populate_record ( 
   target anyelement, 
   json_object jsonb 
) → anyelementCode language: SQL (Structured Query Language) (sql)

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.

The jsonb_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 the jsonb_populate_record() function.

1) Basic jsonb_populate_record() function example

First, create a new type called person:

CREATE TYPE person AS (
  id INT, 
  name VARCHAR, 
  age INT
);Code language: SQL (Structured Query Language) (sql)

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

SELECT
  jsonb_populate_record(
    null :: person, 
   '{"id": 1, "name": "John", "age": 22}' :: jsonb
  );Code language: SQL (Structured Query Language) (sql)

Output:

 jsonb_populate_record
-----------------------
 (1,John,22)
(1 row)Code language: SQL (Structured Query Language) (sql)

2) Using the jsonb_populate_record() function with table data

First, create a new table called employees:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT NOT NULL,
    salary NUMERIC NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert some rows into the employees table:

INSERT INTO employees (name, age, salary) 
VALUES 
  ('John Doe', 25, 70000), 
  ('Jane Smith', 22, 80000);Code language: SQL (Structured Query Language) (sql)

Third, use jsonb_populate_record() to query the data from the employees table in a structured format:

SELECT 
  jsonb_populate_record(
    null :: employees, 
    jsonb_build_object(
      'id', id, 'name', name, 'age', age, 'salary', 
      salary
    )
  ) AS employees
FROM 
  employees;Code language: SQL (Structured Query Language) (sql)

Output:

         employees
---------------------------
 (1,"John Doe",25,70000)
 (2,"Jane Smith",22,80000)
(2 rows)Code language: SQL (Structured Query Language) (sql)

Summary

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