What is json_remove() Function in SQLite?

SQLite is a widely adopted and versatile database engine that allows storing JSON data in different formats, including BLOBs and TEXT. To enhance the functionality of working with JSON, SQLite provides the JSON1 extension, offering a range of handy functions to handle JSON data efficiently.

The json_remove() method is one of the important features provided by the JSON1 extension. In this article, we will explore the json_remove() function in SQLite and its usage in manipulating JSON data.

What is json_remove() Function?

The json_remove() function, as the name suggests, is a built-in SQLite function that removes a value from a JSON string. It returns the modified JSON string as its result.

The syntax for using json_remove() is straightforward.

json_remove(json_doc, path)

The function takes two arguments, the first json_doc is the JSON object or array, and the second argument path is the path to the element that needs to be removed. The syntax used to provide the route, which uses the dot notation, is the same as that used to access elements or properties.

One important thing to note is that json_remove() does not modify the original JSON object or array. Instead, a new JSON object or array is returned that has the requested components deleted. To update the original JSON object or array, the result of the json_remove() function must be saved to a new variable and then used to replace the original JSON data.

How to Use json_remove() Function in SQLite?

You can use the json_remove() function in several cases, some of which are given below:

Basic Usage
Remove Elements From JSON Array
With other JSON Manipulation Functions
With other SQL Statements

Follow the below-given code to create a table in SQLite and insert JSON data into it. This is required for those who have not created a table yet consisting of JSON data because we will need it while performing the above operations.

CREATE TABLE my_table (

id INTEGER PRIMARY KEY,

json_data TEXT);

INSERT INTO my_table (json_data)

VALUES ('{"name": "John", "age": 30, "city": "New York"}');

INSERT INTO my_table (json_data)

VALUES ('{"name": "Jane", "age": 42, "city": "London"}'),

('{"name": "Mark", "age": 28, "city": "Paris"}'),

('{"name": "Alice", "age": 55, "city": "Berlin"}');

After successfully creating a table consisting of JSON data, you are good to perform the below-given operations on it.

1: Basic Usage of SQLite json_remove() Function

Here is an example of using json_remove() to remove an element from a JSON object:

SELECT json_remove('{"name": "John", "age": 30, "city": "New York"}', '$.age');

In this example, the json_remove() function is being called to remove the age element from the JSON object. The dollar sign ($) is used to indicate the root of the JSON object, and the dot notation is used to access the age element. The JSON object without the age element would be returned in response to this query.

Output

2: Remove Elements From JSON Array Using SQLite json_remove()

The json_remove() can also be used to remove elements from a JSON array. To do this, the path must include the index of the element that has to be deleted. Here is an example:

SELECT json_remove('[1,2,3,4,5]', '$[2]');

In this example, the json_remove() function is being used to remove the element at index 2 from the JSON array. The result of this query would be the JSON array without the element at index 2, which would be [1,2,4,5].

Output

3: SQLite json_remove() Function With Other JSON Manipulation Functions

Another way to use json_remove() is to chain it with other JSON manipulation functions to achieve complex queries. For example, json_array() can be used to create a JSON array, followed by json_insert() to insert an element into the array, and then json_remove() to remove an element from the array. Here is an example:

SELECT json_remove(json_insert(json_array(), '$[0]', 'John'), '$[0]');

In this example, the query first creates an empty JSON array using json_array(). Then, it inserts the element John at index 0 using json_insert(). Finally, it removes the element at index 0 using json_remove(). The resulting JSON array would be empty.

Output

4: SQLite json_remove() Function With Other SQL Statements

The json_remove() can also be used in conjunction with other SQL statements to filter and manipulate data stored in a database. For example, it can be used to remove certain elements from a JSON object or array that do not meet specific criteria. Here is an example of using json_remove() with the WHERE clause:

UPDATE my_table

SET json_data = json_remove(json_data, '$.city')

WHERE json_extract(json_data, '$.age') > 40;

SELECT json_data

FROM my_table;

The above code removes the city field from the JSON data where the age field is greater than 40. After the update, the query selects the updated json_data from the table.

Output

Conclusion

The json_remove() function in SQLite offers a powerful way to remove elements from JSON documents stored in an SQLite database. Whether it’s removing a single element or using it with SQL Statements, this function allows developers to easily manipulate JSON data. By combining json_remove() with other JSON functions, developers can perform more advanced operations, such as updating or modifying specific elements within the JSON document.

What is json_remove() Function in SQLite?