PostgreSQL json_populate_recordset() Function

The data stored in JSON objects can be converted into multiple table rows by using the json_populate_recordset() function. The json_populate_recordset() function converts the JSON array containing multiple JSON objects into a set of table rows. The detailed working of the json_populate_recordset() function is provided in the following sections.

PostgreSQL json_populate_recordset() Function

The json_populate_recordset() function converts the provided JSON array into the set of table rows. The basic syntax of the json_populate_recordset() function is given below:

json_populate_recordset ( base_val ANYELEMENT, json_array JSON ) → set_of ANYELEMENT

In the above syntax:

● The json_populate_recordset() takes 2 parameters.

● The base value illustrates the type of value in which the JSON array values are converted.

● The JSON array is specified which needs to be converted.

The PostgreSQL json_populate_recordset() function returns the set of data rows that were transformed from the JSON array provided.

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

The above concepts might seem complicated at times but we’ll examine them using examples so that the working of the json_populate_recordset() function becomes effortless to comprehend. Let’s move forward with examples.

Example 1: Understanding json_populate_recordset() Function

To implement the json_populate_recordset() function in any example, we will follow the below-given steps.

Step 1: Create A User-Defined\Custom Data Type

We can create a custom data type using the CREATE TYPE keyword. The query for the creation of a custom data type “team” looks like this:

CREATE TYPE team 
 AS (member_id INT,member_name TEXT, Projects TEXT[])

This query will successfully create a custom data type named “team” in the Postgres.

Step 2: Implement the json_populate_recordset() Function

Now we’ll use the json_populate_recordset() function on this newly created custom data type. The query can be written as:

SELECT * FROM
  json_populate_recordset(
  NULL::team, 
  '[{"mem_id": 1,
   "mem_name": "Williams Tim",
   "projects": ["game app", "clothing website"]}]');

In this code:

● The base value is specified as NULL. This means that if any JSON object field(provided in the JSON array i.e. the second argument) is missing, the function will return a null value in place of it.

● In the second argument, we have specified the JSON object that we wish to convert. In this case, the function got only one JSON object in the JSON array, so only one row will be returned like this:

We can also get multiple rows from the data of JSON objects using the json_populate_recordset() function.

Example 2: Specifying Multiple JSON Objects in json_populate_recordset() Function

If we specify multiple JSON objects in the json_populate_recordset() function, we will get multiple rows. Let’s execute the following query:

SELECT * FROM
  json_populate_recordset(
  NULL::team, 
  '[{"mem_id": 1,
  "mem_name": "Williams Tim",
  "projects": ["game app", "clothing website"]},  
  {"mem_id": 2,
  "mem_name": "Tim Wills",
  "projects": ["ludo app", "food app"]}]');

In this case, we have specified two JSON objects. So the function will return two data rows like this:

Hence, from the above image, it is clear that if we provide multiple JSON objects in the JSON array, we will be able to get multiple rows.

Now we’ll see how the base value impacts the outcome of the json_populate_recordset() function.

Example 3: 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 “projects” field in the JSON object. We’ll see how will it be executed.

SELECT * FROM
  json_populate_recordset(
  NULL::team, 
'[{"mem_id": 1,
   "mem_name": "Williams Tim"},   
   {"mem_id": 2,
   "mem_name": "Tim Wills"}]');

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

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

We can write any other value, of our choice, as the base value. In such a case, if any field is absent from the JSON object, the value declared in the base value is returned in its place. Let’s comprehend this concept using the following query:

SELECT * FROM
   json_populate_recordset(
  (1, 'any_member_name', Array['dummy project', 'project abc']):: team, 
 '[{"mem_id": 1,
   "mem_name": "Williams Tim"},  
   {"mem_id": 2,
   "projects": ["ludo app", "food app"]}]');

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 “projects” field from the first specified JSON object is missing and in the second field the member’s name is missing. How will the query respond?

In such a case, the missing fields from the JSON object take their value from the defined base value. 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.

json_populate_recordset() Vs json_populate_record() Function in PostgreSQL

The main difference between the json_populate_recordset() and the json_populate_record() function is that the json_populate_record() function takes the JSON object as input and returns only one row. While the json_populate_recordset() function takes the JSON array containing one or multiple JSON objects and returns the set of rows from them.

Conclusion

The json_populate_recordset() function converts the JSON array into a set of data rows. The JSON array can contain multiple JSON objects, which can be converted into arrays by using the json_populate_recordset() function. This blog demonstrated the working of the json_populate_recordset() function with proper examples and also sketched the difference in working of the json_populate_recordset() and json_populate_record() function in PostgreSQL.

PostgreSQL json_populate_recordset() Function