The JSON objects store data in key and value pairs. There is a great possibility that the data being recorded can have NULL values. The NULL values give incomplete information and are usually of no use to us. So we need to get rid of such data. The json_strip_nulls() function can be used to perform this operation. The json_strip_nulls() function removes the fields having NULL values from the provided JSON object.

How to Use the json_strip_nulls() Function in PostgreSQL?

The json_strip_nulls() Function stripes/removes the fields/keys that contain the NULL values. Any other parameter(other than JSON object) passed, remains unaffected by the function even if it contains NULL values. The basic structure of the PostgreSQL json_strip_nulls() Function is given as

json_strip_nulls(json_val JSON)

The syntax says that:

● We need to pass JSON value in the json_strip_nulls() function.

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

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

Below are the examples that illustrate how the json_strip_nulls() function works in PostgreSQL.

Example 1: Understanding the json_strip_nulls() Function

The following JSON object contains the radius for the circles. We will implement the json_strip_nulls() function to remove the NULL value from the JSON object. The query can be written as

SELECT json_strip_nulls('{"circle1_rad":   34,
  "circle2_rad": null,
  "circle3_rad": 22,
  "circle4_rad": 97}');

The query will give the following output:

We can observe that the “circle2_rad” had the NULL value, so after executing the query, it has been removed by the json_strip_nulls() function. This was the simple working of the json_strip_nulls() function.

Example 2: Understanding the json_strip_nulls() Function

Consider the below-given query to see how the function responds to it:

SELECT json_strip_nulls(
  '[45, null, 33, 
  {"circle1_rad": 34, "circle2_rad": null, "circle3_rad": 22}]');

In this query, we have provided an array to the json_strip_nulls() function. The array contains a JSON object as well i.e. {"circle1_rad": 34, "circle2_rad": null, "circle3_rad": 22}, containing the keys and values. Let’s see how the json_strip_nulls() function worked on it.

If we carefully observe the output, we will be able to discover that the JSON object field containing the NULL value(i.e. circle2_rad) has been removed while the NULL value in the array remains unaffected. This means that the json_strip_nulls() function only works for the JSON objects. This is how the json_strip_nulls() function basically works.

The json_strip_nulls() function can be used on the table data. We will learn to execute it on table data in the following example.

Example 3: Using the json_strip_nulls() Function With Table’s Data

Consider the database table for an online store that stores the customers and their shopping cart information. The table is named “online_store” which looks like this:

Now, we’ll apply the json_strip_nulls() function on the “online_store” table to remove all the JSON fields having the value NULL. The query can be written as

SELECT json_strip_nulls(products_in_cart) 
 FROM online_store;

The query will remove all the JSON fields having a NULL value from the “products_in_cart” column of the “online_store” table. The output of the query is:

You can notice that the “qty” field of the second row had a NULL value so it is removed from the particular JSON object. The “item” and ”qty” fields of the last row, both had NULL value, so they both are striped from the respective JSON object.

This is how the json_strip_nulls() function strips all the NULLs from the given JSON objects.

Conclusion

The PostgreSQL json_strip_nulls() function removes the fields in the JSON object that have NULL values. The JSON object is to be provided to the json_strip_nulls() function and it returns the fields with non-null values. Any other data can be provided to this function as well such as an array, etc., other than the JSON object, but the json_strip_nulls() function will not function on it. It will return the same object unaffected. In this detailed guide, we have learned about the json_strip_nulls() function with the help of suitable examples.