SQLite JSON_EXTRACT function

One of the useful features of SQLite is its ability to work with JSON data. SQLite provides a built-in function called json_extract() that allows you to extract values from JSON data stored in a column.

Syntax

The syntax of the function is as follows:

json_extract(json_string, path_expression)

The json_extract() function takes two arguments: the first argument is the JSON string, and the second argument is the JSON path expression that specifies the value to extract from the JSON string. The path expression can include one or more keys separated by dots to navigate through the JSON hierarchy.

Example

For example, suppose we have a table called users with a column named data that contains JSON data representing user information. We can use the json_extract() function to extract a user’s email address like this:

SELECT 
json_extract(data, '$.email') AS email 
FROM users 
WHERE id = 1;

In this example, the $ symbol is used to specify the root element of the JSON object, and the .email expression is used to extract the email address property from the object.

The json_extract() function also supports a range of other JSON path expressions, including array indexing, object property access, and wildcard expressions. For example:

SELECT 
json_extract(data, '$.friends[0].name') AS friend_name 
FROM users 
WHERE id = 1;

In this example, the [0] expression is used to access the first element of the friends array in the JSON object.

In conclusion, the json_extract() function in SQLite is a powerful tool for working with JSON data, allowing you to extract specific values or objects from JSON strings or columns in a table. Its flexibility and ease of use make it a valuable addition to any SQLite developer’s toolkit.