SQLite JSON Wildcard Array Queries: Limitations and Workarounds

Absence of Native JSON Wildcard Array Query Support in SQLite

SQLite’s JSON1 extension provides foundational tools for parsing and querying JSON data, but it lacks native support for wildcard array traversal in JSON paths. This limitation becomes apparent when attempting to extract values from nested JSON arrays using syntax like $.c[*].f, which is standard in databases such as PostgreSQL and MySQL. In those systems, the [*] wildcard operator returns a set of values from all elements in an array, enabling direct filtering in WHERE clauses. For example, querying $.c[*].f against the JSON object {"a":2,"c":[{"f":2},{"f":7},{"f":7}]} would return [2,7,7], allowing developers to filter rows based on the presence of specific values in the array.

In SQLite, the json_extract() function returns scalar values or JSON fragments rather than flattened tuples or sets. When applied to arrays, json_extract() returns the entire array as a JSON-formatted string, not individual elements. For instance, json_extract('{"a":2,"c":[{"f":2},{"f":7},{"f":7}]}', '$.c') returns [{"f":2},{"f":7},{"f":7}], which cannot be directly filtered using standard SQL operators like IN or =. This design forces developers to use auxiliary functions such as json_each() or json_tree() to unpack arrays into relational rows.

The absence of wildcard support impacts two critical areas:

1. Query Readability: Queries involving nested JSON arrays require verbose joins and subqueries with json_each(), making them harder to write and maintain.
2. Indexing Limitations: SQLite cannot create indexes on JSON array elements accessed via json_each() in the same way it can for columns or generated columns.

Performance and Complexity Challenges with JSON_EACH() Workarounds

The primary workaround for querying JSON arrays in SQLite involves using the json_each() table-valued function. This function "unfolds" a JSON array into a virtual table where each row corresponds to an array element. While functional, this approach introduces significant complexity and performance overhead, especially with deeply nested arrays or large datasets.

How JSON_EACH() Works

Consider the JSON object {"a":2,"c":[{"f":2},{"f":7},{"f":7}]}. To extract all f values from the c array, you would write:

SELECT value ->> 'f' AS f_value  
FROM json_each(
  '{"a":2,"c":[{"f":2},{"f":7},{"f":7}]}',  
  '$.c'  
);

This returns a virtual table:

┌─────────┐  
│ f_value │  
├─────────┤  
│ 2       │  
│ 7       │  
│ 7       │  
└─────────┘  

To filter rows where f_value equals 7, you would add a WHERE clause:

SELECT *  
FROM main_table  
WHERE EXISTS (
  SELECT 1  
  FROM json_each(main_table.json_column, '$.c')  
  WHERE value ->> 'f' = 7  
);

Drawbacks of JSON_EACH()

1. Query Complexity: Each level of array nesting requires additional joins or subqueries. For a JSON structure like $.a.b[*].c[*].d, the query becomes unwieldy.
2. Indexing Limitations: SQLite cannot create indexes on the results of json_each(), as it operates on JSON data at query runtime. This forces full-table scans or manual caching of JSON data into indexed columns.
3. Performance Overhead: Unpacking large JSON arrays with json_each() consumes CPU and memory, particularly in queries involving multiple rows.

Optimizing JSON Array Queries and Exploring Alternatives in SQLite

Step 1: Precompute JSON Data with Generated Columns

To enable indexing, use generated columns to materialize JSON values into static columns. For example:

CREATE TABLE main_table (
  id INTEGER PRIMARY KEY,  
  json_data TEXT,  
  -- Extract all 'f' values from the 'c' array as a comma-separated string  
  f_values TEXT AS (  
    SELECT GROUP_CONCAT(value ->> 'f')  
    FROM json_each(json_data, '$.c')  
  ) STORED  
);  

CREATE INDEX idx_f_values ON main_table(f_values);  

This allows efficient filtering:

SELECT * FROM main_table WHERE f_values LIKE '%7%';  

Tradeoff: The GROUP_CONCAT approach flattens array values into a string, losing their individual identity.

Step 2: Normalize JSON Arrays into Relational Tables

For heavily queried JSON arrays, extract them into a separate table:

CREATE TABLE json_c_elements (  
  parent_id INTEGER,  
  f_value INTEGER,  
  FOREIGN KEY (parent_id) REFERENCES main_table(id)  
);  

INSERT INTO json_c_elements (parent_id, f_value)  
SELECT id, value ->> 'f'  
FROM main_table, json_each(main_table.json_data, '$.c');  

CREATE INDEX idx_json_c ON json_c_elements(f_value);  

Querying becomes straightforward:

SELECT *  
FROM main_table  
WHERE id IN (  
  SELECT parent_id  
  FROM json_c_elements  
  WHERE f_value = 7  
);  

Tradeoff: This requires upfront data migration and additional storage.

Step 3: Use Recursive Queries for Nested Arrays

For multi-level arrays, combine json_tree() with recursive CTEs:

WITH RECURSIVE json_paths AS (  
  SELECT  
    json_tree.value AS node,  
    json_tree.fullkey AS path  
  FROM main_table, json_tree(main_table.json_data)  
  WHERE main_table.id = 1  
)  
SELECT node  
FROM json_paths  
WHERE path LIKE '$.c[%].f';  

Tradeoff: Recursive queries are computationally expensive and not indexable.

Step 4: Leverage Application-Side Processing

Offload JSON processing to the application layer. Retrieve the entire JSON array and filter it using programming languages like Python or JavaScript, which have robust JSON libraries.

Step 5: Advocate for Native Wildcard Support

While SQLite’s development team has not announced plans to support JSON wildcards, users can file an official feature request at SQLite’s GitHub repository. Highlight use cases involving analytics, logging, or hierarchical data to prioritize the feature.

Final Considerations

• Benchmark Workarounds: Test the performance of generated columns vs. normalized tables vs. application-side processing.
• Avoid Over-Nesting: Design JSON schemas to minimize nesting if SQLite is the primary database.
• Combine Tools: Use SQLite for structured data and pair it with dedicated JSON databases like MongoDB for complex queries.

This guide provides a comprehensive roadmap for addressing SQLite’s lack of native JSON wildcard array support, balancing immediate workarounds with long-term optimizations.