PostgreSQL json_populate_record() Function

The JSON objects store values in the form of keys and values in the same manner as the tables do. In this regard, they can be transformed into the table data/ table row. This is the function of the json_populate_record() function in PostgreSQL. The json_populate_record() function is a system function, that takes the JSON object and returns the same JSON object expanded into a row. The following blog comprises a detailed demonstration of the basic concepts of the json_populate_record() function along with practical implementation.

PostgreSQL json_populate_record() function

The json_populate_record() function takes in JSON objects and returns the components of those JSON objects, expanded in a table row. The basic structure of the json_populate_record() function is given as:

json_populate_record(base_val ANYELEMENT, json_obj JSON) -> ANYELEMENT

In the above syntax:

● The json_populate_record() function takes 2 parameters.

● The base value can be a value of any data type that illustrates the type of value, in which the JSON object is to be converted.

● The json_obj parameter illustrates the JSON object that we want to convert.

The json_populate_record() function gives the JSON object converted into a customized SQL type value that is specified in the first parameter.

The value of the base is usually declared as NULL. This means that any output column that does not match the JSON object field will return a null value.

The above statements might seem complex but let’s discuss them using examples so that the working of json_populate_record() function becomes easy to understand. Let’s move forward with examples.

Example 1: Understanding json_populate_record() Function

To better understand how the json_populate_record() function is implemented follow the steps.

Step 1: Create A Custom Type

We will first have to create a custom data type to do the further implementation. Let’s create the custom data type with the name “registered_students”.

CREATE TYPE registered_students 
AS( stud_id INT,stud_name TEXT, subjects TEXT[])

A user-defined data type is successfully created in the Postgres.

Step 2: Use the json_populate_record() Function

We can now apply the json_populate_record() function on this type. We can write the following query for this case.

SELECT * FROM
  json_populate_record(
  null::registered_students,
  '{"stud_id": 1,
   "stud_name": "Kate Peter",
   "subjects": ["Physics", "Organic Chemistry"]}');

In this code:

● The base value is considered as NULL. This means that if any JSON object field(provided as a second argument) is missing, the function will return a null value in place of it we will see this case as well.

● In the second argument, we have specified the JSON object that we wish to convert.

The output of this query looks like this:

We can see that the json_populate_record() function has converted the specified JSON object into the table row.

This is how the json_populate_record() function basically works.

Example 2: Base Value in json_populate_record() Function

The base value is usually declared as NULL. This is because if some field of the JSON object is missing, the function will return NULL in its place. Let’s not specify the “subjects” field in the JSON object. We’ll see how it will be executed.

SELECT * FROM
  json_populate_record(
  null::registered_students,
  '{"stud_id": 1,
  "stud_name": "Kate Peter"}');

In this query, you can notice that we haven’t specified the “subjects” field. Upon execution, the query returns the following output.

We can clearly observe that the “subjects” column has returned a NULL value. This is the significance of specifying NULL as the base value.

Question - Can we specify some other value other than the NULL as the base value? If yes, how will it behave? Let’s see

The answer to this question is YES! We can appoint any other value as the base value. In such a case, if any field is missing from the JSON object, the value declared in the base value is returned in its place. Let’s implement this concept using the following query:

SELECT * FROM
  json_populate_record(
  (1, 'any_stud_name', Array['Physics', 'Organic Chemistry'])::registered_students,
   
  '{"stud_id":   1,
   "stud_name": "Kate Peter"}');

Here, we have specified some base value fields. These fields must be in the same order and the same data type as declared in the JSON object and the custom data type. We can also notice that the “subjects” field from the specified JSON object is missing. How will the query respond?

In such a case, the missing field from the JSON object takes its value from the base value defined. The same was the scenario in the case of NULL as well. The output of this query is given as:

It is very clear from the above output that the base value is declared so that it can be returned and utilized in case some field in the JSON object is missing as compared to the custom data type defined earlier. Consider a simple example of invalid casting below.

Example 3: In-Valid Casting in json_populate_record() Function

We need to properly cast the JSON object so that they can be cast into the expected column data type. For example, if we incorrectly declare a “stud_id” as a string, while we have declared it an integer before. The function will throw an error.

SELECT * FROM
  json_populate_record(
  null::registered_students,
  '{"stud_id":   "one",
   "stud_name": "Kate Peter",
   "subjects": ["Physics", "Organic   Chemistry"]}');

The query returns:

This illustrates that the proper casting is important for the json_populate_record() function to work.

This is how the json_populate_record() function works in PostgreSQL.

How to Convert a JSON Object to Multiple Rows?

To add multiple rows, we can use the Postgres json_populate_recordset() function. We can not provide multiple JSON objects to this function, it results in “ERROR: cannot call populate_composite on an array”.

Conclusion

The json_populate_record() function in Postgres converts the JSON object into a data row. This function basically populates the table record from the JSON object. The JSON object is provided to the json_populate_record() function along with the base value, it will convert the JSON objects into the table row in accordance with the base values. The above sections demonstrated the json_populate_record() function along with different use cases and examples.

PostgreSQL json_populate_record() Function