PostgreSQL json_to_record() Function

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.

PostgreSQL json_to_record() Function