The Complete Guide to SQLite JSON Functions

A few days ago I wrote about one of my favourite SQLite patterns - using virtual generated columns with json_extract to index JSON data at full B-tree speed. If you haven't read that one yet, I'd recommend it.

But that post focused on just one function: json_extract. SQLite actually has an entire suite of JSON functions, and they're surprisingly powerful. So I thought I'd write a comprehensive guide covering all of them.

We'll use a real-world example throughout: an e-commerce product catalog. Products have names, prices, categories, tags, variants - the kind of semi-structured data that's perfect for JSON.

Let's dive in.

Setting Up Our Product Catalog

First, let's create a products table and populate it with some sample data:

Now we have a proper product catalog to work with. Let's explore what we can do with it.

1. Extracting Data from JSON

The most common operation is pulling values out of JSON. SQLite gives you three ways to do this.

json_extract()

The workhorse function. Pass it a JSON value and a path, and it returns the value at that path:

You can also reach into nested objects and arrays:

The -> and ->> Operators

SQLite 3.38.0 introduced shorthand operators that make extraction cleaner:

• -> returns the JSON value (keeps quotes on strings, preserves arrays/objects as JSON)
• ->> returns the SQL value (strips quotes from strings, more useful for most cases)

For most queries, you'll want ->> since it gives you proper SQL values you can compare and sort.

2. Validating and Inspecting JSON

Before you work with JSON data, you might want to check what you're dealing with.

json_valid()

Returns 1 if the string is valid JSON, 0 otherwise:

json_type()

Tells you what type of JSON value you're looking at:

json_array_length()

Counts elements in a JSON array:

3. Creating JSON

Sometimes you need to build JSON from SQL values. SQLite has functions for that too.

json()

Validates and minifies a JSON string:

json_object()

Creates a JSON object from key-value pairs:

json_array()

Creates a JSON array from values:

Combining Them

Here's where it gets powerful - building complex JSON structures from query results:

4. Modifying JSON

SQLite lets you update JSON values in place without replacing the entire document. This is incredibly useful.

json_set()

Sets a value at a path, creating the path if it doesn't exist:

json_insert()

Only inserts if the path doesn't already exist (won't overwrite):

json_replace()

Only replaces if the path already exists (won't create new paths):

json_remove()

Removes values at specified paths:

json_patch()

Applies an RFC 7396 merge patch. This is great for partial updates:

Updating Rows in Place

Combine these with UPDATE statements to modify JSON in your tables:

5. Querying JSON Arrays with Table Functions

This is one of the most powerful features. json_each() and json_tree() turn JSON into rows you can query.

json_each()

Expands a JSON array (or object) into one row per element:

The magic happens when you combine this with your data. Let's find all products that have a specific tag:

Or list all unique tags across your catalog:

Working with Nested Arrays

Let's query our product variants - each product has an array of variant objects:

json_tree()

Like json_each() but recursively walks the entire JSON structure. Useful for finding values at any depth:

6. Aggregating into JSON

The flip side of expanding JSON is building it from multiple rows.

json_group_array()

Collects values from multiple rows into a JSON array:

json_group_object()

Builds a JSON object from key-value pairs across rows:

Building Complex Reports

Combine everything to build rich JSON reports:

Wrapping Up

SQLite's JSON support is surprisingly complete. You can:

• Extract values with json_extract(), ->, and ->>
• Validate with json_valid() and inspect with json_type()
• Create JSON using json_object() and json_array()
• Modify in place with json_set(), json_insert(), json_replace(), json_remove(), and json_patch()
• Query arrays by expanding them with json_each() and json_tree()
• Aggregate results into JSON with json_group_array() and json_group_object()

And remember - if you need to query JSON fields frequently, check out my previous post on virtual columns and indexing. You can combine these functions with generated columns and indexes for full performance.

SQLite continues to surprise me with how capable it is. There's a lot packed into that little library.

Thanks for reading!

Jay

---