How to Use json_extract() Function in SQLite

The json_extract() function in SQLite is a built-in function that returns a value from a JSON object or an array. This function requires two parameters: the JSON string as the first and the JSON path to the requested data as the second. The data’s placement within the JSON object is specified by the JSON path.

Syntax of SQLite json_extract() Function

The syntax of the json_extract() function in SQLite is straightforward, which is given below:

Here, json refers to the JSON document or column containing the JSON data, and the path is the JSON path expression that defines the location of the desired data within the JSON object.

The path expression can contain various components such as object keys, array indices, and wildcard characters. It allows for flexible navigation within the JSON document to reach the desired data.

How to Use json_extract() Function in SQLite

In SQLite, the json_extract() can be used in the following ways:

• Extracting the Values of the Key
• Extracting a JSON Object
• Extracting an Array of Values

1: Extracting the Values of the Key

The basic functionality of the json_extract() function is to extract the value of the key name from the following JSON object. The following code extracts the value of the name key in SQLite:

The result of this query is the value of the name key, which is Sarah.

Output

The second argument of the json_extract() function can also specify a path to a particular element within a nested JSON object. When working with intricate JSON structures, this is helpful. For example, to extract the value of the city key from this nested JSON object, we can use the following SQL query:

The result of this query is New York, which is the value of the city key.

Output

2: Extracting a JSON Object

A JSON object can be extracted using the json_extract() function. For example:

This will return the JSON object person.

Output

3: Extracting an Array of Values

The SQLite json_extract() method may also be used to extract an array of data.

For example:

This will return the array of numbers.

Output

The json_extract() function in SQLite supports a variety of JSON path expressions. For example, it can extract all elements in an array, filter array elements that match specified criteria, and calculate the length of a JSON object or array.

Conclusion

The json_extract() function in SQLite provides a powerful tool for extracting specific values from JSON documents stored within an SQLite database. With its flexible path expressions, developers can navigate through JSON structures to access the desired data. This functionality opens up new possibilities for working with JSON data in SQLite, enabling efficient querying and manipulation.