In Neon, you can provision Postgres databases in < 1s. Don't believe us? Check out this demo
Postgres guides/Functions/JSON functions

Postgres jsonb_populate_record() function

Casts a JSONB object to a record

The jsonb_populate_record function is used to populate a record type with values from a JSONB object. It is useful for parsing JSONB data received from external sources, particularly when merging it into an existing record.

Try it on Neon!

Neon is Serverless Postgres built for the cloud. Explore Postgres features and functions in our user-friendly SQL editor. Sign up for a free account to get started.

Sign Up

Function signature

jsonb_populate_record(base_record ANYELEMENT, json JSONB) -> ANYELEMENT

This function takes two arguments: a base record of a row type (which can even be a NULL record) and a JSONB object. It returns the record updated with the JSONB values.

Example usage

Consider a database table that tracks employee information. When you receive employee information as JSONB records, you can use jsonb_populate_record to ingest the data into the table.

Here we create the employees table with some sample data.

CREATE TABLE employees (
    id INT,
    name TEXT,
    department TEXT,
    salary NUMERIC
);

To illustrate, we start with a NULL record and cast the input JSONB payload to the employees record type.

INSERT INTO employees
SELECT *
FROM jsonb_populate_record(
    NULL::employees,
    '{"id": "123", "name": "John Doe", "department": "Engineering", "salary": "75000"}'
)
RETURNING *;

This query returns the following result:

| id | name     | department  | salary |
|----|----------|-------------|--------|
| 123| John Doe | Engineering | 75000  |

Advanced examples

Handling partial data with jsonb_populate_record

For data points where the JSONB objects have missing keys, jsonb_populate_record can still cast them into legible records.

Say we receive records for a bunch of employees who are known to be in Sales, but the department field is missing from the JSONB payload. We can use jsonb_populate_record with the default value specified for a field while the other fields are populated from the JSONB payload, as in this example:

INSERT INTO employees
SELECT *
FROM jsonb_populate_record(
    (1, 'ABC', 'Sales', 0)::employees,
    '{"id": "124", "name": "Jane Smith", "salary": "68000"}'
)
RETURNING *;

This query returns the following:

| id | name       | department | salary |
|----|------------|------------|--------|
| 124| Jane Smith | Sales      | 68000  |

Using jsonb_populate_record with custom types

The base record doesn't need to have the type of a table row and can be a custom Postgres type too. For example, here we first define a custom type address and use jsonb_populate_record to cast a JSONB object to it:

CREATE TYPE address AS (
    street TEXT,
    city TEXT,
    zip TEXT
);

SELECT *
FROM jsonb_populate_record(
    NULL::address,
    '{"street": "123 Main St", "city": "San Francisco", "zip": "94105"}'
);

This query returns the following result:

| street     | city          | zip   |
|------------|---------------|-------|
| 123 Main St| San Francisco | 94105 |

Additional considerations

Alternative options

  • jsonb_to_record - It can be used similarly, with a couple differences. jsonb_populate_record can be used with a base record of a pre-defined type, whereas jsonb_to_record needs the record type defined inline in the AS clause. Further, jsonb_populate_record can specify default values for missing fields through the base record, whereas jsonb_to_record must assign them NULL values.
  • jsonb_populate_recordset - It can be used similarly to parse JSONB, the difference being that it returns a set of records instead of a single record. For example, if you have an array of JSONB objects, you can use jsonb_populate_recordset to convert each object into a new row.
  • json_populate_record - It has the same functionality to jsonb_populate_record, but accepts JSON input instead of JSONB.

Resources

Last updated on

Edit this page
Was this page helpful?