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 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.

PostgreSQL json_to_recordset() Function