# PostgreSQL json_to_record() Function

> The Postgres json_to_record() function takes a top-level JSON object as input, converts it into a table record/row, and returns it.

The data stored in JSON objects can be stored in a Postgres table. This will make the data more readable, comprehendible, and easy to understand to generate analytics. We can use many functions to do this such as json_populate_record(), json_populate_recordset(), json_to_record(), and json_to_recordset() function. All these functions work in their own way and have their own limitations. The content of this blog is the **json_to_record()** function. Let’s see how the **json_to_record()** function works in PostgreSQL.

##  **PostgreSQL json_to_record() Function**

The **json_to_record()** function takes a top-level JSON object as input and returns a row expanded from that JSON object, having the respective data type, the same as defined in the **AS** clause. The basic syntax of **json_to_record()** function looks like this:
    
    
    json_to_record(json_obj JSON) AS (col1 data_type, col2 data_type,..... Coln data_type)

In the above syntax:

● The **json_to_record()** function takes the top-level JSON object.

● The **json_to_record()** function is followed by an **AS** clause.

● After the **AS** clause, we need to specify the column names with their corresponding data types.

● The JSON object gets converted into a row, having **RECORD** data type.

To illustrate the working of the **json_to_record()** function, we’ll consider some examples given below.

###  **Example 1: Understanding the json_to_record() Function**

The following query demonstrates the working of the **json_to_record()** function.
    
    
    SELECT
      *
     FROM
      json_to_record(
      '{"student_id": 1, "student_name": "Tom", "subjects": ["Chemistry", "Calculus"]}' ) 
     AS   cols(student_id INT, student_name TEXT, subjects TEXT[]);

In the above query:

● We have specified a top-level JSON object i.e _{ "student_id": 1, "student_name": "Tom", "subjects": ["Chemistry", "Calculus"]}_, in the **json_to_record()** function.

● After the **AS** keyword, we have written the names of the columns with their respective data types.

● The **json_to_record()** function will return a single table row created from the corresponding JSON object. The name and type of the table 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, we will be able to comprehend that the JSON object is converted into a table record/row. The name and type of table columns are the same as declared after **AS**.

###  **Example 2: Understanding the json_to_record() Function**

In this section, we will notice the impact when a field in the JSON object is missing but we have created a column for it. Consider the following code.
    
    
    SELECT
      *
     FROM
      json_to_record(
      '{ "student_name": "Tom", "subjects": ["Chemistry", "Calculus"]}')
      AS cols(student_id INT, student_name TEXT, subjects TEXT[]);

In the above code, the “student_id” field in the provided JSON object is missing, whereas, we have created a column for it. In this case, the NULL value will be displayed by the table like this:

This is how the **json_to_record()** function responds to such a case. Now let’s see what will happen if we do not provide the **AS** keyword to the **json_to_record()** function.

###  **Example 3: Implementing the json_to_record() Function Without AS Keyword**

As we have stated the significance of the **AS** keyword earlier i.e. after it, we specify the name and type of the columns, we will see how the function responds when the **AS** statement is not present. Consider the following query.
    
    
    SELECT
      *
     FROM
      json_to_record(
      '{ "student_name": "Tom", "subjects": ["Chemistry", "Calculus"]}');

We have eliminated the **AS** statement from the query. So the output of this query is:

Executing the above query returned 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 **json_to_record()** function to execute.

###  **Example 4: Using User-Defined Data Type With the json_to_record() Function**

In the **AS** statement of the **json_to_record()** function, the user can also define a column with a custom/user-defined data type. Let’s first create a custom data type using the **CREATE TYPE**.
    
    
    CREATE TYPE major   
     AS (major_name TEXT, current_semester TEXT)

We have created a custom data type i.e. “major”. This data type contains two fields for “major_name” and the “current_semester”. 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 **json_to_record()** function. The query can be written as
    
    
    SELECT
      *
     FROM
      json_to_record(
      '{"student_id":   1,
       "student_name": "Tom",
       "subjects": ["Chemistry", "Calculus"],
       "major":{"major_name": "Chemical Engineering", "current_semester": "3rd"}}')
      AS cols(student_id INT, student_name TEXT, subjects TEXT[], major major);

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

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

This is how the **json_to_record()** function works in PostgreSQL.

##  **How to Get Multiple Rows From JSON Objects in Postgres?**

To get multiple rows from the JSON objects, we can use the **json_to_recordset()** function. This function almost works the same as the **json_to_record()** function. The additional thing is we can get multiple rows using the **json_to_recordset()** function method. For that, we will provide the **json_to_recordset()** function with a JSON array containing multiple JSON objects so that they can be converted into a set of rows.

##  **Conclusion**

The Postgres **json_to_record()** function takes a top-level JSON object as input, converts it into a table record/row, and returns it. The **AS** statement complements the **json_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 respective data type. The **json_to_record()** function returns the JSON object’s data according to those table columns.

---
[View this page online](https://www.commandprompt.com/education/postgresql-json_to_record-function/)