SQLite json_group_array Double Escaping Quotes: Regression or Bugfix?

Issue Overview: json_group_array Behavior Change in SQLite 3.39.4

The core issue revolves around a change in the behavior of the json_group_array function in SQLite when used over a subquery that generates JSON objects. Specifically, the function began double-escaping quotes in its output when upgrading from SQLite version 3.35.0 to 3.39.4. This change has led to confusion among users, as the output format no longer matches the expected JSON array of objects but instead produces an array of JSON-encoded strings.

In SQLite 3.35.0, the following query:

create table foo (
 a text,
 b text
);
insert into foo (a,b) values ("a1", "b1"),("a2","b2");
select json_group_array(obj) from
 (select
  rowid as id,
  json_object('a', a, 'b', b) AS obj
 from foo);

produces the output:

[{"a":"a1","b":"b1"},{"a":"a2","b":"b2"}]

However, in SQLite 3.39.4, the same query produces:

["{\"a\":\"a1\",\"b\":\"b1\"}","{\"a\":\"a2\",\"b\":\"b2\"}"]

This discrepancy arises because the json_group_array function in version 3.39.4 treats the JSON objects generated by the subquery as strings, resulting in double-escaping of quotes. This behavior can be mitigated by explicitly wrapping the obj column with the json() function, as shown below:

select json_group_array(json(obj)) from
 (select
  rowid as id,
  json_object('a', a, 'b', b) AS obj
 from foo);

This modification ensures consistent output across both versions:

[{"a":"a1","b":"b1"},{"a":"a2","b":"b2"}]

The change in behavior has been confirmed as a bug fix by the SQLite development team, addressing an inconsistency in how json_group_array processes JSON objects within subqueries. The fix ensures that JSON objects are treated as JSON values rather than strings, aligning with the intended functionality of the function.

Possible Causes: Why json_group_array Double Escapes Quotes

The root cause of this issue lies in how SQLite’s json_group_array function interprets JSON objects generated within a subquery. In SQLite 3.35.0, the function implicitly treated the output of json_object as a JSON value, directly embedding it into the resulting array. However, in SQLite 3.39.4, the function began treating the same output as a string, leading to double-escaping of quotes.

This change can be attributed to a bug fix in SQLite’s JSON handling logic. Prior to the fix, json_group_array did not properly distinguish between JSON values and JSON-encoded strings. As a result, it incorrectly embedded JSON objects directly into the array without ensuring proper JSON encoding. The fix introduced in version 3.39.4 addresses this by enforcing stricter handling of JSON values, ensuring that all inputs are properly encoded as JSON.

The issue is further compounded by the fact that SQLite’s JSON functions operate on both JSON values and JSON-encoded strings. When json_object generates a JSON object, it produces a JSON value. However, when this value is passed to json_group_array without explicit JSON encoding, the function may interpret it as a string, leading to double-escaping.

The explicit use of the json() function resolves this issue by ensuring that the input to json_group_array is treated as a JSON value. This approach aligns with SQLite’s JSON handling principles and prevents unintended double-escaping of quotes.

Troubleshooting Steps, Solutions & Fixes: Addressing json_group_array Double Escaping

To address the issue of json_group_array double-escaping quotes in SQLite 3.39.4, users can take the following steps:

1. Explicitly Use the json() Function: The most straightforward solution is to wrap the JSON object generated by the subquery with the json() function. This ensures that the input to json_group_array is treated as a JSON value rather than a string. For example:

   select json_group_array(json(obj)) from
    (select
     rowid as id,
     json_object('a', a, 'b', b) AS obj
    from foo);
   

   This approach produces consistent results across all versions of SQLite and prevents double-escaping of quotes.

2. Review and Update Existing Queries: If upgrading to SQLite 3.39.4 or later, review all queries that use json_group_array over subqueries generating JSON objects. Ensure that the JSON objects are explicitly wrapped with the json() function to avoid unexpected behavior.

3. Understand the Underlying JSON Handling: Familiarize yourself with SQLite’s JSON handling principles, particularly the distinction between JSON values and JSON-encoded strings. This understanding will help you write more robust queries and avoid similar issues in the future.

4. Test Queries Across Versions: When upgrading SQLite, test all JSON-related queries across both the old and new versions to identify any discrepancies. This proactive approach can help catch issues early and ensure a smooth transition.

5. Consult the SQLite Documentation and Changelog: While the changelog for SQLite 3.39.4 does not explicitly mention the change in json_group_array behavior, consulting the documentation and relevant forum discussions can provide valuable insights. The bug fix and related discussions are available at:

   • Bug Fix Commit
   • Original Bug Report

6. Leverage Community Resources: If you encounter further issues or have questions about SQLite’s JSON functions, consider reaching out to the SQLite community through forums or mailing lists. The community can provide additional guidance and share best practices for working with JSON in SQLite.

By following these steps, you can effectively address the issue of json_group_array double-escaping quotes and ensure that your SQLite queries produce the desired results. The key takeaway is to explicitly use the json() function when working with JSON objects in subqueries, as this aligns with SQLite’s JSON handling principles and prevents unintended behavior.

In conclusion, the change in json_group_array behavior between SQLite 3.35.0 and 3.39.4 highlights the importance of understanding how SQLite processes JSON values and strings. By adopting best practices and leveraging the json() function, you can avoid issues related to double-escaping and ensure consistent query results across different versions of SQLite.