SQLite json_extract() Function

Summary: in this tutorial, you will learn how to use the SQLite json_extract() function to extract one or more JSON values from JSON data.

Introduction to the SQLite json_extract() function #

In SQLite, the json_extract() function allows you to extract one or more values from JSON data.

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

json_extract(json_value, path1 [, path2]...)

In this syntax:

json_value is the JSON data from which you want to extract values.
path1, path2, … is the JSON path that locates the values in the json_value. If you provide multiple JSON paths, the json_extract() function returns the extracted values as elements of a JSON array.

Notice that if you provide one JSON path argument (path1), the json_extract() function returns an SQL value.

However, if you provide two or more path arguments, (path1, path2, and so on), the json_extract() function returns a JSON value i.e., a JSON array that holds the extracted values.

Notice that MySQL’s json_extract() function always returns a JSON value.

If the path does not locate any element in json_value, the json_extract() function returns NULL. Additionally, the json_extract() function also returns NULL if any argument is NULL.

SQLite json_extract() function examples #

Let’s use the json_extract() function to extract values from JSON data.

1) Extracting a single value #

The following example uses the json_extract() function to extract the name from a JSON value:

SELECT json_extract('{"name": "Alice", age: 22}', '$.name') name;

Output:

name
-----
Alice

In this example, the path '$.name' locates the name property of a JSON object. Therefore, the json_extract() function returns the value of the name property, which is Alice.

SELECT typeof(json_extract('{"name": "Alice", age: 22}', '$.name')) type;

The result is not a JSON value but has a type of TEXT. You can verify it using the typeof() function:

SELECT typeof(json_extract('{"name": "Alice", age: 22}', '$.name')) type;

Output:

type
----
text

2) Extracting multiple values #

The following example uses the json_extract() function to extract multiple values specified by multiple paths:

SELECT json_extract('{"name": "Alice", age: 22}', '$.name', '$.age') result;

Output:

result
------------
["Alice",22]

In this example, we use multiple JSON paths:

'$.name' – locates the name property.
'$.age' – matches the age property.

The json_extract() function extracts values and puts them into a JSON array. The type of the result is JSON, not TEXT.

SELECT typeof(json_extract('{"name": "Alice", age: 22}', '$.name', '$.age')) type;

Output:

type
----
text

Notice that SQLite uses the TEXT storage type to store JSON. There is no separate JSON data type.

3) Extracting array elements from JSON data #

The following example uses the json_extract() to extract the second array element from a JSON array:

SELECT json_extract('["red","green","blue"]', '$[2]') color;

Output:

color
-----
blue

In this example, the JSON path ‘$[2]’ specifies the second element of the top-level array of a JSON document.

4) Extracting nested values from JSON data #

The following example uses the json_extract() to extract a nested value from JSON data:

SELECT json_extract('{"name": "t-shirt","attributes": {"size": "XL", "colors": ["red","green","blue"]}}', '$.attributes.size') size;

Output:

size
----
XL

In this example, the '$.attributes.size' path specifies the size property of the attributes property of the top-level JSON object in JSON data.

5) Extracting data with a non-existent path #

The following example uses the json_extract() function to extract values from a JSON with a path that does not exist ('$.color'):

SELECT json_extract('{"name": "t-shirt","attributes": {"size": "XL", "colors": ["red","green","blue"]}}', '$.name','$.color') result;

Output:

result
----------------
["t-shirt",null]

6) Using SQLite json_extract() function with table data #

First, create a new table called books to store book data:

CREATE TABLE books(
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    data TEXT NOT NULL
);

Second, insert rows into the books table:

INSERT INTO books (title, data) 
VALUES
    ('The Catcher in the Rye', '{"isbn": "0316769487", "author": {"name": "J.D. Salinger"}}'),
    ('To Kill a Mockingbird', '{"isbn": "0061120081", "author": {"name": "Harper Lee"}}'),
    ('1984', '{"isbn": "0451524934", "author": {"name": "George Orwell"}}');

Third, retrieve data from the books table:

SELECT * FROM books;

Output:

id  title                   data

--  ----------------------  -----------------------------------------------------------
1   The Catcher in the Rye  {"isbn": "0316769487", "author": {"name": "J.D. Salinger"}}
2   To Kill a Mockingbird   {"isbn": "0061120081", "author": {"name": "Harper Lee"}}
3   1984                    {"isbn": "0451524934", "author": {"name": "George Orwell"}}

Finally, extract the ISBN and name from the JSON data using the json_extract() data:

SELECT
  title,
  json_extract (data, '$.isbn')   isbn,
  json_extract (data, '$.author.name') author
FROM
  books;

Output:

title                   isbn        author
----------------------  ----------  -------------
The Catcher in the Rye  0316769487  J.D. Salinger
To Kill a Mockingbird   0061120081  Harper Lee
1984                    0451524934  George Orwell

Summary #

Use the json_extract() function to extract one or more JSON values from JSON data.

Was this tutorial helpful?