Should SQLite Support JSON5 for Input While Maintaining Strict JSON Output?

Issue Overview: JSON5 Support in SQLite’s JSON1 Extension

The core issue revolves around whether SQLite should extend its JSON1 extension to support JSON5, a relaxed version of JSON designed for human readability and ease of manual generation. JSON5 introduces several syntactic relaxations compared to strict JSON, such as allowing unquoted object keys, trailing commas, single-quoted strings, and comments. The primary concern is whether SQLite should accept JSON5 as input while continuing to generate only strict JSON as output. This approach would allow users to leverage JSON5’s readability benefits without compromising SQLite’s adherence to the JSON standard in its output.

The discussion highlights several key points:

1. Current State: SQLite’s JSON1 extension currently only supports strict JSON. It does not recognize JSON5 syntax, which means any JSON5 input would be considered invalid.
2. Proposed Change: The proposal is to allow SQLite to read JSON5 input but continue to write only strict JSON. This would involve updating functions like json_valid(), json(), and json_extract() to handle JSON5 input while ensuring that the output remains compliant with the JSON standard.
3. Potential Risks: Introducing JSON5 support could lead to compatibility issues, especially in applications that rely on SQLite’s strict JSON validation. For example, if json_valid() were to accept JSON5 input, it might break existing workflows that assume strict JSON compliance.

The discussion also touches on related formats like JSONC (JSON with Comments) and the challenges of implementing JSON5 support without introducing performance overhead or breaking changes. The consensus leans toward a cautious approach, where JSON5 support is introduced as an optional feature that does not interfere with existing strict JSON functionality.

Possible Causes: Why JSON5 Support is Being Considered

The push for JSON5 support in SQLite stems from several factors:

1. Human Readability: JSON5 is designed to be more human-friendly than strict JSON. Features like unquoted keys, trailing commas, and comments make it easier for developers to write and maintain JSON data manually. This is particularly useful in scenarios where JSON is used as a configuration format or for debugging purposes.
2. Interoperability with Other Tools: Some programming languages and tools already support JSON5 or similar relaxed JSON formats. For example, Python’s json module can serialize NaN and Infinity values, which are not part of the JSON standard but are allowed in JSON5. By supporting JSON5, SQLite could improve interoperability with these tools.
3. User Demand: Developers working with SQLite have expressed a desire for JSON5 support, particularly for use cases involving manual JSON editing or integration with systems that produce JSON5 output. This demand is driven by the practical benefits of JSON5’s relaxed syntax.
4. Performance Considerations: While JSON5 introduces additional complexity, the discussion suggests that it might be possible to implement a subset of JSON5 features without significant performance overhead. For example, supporting trailing commas or unquoted keys might not require extensive changes to SQLite’s JSON parser.

However, there are also valid concerns about introducing JSON5 support:

1. Compatibility Risks: Changing SQLite’s JSON1 extension to accept JSON5 input could break existing applications that rely on strict JSON validation. For example, if json_valid() were to accept JSON5 input, it might lead to unexpected behavior in applications that assume strict JSON compliance.
2. Implementation Complexity: JSON5 introduces several new syntactic features that would need to be supported in SQLite’s JSON parser. This could increase the complexity of the codebase and introduce new edge cases that need to be handled.
3. Performance Overhead: Supporting JSON5 might require additional parsing logic, which could impact performance, especially for large JSON documents. The discussion suggests that some JSON5 features, such as comments, might be particularly challenging to implement efficiently.

Troubleshooting Steps, Solutions & Fixes: Implementing JSON5 Support Without Breaking Changes

To address the issue of JSON5 support in SQLite, the following steps and solutions can be considered:

1. Introduce a Separate json5_valid() Function

One of the key concerns is that changing json_valid() to accept JSON5 input could break existing applications. To avoid this, SQLite could introduce a separate json5_valid() function that specifically checks for JSON5 compliance. This would allow users to opt into JSON5 validation without affecting existing strict JSON workflows.

For example:

-- Existing strict JSON validation
CREATE TABLE snapshots(data JSON CHECK (json_valid(data)));

-- Optional JSON5 validation
CREATE TABLE snapshots(data JSON CHECK (json5_valid(data)));

This approach ensures that existing applications continue to work as expected while providing an option for users who need JSON5 support.

