How to convert JSONB to a record using jsonb_to_record in PostgreSQL

We can store the JSONB data in a Postgres table. This makes the data more readable, comprehendible, and easy to understand to generate analytics. Many functions are used to perform this particular task such as jsonb_populate_record(), jsonb_populate_recordset(), jsonb_to_record(), and jsonb_to_recordset() functions. All of these functions work in their particular way and have their limitations and restrictions. This blog will be based on the jsonb_to_record() function. Let’s see how the jsonb_to_record() function converts the JSONB object into table records in PostgreSQL.

How to Convert JSONB to a Record Using jsonb_to_record in PostgreSQL

The PostgreSQL jsonb_to_record() function takes a top-level JSONB object as input and gives a single row expanded from that JSONB object, having the corresponding data type defined in the AS clause. The basic/fundamental structure of jsonb_to_record() function looks like this:

jsonb_to_record(jsonb_obj JSONB) AS (col1 data_type, col2 data_type,..... Coln data_type)

In the above syntax:

● The jsonb_to_record() function takes the top-level JSONB object.

● The jsonb_to_record() function gets followed by an AS clause.

● After the AS clause, we need to specify the list of columns with their data types i.e. the list of column definitions.

● The JSONB object is converted into a single row, having RECORD data type.

This function is almost the same as the json_to_record() function used to convert JSON objects into a record. To illustrate the working of the jsonb_to_record() function, we’ll consider some examples given below.

Example 1: Understanding the jsonb_to_record() Function

The following query demonstrates the working of the jsonb_to_record() function.

SELECT
  *
 FROM
  jsonb_to_record(
  '{"customer_id": 14,
   "customer_name": "Williams J",
   "items_buy": ["Cookies", "Jam"]}' ) 
 AS cols(customer_id INT, customer_name TEXT, items_buy TEXT[]);

In the above query:

● We have specified a top-level JSONB object i.e '{"customer_id": 14, "customer_name": "Williams J", "items_buy": ["Cookies", "Jam"]}', in the jsonb_to_record() function.

● After the AS keyword, we have written the column names with their individual data types.

● The jsonb_to_record() function will give a single table row created from the provided JSONB object. The name and type of columns will be the same as specified after the AS keyword.

The output of this query is given below:

If we observe the above output screenshot, we will be able to understand that the JSONB object is transformed into a table record/row. The name and data type of columns are the same as we declared after the AS keyword.

Example 2: More About the jsonb_to_record() Function

In this example, we will notice the impact, when a field in the JSONB object is missing but we have created a column for it. Consider the following query for this case.

SELECT
  *
 FROM
  jsonb_to_record(
  '{"customer_id": 14,
  "items_buy": ["Cookies", "Jam"]}' ) 
 AS cols(customer_id INT, customer_name TEXT, items_buy TEXT[]);

The field named “customer_name” is missing from the JSONB object, while the column has been created for it. For such cases, the column is created with the name as specified by the value for that field/key will be NULL like this:

This is how the jsonb_to_record() function responds to such a case. Now let’s see the impact if we do not provide the AS keyword on the working of the jsonb_to_record() function.

Example 3: Executing the jsonb_to_record() Function Without the AS Keyword

As we have stated the significance and importance of the AS keyword earlier i.e. after it, we specify the name and type of the columns, we will see how the function reacts to the absence of AS statement. Consider the following query.

SELECT
  *
 FROM
  jsonb_to_record(
  '{"customer_id":   14,
   "customer_name": "Williams J",
   "items_buy": ["Cookies", "Jam"]}' );

We have removed the AS statement from the query. Running the query will give the following output:

Executing the above query has thrown an error, which says that we have not specified the column definition list. This clearly illustrates that the AS statement and the column definition list are important for the jsonb_to_record() function to execute and create a record from the provided JSONB object.

Example 4: Using User-Defined Data Type With the jsonb_to_record() Function

In the AS statement of the jsonb_to_record() function, we can also define a column with a user-defined data type. We will first create a custom data type using the CREATE TYPE statement for the same above-considered example. Let’s create a custom data type named “quantity” showing the quantity count for the items.

CREATE TYPE quantity 
 AS (item1_qty INT, item2_qty INT)

We have created a custom data type i.e. “quantity”. This data type contains two fields for “item1_qty” and “item2_qty” both of integer type. Execution of the above query will successfully create a new user-defined data type.

We will now use it in the column definition in the jsonb_to_record() function. The query can be written as

SELECT
  *
 FROM
  jsonb_to_record(
  '{"customer_id": 14,
   "customer_name": "Williams J",
   "items_buy": ["Cookies", "Jam"],
   "quantity":{"item1_qty": 5, "item2_qty": 1}}' ) 
 AS   cols(customer_id INT, customer_name TEXT, items_buy TEXT[],quantity quantity);

In the above query, we have used the created user-defined data type “quantity” for the column definition. The query will return the following output.

We can see that another column is added. The new column “quantity” has the data type “quantity” as defined in the custom data type creation.

This is how the jsonb_to_record() function works in PostgreSQL.

Additional Information

To get multiple rows from the JSONB objects, we can use the jsonb_to_recordset() function. This function almost works the same as the jsonb_to_record() function the additional thing is that we can get multiple rows using the jsonb_to_recordset() function methods. For that, we will provide the jsonb_to_recordset() function, with a JSONB array containing multiple JSONB objects so that they can be converted into a set of rows.

Conclusion

The Postgres jsonb_to_record() function takes a top-level JSONB object, converts it into a table record/row, and returns it. The AS statement is used with the jsonb_to_record() function and is necessary for the execution of the function. After the AS statement, we specify the list of column names with their data type i.e. column definition list. The jsonb_to_record() function returns the JSONB object’s data according to those table columns.

How to convert JSONB to a record using jsonb_to_record in PostgreSQL