Do Firebird 4.0 JSON_QUERY DEFAULT clauses conform to SQL/JSON standard for missing keys?

Conformance Overview

Firebird 4.0's JSON_QUERY implementation provides a DEFAULT clause to handle missing keys, but it does not strictly conform to the full SQL/JSON standard syntax. While the standard specifies ON EMPTY and ON ERROR clauses to define behavior for missing data or malformed JSON, Firebird uses a simplified DEFAULT clause to provide a fallback value when a path evaluation results in an empty set.

Likely Implementation Divergence

In the SQL/JSON standard, the distinction between an "empty" result (the path exists but is empty) and an "error" (the path is invalid or the key is missing) is critical. Firebird 4.0 generally operates in a "lax" mode by default. In this mode, missing object keys or out-of-bounds array indices are treated as empty results rather than errors. Consequently, the DEFAULT clause triggers for both missing keys and empty values, collapsing two distinct standard states into a single fallback mechanism.

Verification Steps

To determine if specific edge cases (such as nested paths or array indices) diverge from your required standard, execute the following tests in a local Firebird 4.0 instance:

1. Setup: Create a table with a JSON-constrained column and insert a record with a known structure (e.g., {'user': {'id': 1, 'tags': ['sql']}}).
2. Missing Root Key: Query a key that does not exist at the root level with and without the DEFAULT clause.
3. Missing Nested Key: Query a path where the parent exists but the child does not (e.g., $.user.email).
4. Out-of-Bounds Array: Query an array index that exceeds the actual length (e.g., $.user.tags[5]).

-- Example Test Query
SELECT JSON_QUERY(json_column, '$.non_existent_key' DEFAULT 'FallbackValue')
FROM test_table;

Technical Assumptions

This analysis assumes the use of Firebird 4.0.x. It is important to note that JSON_QUERY is intended to return a JSON fragment (object or array). If you are attempting to extract a scalar value (string, number), JSON_VALUE should be used instead, as the two functions have different return type requirements and may handle defaults differently.

Diagnostic Requirement: To refine this recommendation, please specify if you are utilizing strict mode path evaluation, as this changes whether a missing key triggers an error state versus an empty state.