# PostgreSQL json_populate_recordset() Function

> 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 converte…

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.

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