# How to convert JSONB to a record using jsonb_to_record in PostgreSQL

> The Postgres jsonb_to_record() function takes a top-level JSONB object, converts it into a table record/row, and returns it.

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.

---
[View this page online](https://www.commandprompt.com/education/how-to-convert-jsonb-to-a-record-using-jsonb_to_record-in-postgresql/)