Postgres jsonb_to_record() function

You can use the jsonb_to_record function to convert a top-level JSONB object into a row, with the type specified by the AS clause.

This function is useful when you need to parse JSONB data received from external sources, such as APIs or file uploads, and store it in a structured format. By using jsonb_to_record, you can easily extract values from JSONB and map them to the corresponding columns in your database table.

Function signature

jsonb_to_record(json JSONB) AS (column_name column_type [, ...])

The function's definition includes a column definition list, where you specify the name and data type of each column in the resulting record.

Example usage

Consider a scenario in which you have JSONB data representing employee information, and you want to ingest it for easier processing later. The JSONB data looks like this:

{
  "id": "123",
  "name": "John Doe",
  "department": "Engineering",
  "salary": "75000"
}

The table you want to insert data into is defined as follows:

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

Using jsonb_to_record, you can insert the input data into the employees table as shown:

INSERT INTO employees
SELECT *
FROM jsonb_to_record('{"id": "123", "name": "John Doe", "department": "Engineering", "salary": "75000"}') AS x(id INT, name TEXT, department TEXT, salary NUMERIC);

Note that the string representation of the JSON object didn't need to be explicitly cast to JSONB. Postgres automatically casts it to JSONB when the function is called.

To verify the data was inserted, you can run the following query:

SELECT * FROM employees;

This query returns the following result:

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

Advanced examples

This section provides advanced jsonb_to_record examples.

Handling partial data with jsonb_to_record

For datapoints where the JSONB objects have missing keys, jsonb_to_record can still cast them into records, producing NULL values for the unmatched columns. For example:

INSERT INTO employees
SELECT *
FROM jsonb_to_record('{
  "id": "124",
  "name": "Jane Smith"
}') AS x(id INT, name TEXT, department TEXT, salary NUMERIC)
RETURNING *;

This query returns the following result:

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

Handling nested data with jsonb_to_record

jsonb_to_record can also be used to handle nested JSONB input data (i.e., keys with values that are JSONB objects themselves). You need to first define a custom Postgres type. The newly created type can then be used in the column definition list along with the other columns.

In the following example, we handle the address field by creating an ADDRESS_TYPE type first.

CREATE TYPE ADDRESS_TYPE AS (
  street TEXT,
  city TEXT
);

SELECT *
FROM jsonb_to_record('{
  "id": "125",
  "name": "Emily Clark",
  "department": "Marketing",
  "salary": "68000",
  "address": {
    "street": "123 Elm St",
    "city": "Springfield"
  }
}') AS x(id INT, name TEXT, department TEXT, salary NUMERIC, address ADDRESS_TYPE);

This query returns the following result:

| id | name        | department | salary | address                     |
|----|-------------|------------|--------|-----------------------------|
| 1  | Emily Clark | Marketing  | 68000  | ("123 Elm St", Springfield) |

Alternative functions

• jsonb_populate_record: This function can also be used to create records using values from a JSONB object. The difference is that jsonb_populate_record requires the record type to be defined beforehand, while jsonb_to_record needs the type definition inline.

• jsonb_to_recordset: This function 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_to_recordset to convert each object into a new row.

• json_to_record: This function provides the same functionality as json_to_record, but accepts JSON input instead of JSONB. In cases where the input payload type isn't exactly specified, either of the two functions can be used.

  For example, take this json_to_record query:

  SELECT *
  FROM json_to_record('{"id": "123", "name": "John Doe", "department": "Engineering"}')
  AS x(id INT, name TEXT, department TEXT);

  It works just as well as this JSONB variant (below) since Postgres casts the literal JSON object to JSON or JSONB depending on the context.

  SELECT *
  FROM jsonb_to_record('{"id": "123", "name": "Sally", "department": "Engineering"}')
  AS x(id INT, name TEXT, department TEXT);

Resources

• PostgreSQL documentation: JSON functions