Regression in JSON Handling After SQLite 3.39 Update

JSON Data Double-Encoding Issue in SQLite 3.39

The core issue revolves around a regression in JSON handling observed after upgrading to SQLite 3.39. Specifically, JSON data is being double-encoded when passed through subqueries or views, leading to unexpected behavior in applications that rely on JSON manipulation. This regression manifests when JSON data is extracted, processed, and re-encoded within nested queries or triggers, resulting in JSON strings being treated as plain text and subsequently re-encoded as JSON strings again. This behavior was not present in SQLite 3.38.5, where JSON data was correctly preserved and handled without double-encoding.

The issue is particularly problematic for applications that use JSON for change tracking, logging, or other data transformation workflows. For example, a trigger designed to log changes to a JSON column in a table may inadvertently store double-encoded JSON strings in the log table, making it difficult to parse and interpret the logged data correctly. This regression has significant implications for data integrity and application logic, especially in systems where JSON data is a core component of the schema.

Root Cause: JSON Subtype Propagation in Subqueries and Views

The root cause of this issue lies in how SQLite handles JSON subtypes in subqueries and views. Prior to SQLite 3.39, JSON subtypes were inconsistently propagated through subqueries and views, depending on the query planner’s optimizations. This inconsistency led to unpredictable behavior, where JSON data might retain its subtype in some cases but lose it in others. To address this inconsistency, SQLite 3.39 introduced a fix that ensures JSON data is always converted to TEXT when passed through subqueries or views, aligning with the limitations of the SQLite file format.

The SQLite file format only supports four basic data types: TEXT, INTEGER, REAL, and NULL. While SQLite can handle JSON as an intermediate data type within a single query, it cannot store JSON as a distinct data type in the database file. As a result, JSON data must be converted to TEXT when stored or passed through subqueries and views. The fix in SQLite 3.39 enforces this conversion consistently, ensuring that JSON data is always treated as TEXT in these contexts. However, this fix also introduced the double-encoding issue, as JSON data is now re-encoded as a JSON string when it should be treated as a JSON object.

The inconsistency in handling JSON data is further highlighted by the fact that quoted JSON strings (e.g., '"hello"') are not affected by this issue. This discrepancy suggests that the JSON subtype is being handled differently depending on whether the data is a JSON object or a JSON string. The fix in SQLite 3.39 addresses the inconsistency but also introduces a new challenge for developers who need to work with JSON data in subqueries and views.

Resolving Double-Encoding with json_valid and Conditional Logic

To resolve the double-encoding issue, developers can use conditional logic with the json_valid function to determine whether a value should be treated as JSON or plain text. The json_valid function checks whether a given string is a valid JSON object or array, allowing developers to conditionally apply the json function to re-encode the data as JSON if necessary. This approach ensures that JSON data is handled correctly, regardless of whether it is passed through subqueries or views.

For example, consider the following query, which demonstrates the double-encoding issue in SQLite 3.39:

SELECT json_array(d) FROM (
  SELECT json_extract(value, '$') AS d
  FROM json_each(json_array('{"k1": "v1"}'))
);

In SQLite 3.39, this query returns ["{\"k1\":\"v1\"}"], where the JSON object is double-encoded as a JSON string. To avoid this, developers can modify the query to use json_valid and conditional logic:

SELECT json_array(CASE WHEN json_valid(d) THEN json(d) ELSE d END) FROM (
  SELECT json_extract(value, '$') AS d
  FROM json_each(json_array('{"k1": "v1"}'))
);

This modified query checks whether the value of d is a valid JSON object using json_valid. If it is, the json function is applied to re-encode the data as JSON. Otherwise, the value is treated as plain text. The result is [{"k1":"v1"}], which matches the expected behavior in SQLite 3.38.5.

This approach can also be applied to more complex queries and triggers. For example, the trigger in the original issue can be modified to use json_valid and conditional logic:

CREATE TRIGGER IF NOT EXISTS ct2_changes_INSERT
  AFTER INSERT ON ct2
  BEGIN
    INSERT INTO changelog
      ("action", "table", "primary_key", "changes")
    SELECT
      'INSERT', 'ct2', NEW."id", "changes"
    FROM (
      SELECT json_group_object(
        col, json_array("oldval", "newval")) AS "changes"
      FROM (
        SELECT json_extract(value, '$[0]') AS "col",
            json_extract(value, '$[1]') AS "oldval",
            json_extract(value, '$[2]') AS "newval"
        FROM json_each(json_array(json_array('data', NULL, 
          CASE WHEN json_valid(NEW."data") THEN json(NEW."data") ELSE NEW."data" END
        )))
        WHERE "oldval" IS NOT "newval"
      )
    );
  END;

This modified trigger ensures that the data column is correctly encoded as JSON, avoiding the double-encoding issue.

In summary, the regression in JSON handling in SQLite 3.39 is a result of a bug fix that enforces consistent JSON-to-TEXT conversion in subqueries and views. While this fix addresses an underlying inconsistency, it also introduces a new challenge for developers working with JSON data. By using json_valid and conditional logic, developers can ensure that JSON data is handled correctly, avoiding double-encoding and maintaining compatibility with previous versions of SQLite.