Incorrect json_array_length Results After json_remove in SQLite: Diagnosis and Resolution

Unexpected Array Length Miscalculations in SQLite JSON Operations

When working with JSON arrays in SQLite, developers often rely on the json_array_length function to determine the number of elements in an array. A critical issue arises when combining json_array_length with json_remove, where the reported length of the modified array does not reflect the actual number of elements after removal. This discrepancy can lead to incorrect query results, broken application logic, or silent data integrity failures.

Key Observations and Impact

The problem manifests in scenarios where elements are dynamically removed from a JSON array using json_remove, followed by an immediate call to json_array_length to inspect the modified array. For example:

SELECT json_array_length(json_remove('[1,2,3,4]', '$[1]'));  

Expected Result: 3 (since element at index 1 is removed, leaving [1,3,4]).
Observed Result in Affected Versions: 4 (original array length is reported).

This inconsistency is not merely a cosmetic error. Applications that depend on accurate array length calculations for iteration, validation, or subsequent JSON operations will malfunction. The issue is particularly insidious because the array appears correct when printed directly, but the length value is stale:

SELECT 
  json_remove('[1,2,3]', '$[#-1]') AS modified_array,
  json_array_length(json_remove('[1,2,3]', '$[#-1]')) AS reported_length;  

Output in Affected Versions:

modified_array | reported_length  
[1,2]          | 3  

Here, the modified array visually contains two elements, but json_array_length incorrectly reports the original length of 3.

Version-Specific Behavior

The bug exhibits version-dependent behavior:

• SQLite 3.42.0 and Earlier: Correctly reports the updated array length.
• SQLite 3.43.0 (Pre-Patch): Incorrectly retains the original length.
• SQLite 3.43.0 (Post-Patch) and Trunk: Fixed to report the correct length.

This indicates that the issue was introduced in a specific version range and later resolved. Environments using SQLite Fiddle or pre-patch builds of SQLite 3.43.0 are particularly vulnerable.

Underlying Mechanics of JSON Functions in SQLite

To fully grasp the problem, it’s essential to understand how SQLite handles JSON data internally. SQLite does not have a native JSON storage type; instead, it stores JSON as plain text and parses it on-the-fly using the JSON1 extension. Functions like json_remove and json_array_length operate on this text representation:

1. Input Parsing: When a JSON function is invoked, the input text is parsed into an abstract syntax tree (AST) or an intermediate binary representation.
2. Modification Operations: json_remove manipulates the AST by deleting the specified elements.
3. Output Serialization: The modified AST is converted back to a valid JSON text string.
4. Length Calculation: json_array_length parses the output JSON text to count the elements.

The bug arises when the intermediate representation is not fully serialized back to text after modification. Subsequent functions may inadvertently read from the outdated AST or metadata instead of the serialized text, leading to incorrect length calculations.

Root Causes of Stale Array Length Values in JSON Operations

Improper Handling of JSON Metadata in Cached Intermediate Representations

SQLite optimizes performance by caching intermediate representations of JSON objects during complex operations. When multiple JSON functions are chained (e.g., json_array_length(json_remove(...))), the engine may skip re-serializing the modified JSON object, assuming the cached metadata (like array length) is still valid. This optimization is incorrect when the object is mutated, as metadata becomes stale.

Technical Breakdown:

• Step 1: json_remove('[1,2,3,4]', '$[1]') parses the input string into an AST.
• Step 2: The element at index 1 (value 2) is removed from the AST.
• Step 3: Instead of serializing the modified AST back to a JSON string, the engine retains the AST in memory.
• Step 4: json_array_length reads the length property directly from the AST’s metadata, which was not updated during the removal operation.

This bypassing of serialization causes json_array_length to report the original length stored in the AST’s metadata, not the actual count of elements in the modified AST.

Version-Specific Regression in JSON1 Extension

The bug was inadvertently introduced in SQLite 3.43.0 due to changes aimed at improving the performance of JSON operations. Specifically, a new optimization avoided redundant serialization of JSON objects when passing results between functions. While this reduced computational overhead, it violated the assumption that all JSON functions operate on fully serialized text, leading to metadata mismatches.

Code Change Analysis:
In pre-patch versions of SQLite 3.43.0, the json_remove function modified the AST but did not invalidate the cached length metadata. The json_array_length function relied on this cached value instead of recounting the elements in the serialized text. Post-patch versions enforce a "serialize-on-mutation" rule, ensuring that any modification to the JSON object invalidates cached metadata and triggers a re-serialization.

