SQLite json_remove() Function

Summary: in this tutorial, you will learn how to use the SQLite json_remove() function to remove one or more elements in JSON data based on specified paths.

Introduction to the SQLite json_remove() function

In SQLite, the json_remove() function replaces values in JSON data at specified paths.

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

json_remove(json_data, path1, path2, ...)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• json_data is the JSON data from which you want to remove values.
• path1, path2, … are the paths that specify elements you want to delete.

The json_remove() function returns the JSON data with the values at the specified path removed. If a path does not exist, the json_remove() function silently ignores it.

SQLite json_remove() function example

First, create a table called products:

CREATE TABLE products(
   id INTEGER PRIMARY KEY,
   info TEXT NOT NULL
);Code language: SQL (Structured Query Language) (sql)

Second, insert JSON data that contains the product details into the info column of the products table:

INSERT INTO products(info)
VALUES('{
  "name": "T-Shirt",
  "color": "Black",
  "size": [
    "S",
    "M",
    "L",
    "XL"
  ],
  "price": 19.99,
  "discount": {
    "percentage": 10,
    "expiry_date": "2024-05-31"
  }, 
  "material": "100% Cotton",
  "care_instructions": [
    "Machine wash cold",
    "Tumble dry low",
    "Do not bleach",
    "Iron low heat"
  ]
}');Code language: SQL (Structured Query Language) (sql)

Third, delete the material property of the JSON data using the json_remove() function:

UPDATE products
SET
  info = json_remove (info, '$.material')
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Verify the delete:

SELECT
  info ->> 'material' material
FROM
  products
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

material
--------
nullCode language: SQL (Structured Query Language) (sql)

The output indicates that the statement successfully deleted the material property.

Fourth, delete the color and price properties of the JSON data using the json_remove() function:

UPDATE products
SET
  info = json_remove (info, '$.color', '$.price')
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Verify the delete:

SELECT
  info ->> 'color' color,
  info ->> 'price' price
FROM
  products
WHERE
  id = 1;Code language: SQL (Structured Query Language) (sql)

Output:

color  price
-----  -----
null   nullCode language: SQL (Structured Query Language) (sql)

Summary

• Use the json_remove() function to delete one or more elements in JSON data based on specified paths.