2. Update json() to Handle JSON5 Input

The json() function in SQLite is used to parse and minify JSON data. To support JSON5 input, this function could be updated to accept JSON5 syntax while ensuring that the output remains strict JSON. This would allow users to leverage JSON5’s readability benefits without compromising SQLite’s adherence to the JSON standard.

For example:

-- Input: JSON5 with unquoted keys and trailing commas
SELECT json('{key: "value", trailing: "comma",}');

-- Output: Strict JSON
'{"key": "value", "trailing": "comma"}'

This approach ensures that SQLite’s output remains compatible with other tools and systems that expect strict JSON.

3. Implement a Subset of JSON5 Features

Given the complexity of fully implementing JSON5, SQLite could start by supporting a subset of JSON5 features that are most commonly requested and easiest to implement. For example:

• Trailing Commas: Allowing trailing commas in arrays and objects is a relatively simple change that can significantly improve readability.
• Unquoted Keys: Supporting unquoted keys in objects is another commonly requested feature that can be implemented without major performance overhead.
• Single-Quoted Strings: Allowing single-quoted strings can make JSON data easier to write and maintain.

By focusing on these features, SQLite can provide meaningful JSON5 support without introducing unnecessary complexity or performance overhead.

4. Maintain Strict JSON Output

Regardless of the input format, SQLite should continue to generate only strict JSON as output. This ensures compatibility with other tools and systems that expect strict JSON. For example:

-- Input: JSON5 with comments and unquoted keys
SELECT json('{key: "value", /* comment */ trailing: "comma",}');

-- Output: Strict JSON with comments removed
'{"key": "value", "trailing": "comma"}'

This approach ensures that SQLite’s output remains consistent and predictable, even when the input includes JSON5 features.

5. Provide Clear Documentation and Migration Guidance

To help users transition to JSON5 support, SQLite should provide clear documentation and migration guidance. This includes:

• Documentation: Clearly document the differences between JSON and JSON5, as well as the specific JSON5 features supported by SQLite.
• Migration Guide: Provide a step-by-step guide for migrating existing applications to use JSON5 features, including any necessary changes to schema definitions or application code.
• Examples: Include examples of common JSON5 use cases, such as configuration files or debugging output, to help users understand how to leverage JSON5 support in SQLite.

By providing clear documentation and guidance, SQLite can help users adopt JSON5 features without introducing unnecessary risks or confusion.

6. Monitor Performance and Compatibility

After implementing JSON5 support, SQLite should closely monitor performance and compatibility to ensure that the changes do not introduce unintended side effects. This includes:

• Performance Testing: Conduct thorough performance testing to ensure that JSON5 support does not introduce significant overhead, especially for large JSON documents.
• Compatibility Testing: Test existing applications to ensure that they continue to work as expected with the new JSON5 features. This includes testing edge cases and scenarios where JSON5 input might be misinterpreted as strict JSON.
• User Feedback: Collect feedback from users to identify any issues or challenges they encounter when using JSON5 features. Use this feedback to refine the implementation and address any concerns.

By monitoring performance and compatibility, SQLite can ensure that JSON5 support is both effective and reliable.

Conclusion

The question of whether SQLite should support JSON5 is a complex one, with valid arguments on both sides. On the one hand, JSON5 offers significant benefits in terms of human readability and ease of use, particularly for manual JSON editing and configuration files. On the other hand, introducing JSON5 support could introduce compatibility risks and implementation challenges, especially if not done carefully.

The proposed solution is to introduce JSON5 support in a way that minimizes these risks. This includes:

• Introducing a separate json5_valid() function to avoid breaking existing strict JSON workflows.
• Updating the json() function to handle JSON5 input while maintaining strict JSON output.
• Implementing a subset of JSON5 features that are most commonly requested and easiest to implement.
• Providing clear documentation and migration guidance to help users adopt JSON5 features.
• Monitoring performance and compatibility to ensure that the changes do not introduce unintended side effects.

By taking this cautious and incremental approach, SQLite can provide meaningful JSON5 support without compromising its commitment to strict JSON compliance and reliability. This would allow users to leverage the benefits of JSON5 while ensuring that SQLite remains a robust and dependable tool for working with JSON data.