# PostgreSQL json_populate_record() Function

> The json_populate_record() function in Postgres converts the JSON object into a data row. It populates the table record from the JSON object.

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.

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