# PostgreSQL json_to_recordset() Function

> In PostgreSQL, the user can convert the data stored in JSON objects (as key-value pairs) to a set of table rows. This operation can be performed by making use …

In PostgreSQL, the user can convert the data stored in JSON objects (as key-value pairs) to a set of table rows. This operation can be performed by making use of the **json_to_recordset()** function. The **json_to_recordset()** function takes a JSON array, which can contain one or more JSON objects. The **json_to_recordset()** function converts those JSON objects into the set of table records/rows. Let’s start learning the **json_to_recordset()** function with examples.

##  **PostgreSQL json_to_recordset() Function**

The PostgreSQL **json_to_recordset()** function takes the **JSON** array. The JSON array might possess one or more JSON objects. These JSON objects are converted into the set of data rows by the **json_to_recordset()** function. The basic syntax for the **json_to_recordset()** function is given below:
    
    
    json_to_recordset(json_arrayJSON) AS (col1 data_type, col2 data_type,..... Coln data_type)

In the above syntax:

● The **json_to_recordset()** function takes in the JSON array.

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

● After the **AS** clause, we have to specify the column definition list i.e. column names with their data types.

● The JSON objects present in the JSON array are converted into a set of rows, having **RECORD** data type.

We will understand the workings of the **json_to_recordset()** function using the example.

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

To understand the concept behind the **json_to_recordset()** function, the following query can be used.
    
    
    SELECT
      *
     FROM
      json_to_recordset(
      '[{"student_id": 1, "student_name": "Tom", "subjects": ["Chemistry", "Calculus"]},
     {"student_id": 2, "student_name": "Smith", "subjects": ["Data Structures", "Calculus"]}]')
      AS cols(student_id INT, student_name TEXT, subjects TEXT[])

In the above code:

● We have specified 2 JSON objects in the JSON array. Both these objects will be converted to the rows of the table by using the **json_to_recordset()** function.

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

● The **json_to_recordset()** function will return the table rows created from the corresponding JSON objects present in the JSON array. The name and type of the columns will be the same as specified after the **AS** keyword.

The output is given as

We can clearly notice that the **json_to_recordset()** function has returned 2 rows, as we specified 2 JSON objects in the JSON array.

 **NOTE:** Note that the **json_to_recordset()** function primarily takes a JSON array. Otherwise, specifying the JSON object causes an error. We can specify the JSON objects inside the JSON array.

Another important point is that the **json_to_recordset()** function can also return 1 row. This happens when we pass a single JSON object in the JSON array. But, to get the single row **json_to_record()** function is preferred. However, the **json_to_recordset()** function can do it as well.

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

In this example, we will detect 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_recordset(
      '[{"student_id": 1, "subjects": ["Chemistry", "Calculus"]},
       { "student_name": "Smith", "subjects": ["Data Structures", "Calculus"]}]')
      AS cols(student_id INT, student_name TEXT, subjects TEXT[])

In the above code, the “studen_name” in the first JSON object and the “student_id” field in the second JSON object are skipped, whereas, we have created a column for it. In this case, the NULL value will be shown by the table like this:

This is how the **json_to_recordset()** 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_recordset()** function.

###  **Example 3: Executing the json_to_recordset() Function Without AS Keyword**

The significance of the **AS** statement in the **json_to_recordset()** function is already stated. We basically provide the list of column definitions after the **AS** statement. Let’s notice the impact of skipping it.
    
    
    SELECT
      *
     FROM
      json_to_recordset(
      '[{"student_id": 1, "student_name": "Tom", "subjects": ["Chemistry", "Calculus"]},
      {"student_id": 2, "student_name": "Smith", "subjects": ["Data Structures", "Calculus"]}]');

The output of this query is given below:

Running the above query yielded an error, which says that we have not specified the column definition list. This clearly depicts that the **AS** statement and the column definition list are necessary for the **json_to_recordset()** function to execute.

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

The user can also use a custom data type to define any column. The user-defined/custom data type can be created using the **CREATE TYPE** statement.
    
    
    CREATE TYPE major   
     AS (major_name TEXT, current_semester TEXT)

This query will successfully create a “major” data type for the user now this can be used to define the data type of a column like this:
    
    
    SELECT
      *
     FROM
      json_to_recordset(
      '[{"student_id":   12,
       "student_name": "Will",
       "subjects": ["Organic Chemistry", "Inorganic Chemistry"],
       "major":{"major_name": "Chemical   Engineering", "current_semester": "3rd"}},
       
       {"student_id": 45,
       "student_name": "Oliver",
       "subjects": ["Data Structures", "Calculus"],
       "major":{"major_name": "Software   Engineering", "current_semester": "4th"}}]')
      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_recordset()** function works in PostgreSQL.

##  **PostgreSQL json_to_record() Vs json_to_recordset() Function**

The PostgreSQL **json_to_record()** function takes the **JSON object** and converts it into **a single data row**. Whereas, the PostgreSQL **json_to_recordset()** function takes the **JSON array** as input and returns the **set of rows** expanded from the JSON objects provided in the JSON array.

##  **Conclusion**

The Postgres **json_to_recordset()** function takes a top-level **JSON array** as input and converts it into a set of table rows. The JSON array can contain multiple JSON objects that are expanded into rows. The **AS** statement and the **json_to_recordset()** function, serve together for the execution of the function. After the **AS** statement, we specify the list of column names with their data type. The **json_to_recordset()** function returns the JSON object’s data according to those table columns.

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