SQLite’s JSON functions are surprisingly powerful, allowing you to treat JSON documents as first-class citizens within your relational database without needing a separate document store.
Let’s see it in action. Imagine you’re storing user profiles, and some attributes are optional or can vary wildly.
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
profile_data TEXT
);
INSERT INTO users (profile_data) VALUES
('{"name": "Alice", "age": 30, "address": {"street": "123 Main St", "city": "Anytown"}}'),
('{"name": "Bob", "age": 25, "contact": {"email": "bob@example.com", "phone": "555-1234"}}'),
('{"name": "Charlie", "interests": ["hiking", "coding"], "settings": {"theme": "dark"}}');
Now, let’s query this data. To get Alice’s city, you’d use json_extract():
SELECT json_extract(profile_data, '$.address.city') AS city
FROM users
WHERE json_extract(profile_data, '$.name') = 'Alice';
This returns 123 Main St.
But what if you want to find all users older than 28?
SELECT id, profile_data
FROM users
WHERE json_extract(profile_data, '$.age') > 28;
This will fetch Alice.
You can also check for the existence of keys. To find users with an email address:
SELECT id, profile_data
FROM users
WHERE json_extract(profile_data, '$.contact.email') IS NOT NULL;
This will return Bob.
The real magic happens when you start working with arrays. Let’s say you want to find users interested in "coding":
SELECT id, profile_data
FROM users
WHERE json_each(profile_data, '$.interests')
AND json_each.value = 'coding';
This query will select Charlie. json_each() unnests the JSON array into a temporary table-like structure, allowing you to filter on individual elements.
To insert or update JSON data, you use json_insert(), json_set(), and json_replace(). For example, to add a zip_code to Alice’s address:
UPDATE users
SET profile_data = json_set(profile_data, '$.address.zip_code', '12345')
WHERE json_extract(profile_data, '$.name') = 'Alice';
The json_set() function will create the zip_code key if it doesn’t exist, or update it if it does.
Consider a scenario where you need to check if a user has any contact information (either email or phone). You can combine json_extract with json_type:
SELECT id
FROM users
WHERE json_type(profile_data, '$.contact.email') = 'string'
OR json_type(profile_data, '$.contact.phone') = 'string';
This query would return Bob. json_type() is invaluable for ensuring you’re dealing with the expected data type before performing operations.
Most people understand json_extract for simple key-value pairs, but the ability to traverse nested objects and arrays with a unified path syntax is where the real power lies. You can access elements deep within a JSON structure as easily as top-level keys. For instance, $.a[0].b will navigate into an object a, then the first element of its array, then the key b.
When querying JSON data, remember that json_extract returns JSON values, which might be strings, numbers, booleans, or NULL. For comparisons, you often need to cast these or ensure you’re comparing like types. For example, WHERE json_extract(profile_data, '$.age') > 28 works because SQLite is often smart enough to infer numeric types for comparison. However, for strict type checking or when dealing with strings that look like numbers, explicitly using CAST(json_extract(profile_data, '$.age') AS INTEGER) can prevent unexpected behavior.
The next logical step is to explore how to efficiently index JSON data for faster queries, especially on large datasets.