PostgreSQL jsonb_to_record() Function

The PostgreSQL jsonb_to_record() function expands the specified top-level JSONB object into a row with the corresponding type defined in the AS clause.

jsonb_to_record() Syntax

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

jsonb_to_record(from_json JSONB) -> RECORD

Parameters

from_json

Required. The JSONB object to convert.

Return value

The PostgreSQL jsonb_to_record() function returns a value of RECORD type , which is converted from the specified JSONB object. The JSONB object is converted to a value ​​of type RECORD according to the jsonb_populate_record() function.

jsonb_to_record() Examples

This example shows how to use the PostgreSQL jsonb_to_record() function to convert a JSONB object to a row with a complex type.

SELECT
  *
FROM
  jsonb_to_record(
    '{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"]}'
  ) AS x(name TEXT, age INT, hobbies TEXT[]);

 name | age |    hobbies
------+-----+---------------
 Tom  |  20 | {sports,cars}

Here, we define the type of row to return in the AS clause : AS x(name TEXT, age INT, hobbies TEXT[]).

You can also use more complex types by combining custom types.

First, let’s create a custom SQL type:

CREATE TYPE address as (country TEXT, city TEXT);

Then convert a complex JSON object into a row:

SELECT
  *
FROM
  jsonb_to_record(
    '{"name": "Tom", "age": 20, "hobbies": ["sports", "cars"], "address": {"country": "CN", "city": "BeiJing"}}'
  ) AS x(name TEXT, age INT, hobbies TEXT[], address address);

 name | age |    hobbies    |   address
------+-----+---------------+--------------
 Tom  |  20 | {sports,cars} | (CN,BeiJing)

Here, we defined the column address with the type address.