PostgreSQL jsonb_strip_nulls() Function

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)Code language: SQL (Structured Query Language) (sql)

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]}'
  );Code language: SQL (Structured Query Language) (sql)

Output:

                         jsonb_strip_nulls
--------------------------------------------------------------------
 {"scores": [null, 4, 5], "last_name": "Doe", "first_name": "John"}
(1 row)Code language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

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 *;Code language: SQL (Structured Query Language) (sql)

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)Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

                                                     cleaned_attributes
-----------------------------------------------------------------------------------------------------------------------------
 {"id": 1, "name": "Laptop", "specs": {"cpu": "Intel i7", "gpu": "Nvidia GTX 1650", "extras": {"fingerprint_reader": true}}}
(1 row)Code language: SQL (Structured Query Language) (sql)

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.