Incorrect Indexing in Dynamic Array Modifications

Another contributing factor is the handling of negative array indices (e.g., $[#-1] to target the last element). In some cases, the index calculation logic failed to account for previously removed elements, leading to incorrect element removal and length reporting. This is evident in the example:

SELECT json_array_length(json_remove('[1]', '$[#-1]'));  

Expected Result: 0 (array becomes empty after removal).
Observed Result: 1 (original length retained).

Here, the removal operation itself may have failed to update the array’s internal state, causing json_array_length to read the pre-removal length.

Comprehensive Solutions for Correct Array Length Calculations

Immediate Fix: Upgrade to Patched SQLite Versions

The definitive resolution is to upgrade to a SQLite version that includes the fix for this bug. The patch was applied to both the trunk (main development branch) and the branch-3.43 (maintenance branch for version 3.43.x).

Steps to Verify and Upgrade:

1. Check Current SQLite Version:

   SELECT sqlite_version();  
   

   If the result is 3.43.0 and the build date is before August 30, 2023, the bug is present.

2. Download Updated Binaries:

   • Precompiled Binaries: Obtain the latest SQLite binaries from the official website.
   • Compile from Source: Clone the SQLite source repository and ensure you’re using a commit after the fix (check for commits dated after 2023-08-30).

3. Test the Fix:
   Run the problematic query to confirm the correct behavior:

   SELECT json_array_length(json_remove('[1,2,3,4]', '$[1]'));  
   

   Expected Output: 3.

Workarounds for Unpatched Environments

If upgrading is not immediately feasible, employ these strategies to circumvent the bug:

Force JSON Re-Serialization with Nested Functions

Wrap the output of json_remove in json or json_array to force re-serialization:

SELECT json_array_length(json(json_remove('[1,2,3,4]', '$[1]')));  

The json() function parses and re-serializes the modified array, ensuring metadata is updated.

Manual Array Reconstruction

Instead of removing elements, rebuild the array while excluding unwanted elements:

SELECT json_array_length(
  (SELECT json_group_array(value) 
   FROM json_each('[1,2,3,4]') 
   WHERE CAST(key AS INT) != 1)
);  

This approach uses json_each to iterate over the array elements, skips the unwanted index, and reconstructs the array with json_group_array.

Use Temporary Tables for Intermediate Storage

Store the modified array in a temporary table to force serialization:

CREATE TEMP TABLE temp_array AS 
SELECT json_remove('[1,2,3,4]', '$[1]') AS arr;  

SELECT json_array_length(arr) FROM temp_array;  

Writing the array to a table ensures it is stored as a text string, which is fully parsed when read back.

Preventing Future Regressions

To avoid similar issues, adopt these best practices:

1. Automated Regression Testing: Include test cases for JSON function chains in your test suite. Example:

   -- Verify array length after removal
   SELECT 
     CASE 
       WHEN json_array_length(json_remove('[1,2,3,4]', '$[1]')) = 3 
       THEN 'PASS' 
       ELSE 'FAIL' 
     END;  
   

2. Monitor SQLite Changelogs: Subscribe to SQLite’s RSS feed or check the news page for updates on fixes and regressions.
3. Avoid Over-Optimization Assumptions: When chaining JSON functions, explicitly force serialization if unexpected behavior occurs.

Deep Dive: How the Patch Resolves the Issue

The patch addresses the root cause by modifying how the JSON1 extension handles mutated JSON objects. Key changes include:

• Invalidation of Cached Metadata: After any mutation operation (e.g., json_remove, json_set), the cached length and other metadata are marked as invalid.
• Lazy Re-Serialization: The JSON text is re-serialized only when needed (e.g., when passing the result to another function or storing it in a table). This balances performance and correctness.
• Index Adjustment Logic: The handling of negative indices ($[#-1]) was refined to account for dynamic changes in array size during mutations.

These changes ensure that json_array_length always operates on the most up-to-date representation of the JSON array, eliminating the stale length problem.

Conclusion

The incorrect json_array_length results after using json_remove stem from a failure to update internal metadata during JSON mutations. This issue is resolved in patched versions of SQLite 3.43.0 and later. Developers must verify their SQLite version, apply upgrades where possible, and employ workarounds in environments where immediate upgrades are not feasible. By understanding the interplay between JSON function internals and adopting defensive coding practices, such as forced re-serialization and comprehensive testing, teams can mitigate the risks of similar issues in their applications.