# How do I remove null values with the jsonb_strip_nulls function in PostgreSQL?

> The PostgreSQL stores data and there are a lot of chances that the data being recorded might contain NULL values. The root cause of these NULL values may be so…

The PostgreSQL stores data and there are a lot of chances that the data being recorded might contain NULL values. The root cause of these **NULL** values may be some discrepancies in the system generating the data or maybe due to inefficient/incorrect data entry. The **JSONB** is a widely used data type in Postgres that stores an immense range of textual data in the form of key-value pairs. So the NULL values also appear in **JSONB** data.

We need to get rid of these **NULL** values in order to perform effective operations on the data and also to get useful insights from the data, which is not possible until we have the **NULL** value present in our data. To remove the **NULL** value field from the **JSONB** data, we use the **jsonb_strip_nulls()** function.

The content of this blog is based on the usage of the **jsonb_strip_nulls()** function using examples. So let’s get started.

##  **How to Remove Null Values With the jsonb_strip_nulls Function in PostgreSQL?**

The PostgreSQL **jsonb_strip_nulls()** Function removes the fields/keys that include the NULL values. Any other value/parameter(other than JSONB object) passed, remains unchanged by the function even if it contains NULL values. The basic format/syntax of the PostgreSQL **jsonb_strip_nulls()** Function is given as
    
    
    jsonb_strip_nulls(jsonb_val   JSONB)

The syntax says that:

● We need to provide the **JSONB** value in the **jsonb_strip_nulls()** function.

● The function will return the **JSONB** values, with the fields containing NULL values, removed.

● If the parameter is specified as **NULL** , the function will return **NULL**.

The **jsonb_strip_nulls()** function is the same as the **json_strip_nulls()** function used to remove the NULL field from the **JSON** data fields. Below are the examples that illustrate how the **jsonb_strip_nulls()** function works in PostgreSQL.

###  **Example 1: Understanding the jsonb_strip_nulls() Function**

In the below query, we have provided the JSONB object that contains the marks of different students and the NULL, in case, the student has not attempted the test. Now if we want to remove the field (the students who have not attempted the test), we will use the **jsonb_strip_nulls()** function on the data.
    
    
    SELECT jsonb_strip_nulls(
      '{"stud1_marks": 85,
      "stud2_marks": null,
      "stud3_marks": 92,
      "stud4_marks": 77,
      "stud5_marks": null}');

On execution, the above query will return the data of students who have attempted the test.

We can observe that the fields having **NULL** values are removed from the provided data. This is the function of the **jsonb_strip_nulls()** function. We can also notice that the returned data type of the **jsonb_strip_nulls()** function is **JSONB**.

This was a simple example, let’s consider another example.

###  **Example 2: More About the jsonb_strip_nulls() Function**

As stated above, the **jsonb_strip_nulls()** function only removes the NULL value fields that are present in the JSONB object. NULL values present on other parameters other than the JSONB objects remain unaffected by the **jsonb_strip_nulls()** function. Let’s see an example of this:
    
    
    SELECT jsonb_strip_nulls(
      '[84, null, 53, 
      {"stud1_marks": 85,
      "stud2_marks": null,
       "stud3_marks": 92,
       "stud4_marks": 77,
       "stud5_marks": null}]');

In this query, we have provided a JSONB array to the **jsonb_strip_nulls()** function. The **JSONB** array has elements, also containing a **NULL** value, and a JSONB object as well. The JSONB object contains fields that have NULL values. Let’s execute the query to see what it will return.

It is clear from the above output that the fields having **NULL** values are removed from the JSONB objects while the NULL other than the **JSONB** object remains unaffected.

###  **Example 3: Using the jsonb_strip_nulls() Function On Table’s Data**

We can also implement the **jsonb_strip_nulls()** function on the table’s data. The table’s name is “results” and it is given as

The “marks_record” column, which is the JSONB column containing the JSONB objects. We will now implement the **jsonb_strip_nulls()** function in this column for the “results” table. The query can be written as
    
    
    SELECT jsonb_strip_nulls(marks_record) 
    FROM results;

The **jsonb_strip_nulls()** function will remove all the NULL value fields from the JSONB objects present in the “marks_record” column of the above data.

The query returned the JSONB objects without the NULL value fields. This is how the **jsonb_strip_nulls()** function strips all the NULLs from the JSONB objects.

##  **Conclusion**

The PostgreSQL **jsonb_strip_nulls()** function strips the fields in the **JSONB object** that have NULL values. The JSONB object is to be provided to the **jsonb_strip_nulls()** function and it returns the fields with non-null values. The **jsonb_strip_nulls()** function will not affect any other data such as an array, etc., other than the JSONB object. It will return the same object unaffected. In the above post, we have learned about the **jsonb_strip_nulls()** function with the help of proper implementation.

---
[View this page online](https://www.commandprompt.com/education/how-do-i-remove-null-values-with-the-jsonb_strip_nulls-function-in-postgresql/)