Summary: in this tutorial, you will learn how to use the PostgreSQL jsonb_strip_nulls() function to recursively delete all object fields that have null values.

Introduction to the PostgreSQL jsonb_strip_nulls() function

The jsonb_strip_nulls() function accepts a JSON document with the JSONB type and recursively deletes all object fields that have null values. It does not delete non-object fields with null values.

Here’s the syntax of the jsonb_strip_nulls() function:

jsonb_strip_nulls(jsonb_data)

In this syntax, you specify a JSON document in which you want to delete all object fields with null values. The jsonb_data must have the type JSONB.

The jsonb_strip_nulls returns a new jsonb_data whose object fields with null values are removed.

If the jsonb_data is null, the function returns NULL.

PostgreSQL jsonb_strip_nulls() function example

Let’s take some examples of using the jsonb_strip_nulls() function.

1) Basic jsonb_strip_nulls() function example

The following example uses the jsonb_strip_nulls() function to remove object fields with null values:

SELECT  jsonb_strip_nulls(  '{"first_name": "John", "middle_name":null, "last_name": "Doe", "scores": [null, 4, 5]}'  );

Output:

jsonb_strip_nulls --------------------------------------------------------------------  {"scores": [null, 4, 5], "last_name": "Doe", "first_name": "John"} (1 row)

In this example, the object field middle_name has a null value therefore the jsonb_strip_nulls() function removes it. The scores array also has null but it is a non-object field, therefore, the jsonb_strip_nulls() function does not delete it.

2) Using the jsonb_strip_nulls() function to recursively delete object fields with null values

First, create a new table called products:

CREATE TABLE products (  id SERIAL PRIMARY KEY,  name VARCHAR(100) NOT NULL,  attributes JSONB );

Second, insert data into the products table:

INSERT INTO products (name, attributes) VALUES  (  'Smartwatch', '{  "id": 1,  "name": "Laptop",  "specs": {  "cpu": "Intel i7",  "ram": null,  "gpu": "Nvidia GTX 1650",  "extras": {  "bluetooth": null,  "fingerprint_reader": true,  "webcam": null  }  } }' ) RETURNING *;

Output:

id | name | attributes  ----+------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------  1 | Smartwatch | {"id": 1, "name": "Laptop", "specs": {"cpu": "Intel i7", "gpu": "Nvidia GTX 1650", "ram": null, "extras": {"webcam": null, "bluetooth": null, "fingerprint_reader": true}}} (1 row)

Third, use the jsonb_strip_nulls() function to remove all fields with null values recursively from the specs object and its nested object:

SELECT jsonb_strip_nulls(attributes) AS cleaned_attributes FROM products;

Output:

cleaned_attributes -----------------------------------------------------------------------------------------------------------------------------  {"id": 1, "name": "Laptop", "specs": {"cpu": "Intel i7", "gpu": "Nvidia GTX 1650", "extras": {"fingerprint_reader": true}}} (1 row)

The output indicates that the ram field and the entire extras object, as well as its nested fields with null values, have been removed from the JSONB data.

Summary

  • Use the jsonb_strip_nulls() function to recursively delete all object fields that have null values.