How to Query JSON in SQLite

Accessing a Field

Use json_extract() to extract a value from a JSON object:

SELECT json_extract(data, '$.name') AS name FROM my_table;

Another example, of querying nested fields:

SELECT json_extract(data, '$.user[0].name') AS username FROM my_table;

Popular Functions

json_each()

The json_each() function can be useful when you need to break down a JSON array or object into its component parts.

Suppose we have the following data in our users table:

INSERT INTO users(data) VALUES 
('{"name": "John", "hobbies": ["reading", "cycling", "swimming"]}');

Now, if you want to list all the hobbies for John (i.e., transform the JSON array into individual rows), you can use the json_each() function:

WITH hobbies AS (
   SELECT json_each.value AS hobby
   FROM users, json_each(users.data, '$.hobbies')
   WHERE json_extract(data, '$.name') = 'John'
)
SELECT hobby FROM hobbies;

The result will be:

  hobby
----------
reading
cycling
swimming

json_object()

This function is used to create a JSON object.

For instance:

SELECT json_object('name', 'John', 'age', 25);

This will return:

{"name": "John", "age": 25}

json_group_array()

This function aggregates its arguments into a JSON array.

For instance, if you want to aggregate names from a table into a JSON array:

SELECT json_group_array(name) FROM users;

The result could be something like:

["John", "Doe", "Smith"]

And more…

SQLite offers several other JSON functions and capabilities. For more details, refer to the SQLite JSON documentation at https://www.sqlite.org/json1.html.