SQLite json_extract: Complete Function Documentation (JSON1)

The json_extract() function is part of SQLite's JSON1 extension and is the primary way to pull values out of JSON documents stored in your database. This guide covers everything you need to know about using json_extract effectively.

Quick Reference

json_extract(json, path, ...)

• json: A valid JSON string or column containing JSON
• path: One or more JSON path expressions starting with $
• Returns: The extracted value(s) in SQL format

Basic Syntax

The simplest use case extracts a single value from a JSON object:

The $ represents the root of the JSON document. Use dot notation to access object properties.

Path Expression Syntax

SQLite's json_extract supports a subset of JSONPath syntax. Here's the complete reference:

| Path | Description | |------|-------------| | $ | The root element | | $.key | Property named "key" | | $[n] | Array element at index n (0-based) | | $.a.b.c | Nested property access | | $[0][1] | Nested array access | | $.a[0].b | Mixed object and array access |

Accessing Object Properties

Accessing Array Elements

Arrays use zero-based indexing with bracket notation:

Negative Array Indices

SQLite supports negative indices to count from the end of an array:

Complex Nested Paths

Combine object and array access for deeply nested structures:

Return Types

json_extract returns values as their SQL equivalents:

| JSON Type | SQL Type | |-----------|----------| | string | TEXT | | number | INTEGER or REAL | | boolean | INTEGER (1 or 0) | | null | NULL | | object | TEXT (JSON string) | | array | TEXT (JSON string) |

Extracting Multiple Values

You can extract multiple values in a single call by passing multiple paths. The result is a JSON array:

Handling Missing Paths

When a path doesn't exist, json_extract returns NULL:

Using json_extract with Tables

The real power comes from using json_extract with table columns:

Filtering with json_extract

Use json_extract in WHERE clauses to filter by JSON values:

Sorting by JSON Values

The -> and ->> Operators

SQLite 3.38.0 (2022) introduced shorthand operators as alternatives to json_extract:

• -> returns the JSON representation (like json_extract)
• ->> returns the SQL value (unquotes strings)

For most use cases, ->> is what you want since it returns proper SQL values. The operators are just syntactic sugar for json_extract.

Performance: Indexing json_extract

For frequent queries on JSON fields, create a generated column with an index:

This pattern gives you the flexibility of JSON with the performance of indexed columns. See our full guide on SQLite JSON indexing for more details.

Common Patterns

Extracting from Arrays of Objects

Coalescing with Default Values

Type Checking Before Extraction

Error Handling

json_extract returns NULL for invalid JSON rather than raising an error:

Use json_valid() if you need to validate JSON before processing:

JSON1 Extension Availability

The json_extract function is part of the JSON1 extension. In modern SQLite (3.38.0+), JSON functions are built-in and enabled by default. For older versions, you may need to compile SQLite with the -DSQLITE_ENABLE_JSON1 flag or load the extension manually.

Check if JSON functions are available:

Summary

The json_extract() function is essential for working with JSON data in SQLite:

• Use $.path.syntax to navigate JSON structures
• Access arrays with $[index] notation (supports negative indices)
• Returns SQL-native types for scalar values
• Returns JSON strings for objects and arrays
• Combine with generated columns and indexes for performance
• Use ->> operator as a cleaner alternative in SQLite 3.38.0+

For more SQLite JSON techniques, check out our guides on all SQLite JSON functions and indexing JSON with virtual columns.