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.