Introduction to the PostgreSQL JSONB jsonb_to_record() function

Thejsonb_to_record() function allows you to convert aJSON object into a PostgreSQL record type.

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

jsonb_to_record(json_object)  as record_type (column1type, column2type,...)

In this syntax:

• First, specify a JSON object (json_object) of type JSONB that you want to convert into a record type.
• Second, define therecord_type with a list of columns that have the same names as the keys in the JSON object. The record may have fewer columns than the number of keys in the JSON object. If the names of the columns of therecord_type are not the same as the names of the keys in the JSON object, they will be ignored.

The functionjsonb_to_record() returns a record type representing the structure of a JSON object.

If thejson_object is null, thejsonb_to_record() function returns null.

PostgreSQL jsonb_to_record() function examples

Let’s take some examples of using thejsonb_to_record() function.

1) Basic PostgreSQL jsonb_to_record() function examples

The following example uses thejsonb_to_record() function to convert a JSON object into a record:

SELECT  *FROM  jsonb_to_record(    '{"id": 1, "name": "Alice", "age": 30}'  )AS person (idINT,name TEXT, ageINT);

Output:

id | name  | age----+-------+-----  1 | Alice |  30(1 row)

The following example converts a JSON object into a record type but with fewer keys:

SELECT  *FROM  jsonb_to_record(    '{"id": 1, "name": "Alice", "age": 30}'  )AS person (idINT,name TEXT);

Output:

id | name----+-------  1 | Alice(1 row)

2) Using jsonb_to_record() function with user-defined type

First, create a new custom type calledpet with two fieldstype andname:

CREATE TYPE pet AS (type VARCHAR,name VARCHAR);

Second, use the pet type with thejsonb_to_record() function:

SELECT  *FROM  jsonb_to_record(    '{"id": 1, "name": "Alice", "age": 30, "pets": [{"type":"cat", "name": "Ellie"}, {"type":"dog", "name": "Birdie"}]}'  )AS person (idINT,name TEXT, pets pet[]);

Output:

id | name  |              pets----+-------+--------------------------------  1 | Alice | {"(cat,Ellie)","(dog,Birdie)"}(1 row)

Summary

• Use thejsonb_to_record() function to convert a JSON object into a PostgreSQL record type.

Last updated onFebruary 24, 2024

On this page

• Introduction to the PostgreSQL JSONB jsonb_to_record() function
• PostgreSQL jsonb_to_record() function examples
• Summary