Introduction to the PostgreSQL jsonb_strip_nulls() function

Thejsonb_strip_nulls() function accepts a JSON document with theJSONB 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 thejsonb_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. Thejsonb_data must have the typeJSONB.

Thejsonb_strip_nulls returns a newjsonb_data whose object fields with null values are removed.

If thejsonb_data is null, the function returnsNULL.

PostgreSQL jsonb_strip_nulls() function example

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

1) Basic jsonb_strip_nulls() function example

The following example uses thejsonb_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 fieldmiddle_name has a null value therefore thejsonb_strip_nulls() function removes it. Thescores array also has null but it is a non-object field, therefore, thejsonb_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 calledproducts:

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

Second,insert data into theproducts 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 thejsonb_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_attributesFROM 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 theram field and the entireextras object, as well as its nested fields with null values, have been removed from the JSONB data.

Summary

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

Last updated onFebruary 24, 2024

On this page

• Introduction to the PostgreSQL jsonb_strip_nulls() function
• PostgreSQL jsonb_strip_nulls() function example
• Summary