JSON Functions Rejecting Blob Inputs After SQLite 3.45 Update

Malformed JSON Errors When Passing BLOBs to JSON Functions

Issue Overview

A significant behavioral change occurred in SQLite 3.45 that causes JSON functions like json_extract, json_each, and the -> operator to reject BLOB inputs that were previously accepted. This manifests as runtime errors such as "malformed JSON" or silent failures in queries that worked in earlier versions (e.g., 3.44).

Key Symptoms

1. Queries using readfile('data.json') -> '$[1]' fail with "malformed JSON" errors despite valid JSON content.
2. json_each and other JSON table-valued functions reject BLOB columns storing JSON data.
3. Applications that stored JSON as BLOBs (via CAST('[...]' AS BLOB) or implicit typing) suddenly break.

Technical Context

• Prior Behavior (Pre-3.45): JSON functions incorrectly accepted BLOBs by implicitly treating them as TEXT. This violated documented behavior, which explicitly stated that BLOB inputs should throw errors.
• New Behavior (3.45+): JSON functions now interpret BLOBs as JSONB, a compact binary JSON format introduced in 3.45. If the BLOB does not conform to JSONB encoding rules, queries fail.

Example Breakdown

-- Worked pre-3.45; fails in 3.45:
SELECT readfile('data.json') -> 1; 
-- Now requires:
SELECT CAST(readfile('data.json') AS TEXT) -> 1;

JSONB Introduction and Undocumented BLOB Handling

Possible Causes

The root cause is SQLite 3.45’s introduction of JSONB, a binary encoding for JSON. This changed how BLOBs are interpreted by JSON functions:

1. JSONB Validation Rules
   BLOBs are now presumed to contain JSONB-encoded data. If the BLOB lacks a valid JSONB header or structure, JSON functions reject it. Legacy TEXT-based JSON stored in BLOBs fails this validation.

2. Undocumented Pre-3.45 Behavior
   Earlier versions allowed BLOBs to act as TEXT JSON due to an unintended bug. The JSON functions did not enforce the documented restriction against BLOB inputs, creating a hidden dependency for many users.

3. Schema Design Choices
   Applications storing JSON in BLOB columns (instead of TEXT) to avoid encoding issues or for binary storage efficiency are disproportionately affected.

Critical Implications

• Backward Compatibility Break: While the change aligns SQLite with its documentation, it disrupts workflows that relied on the bug.
• Ambiguous Data Handling: Mixing TEXT and BLOB JSON storage without explicit casting introduces fragility. For example, CAST('[1,2,3]' AS BLOB) creates a non-JSONB-compliant BLOB that 3.45+ rejects.
• Edge Case Risks:
  • Files with ASCII text accidentally matching JSONB headers (e.g., Gabcd → valid JSONB for "abcd").
  • Whitespace differences (e.g., tabs in JSON text) causing mismatches between TEXT and BLOB interpretations.

Casting, Compatibility Flags, and Schema Migration

Troubleshooting Steps, Solutions & Fixes

Immediate Fixes: Query-Level Adjustments

1. Explicit Casting to TEXT
   Modify queries to convert BLOBs to TEXT before JSON processing:

   SELECT json_extract(CAST(blob_column AS TEXT), '$.path') FROM table;
   

   This preserves existing schemas but requires query updates.

2. File Reading Workarounds
   For readfile(), which returns BLOBs, force TEXT conversion:

   SELECT json_extract(CAST(readfile('data.json') AS TEXT), '$[1]');
   

Schema-Level Corrections

1. Convert BLOB Columns to TEXT
   Alter tables to store JSON as TEXT:

   ALTER TABLE data ADD COLUMN val_temp TEXT;
   UPDATE data SET val_temp = CAST(val AS TEXT);
   ALTER TABLE data DROP COLUMN val;
   ALTER TABLE data RENAME COLUMN val_temp TO val;
   

   Advantages:

   • Eliminates casting in queries.
   • Aligns with SQLite’s intended JSON handling.

2. Validation Constraints
   Add CHECK constraints to ensure TEXT columns contain valid JSON:

   CREATE TABLE data (
     val TEXT CHECK (json_valid(val))
   );
   

Build Options for Legacy Compatibility

1. Compile-Time Flag
   Re-enable pre-3.45 BLOB handling using:

   CFLAGS="-DSQLITE_JSON_BLOB_INPUT_BUG_COMPATIBLE" ./configure
   

   This flag restores the buggy behavior where BLOBs are treated as TEXT JSON.

2. Caution with JSONB Ambiguity
   Enabling this flag reintroduces risks where BLOBs might be misinterpreted as JSONB or TEXT. For example, a BLOB containing Gabcd would parse as "abcd" under JSONB rules but would fail as TEXT JSON.

Future-Proofing Strategies

1. Adopt JSONB Encoding
   Convert existing JSON data to JSONB format if binary storage is required:

   INSERT INTO data VALUES (jsonb('[1, 2, 3]'));
   

   Limitation: JSONB is experimental in 3.45 and may evolve.

2. Leverage New Functions (Proposals)
   Monitor SQLite’s development for functions like readfileastext() or enhanced readfile() with encoding parameters.

3. Pre-Release Testing
   Test applications against SQLite pre-release versions (e.g., alpha/beta builds) to catch compatibility issues early.

Documentation and Community Guidance

1. Release Notes Vigilance
   Review SQLite’s changelogs for mentions of JSONB, BLOB handling, or JSON function changes.

2. Updated Documentation (3.45.1+)
   SQLite 3.45.1 documents the legacy BLOB behavior under JSON BLOB Input Bug Compatibility, formalizing support for it when the compile-time flag is set.

3. Community Best Practices

   • Avoid BLOB for JSON: Prefer TEXT for JSON storage unless JSONB is explicitly needed.
   • Explicit Casting: Use CAST in queries when interfacing with BLOBs from external sources (e.g., files).

Conclusion
The shift in SQLite 3.45’s JSON handling underscores the importance of adhering to documented behavior and proactive schema design. While disruptive, the changes prevent subtle data corruption risks and align SQLite with safer, more predictable JSON processing. Developers must audit their use of BLOBs with JSON functions, adopt explicit casting, or leverage compatibility flags to navigate this transition smoothly.