JSON in SQLite is fantastic

Working with JSON is most the time decent enough. One could definitly argue that there is good reason this format has “won” over other formats, such as XML or shudders YAML.

However, where things usually fall short is when we need to persist this JSON. Of course, we could easily just keep it as a string, then deal with parsing it, and getting the contents from that string ourselves. But, why make things more complicated than necessary? Especially when SQLite has our back.

SQLite, in case you didn’t know, SQLite supports ”JSON” out of the box. Meaning it supports you dumping JSON into a column and then offers a set of functions that can be used to query and manipulated nested properties and values in that JSON string.

Just look at this goodness:

SELECT json_extract(json_column, '$.key') FROM table_name;

See that $? That’s JSONPath syntax, and SQLite speaks it fluently. You can drill down into nested objects, access array elements, and even use wildcards. It’s surprisingly powerful:

-- Get nested values
SELECT json_extract(data, '$.user.email') FROM users;

-- Access array elements
SELECT json_extract(data, '$.tags[0]') FROM posts;

-- Update JSON values in place
UPDATE posts
SET data = json_set(data, '$.published', true)
WHERE id = 123;

-- Query based on JSON content
SELECT * FROM posts
WHERE json_extract(data, '$.category') = 'tech';

-- Even aggregate JSON arrays
SELECT json_group_array(name) FROM users;

But here’s where it gets really interesting. SQLite can create indexes on JSON expressions:

CREATE INDEX idx_user_email ON users(json_extract(data, '$.email'));

Now your JSON queries are just as fast as regular column queries. You get the flexibility of schema-less data with the performance of a proper database.

The best part? You can gradually migrate from JSON to proper columns if needed, or keep things hybrid. Store your stable schema as columns and put the flexible stuff in JSON. It’s the best of both worlds.

No separate document database needed. No complex ORM mappings. Just SQLite and JSON, working together beautifully.