Unexpected JSON_EACH Behavior When JSON Object Contains “value” Field

Issue Overview: JSON_EACH Misinterpretation of Double-Quoted Values

The core problem manifests when using SQLite’s JSON_EACH table-valued function with JSON objects containing a field named "value", particularly when employing double-quote characters (") instead of standard SQL single-quote (') delimiters. This creates a conflict between SQL’s identifier quoting rules and JSON object key interpretation.

In the observed case, a developer executed:

SELECT value FROM JSON_EACH(JSON_ARRAY(JSON_OBJECT("arg", 1, "value", 2)))  

Expecting to retrieve the numeric value 2 from the "value" property, but instead received the entire JSON object {"arg":1,"value":2}. The inverse operation with a non-colliding key name "valuex" worked as intended. This discrepancy stems from SQLite’s dual interpretation of double-quoted strings depending on context – sometimes as identifier quotes, other times as string literals in non-standard parsing modes.

JSON_EACH’s column output structure exposes this ambiguity through its value column name, which directly collides with the JSON object’s "value" key name when using double quotes. SQLite’s parser first interprets the double-quoted "value" as an identifier reference rather than a JSON key string literal, leading to unexpected whole-object returns instead of specific field extraction.

The confusion amplifies because SQLite historically allowed double-quoted string literals as a compatibility feature despite violating SQL standards. When combined with JSON functions that use column names matching JSON keys, this creates a perfect storm of misinterpretation. Developers accustomed to JavaScript-style JSON quoting or other SQL implementations with different string literal rules are particularly vulnerable to this pitfall.

Fundamental Causes: SQLite’s Dual-Use Quote Handling

Three primary factors converge to create this behavioral quirk:

1. Identifier vs Literal Ambiguity
SQLite uses single quotes (') for string literals and double quotes (") for identifiers by default per SQL-92 standards. However, it historically allowed double quotes as string literals when the quoted text doesn’t match any known identifier – a compatibility concession that becomes hazardous with JSON keys matching column names.

When the parser encounters "value" in:

JSON_OBJECT("arg", 1, "value", 2)  

It first attempts to resolve "arg" and "value" as identifiers. Since no such columns or tables exist in this context, SQLite falls back to interpreting them as string literals but retains their original quotation marks in the resulting JSON string. This creates JSON objects with keys wrapped in double quotes, which then interacts unexpectedly with JSON_EACH’s column naming.

2. JSON_EACH’s Column Naming Schema
The JSON_EACH virtual table exposes four fixed columns:

• key (TEXT): JSON object key or array index
• value (TEXT/INTEGER/REAL/BLOB): Extracted JSON value
• type (TEXT): JSON data type
• atom (TEXT/INTEGER/REAL/BLOB): Parsed atomic value
• id (INTEGER): Unique ID for JSON elements

When a JSON object contains a key named "value", the value column name in JSON_EACH creates a namespace collision. SQLite prioritizes column name resolution over JSON key extraction in the absence of explicit disambiguation syntax.

3. Strict Mode Configuration
As noted in the discussion, SQLite’s PRAGMA strict_mode=1 setting (introduced in version 3.37.0) modifies quote handling behavior:

PRAGMA strict_mode=1; -- Enforces standard SQL quote rules  

When enabled, double quotes always denote identifiers, never string literals. Queries using double quotes for string literals will fail with SQLITE_ERROR immediately, preventing the ambiguous parsing that leads to this JSON_EACH issue. However, many developers omit this setting due to backward compatibility concerns or lack of awareness.

Resolution Strategies: Disambiguation and Prevention

1. Quote Normalization
Force consistent quoting styles using SQL standard single quotes for string literals and double quotes only for identifiers:

-- Correct approach with unambiguous quoting  
SELECT value 
FROM JSON_EACH(
  JSON_ARRAY(
    JSON_OBJECT('arg', 1, 'value', 2)  -- Single quotes for JSON strings
  )
);

This generates clean JSON without quoted keys:

[{"arg":1,"value":2}]  

JSON_EACH can then properly distinguish between its value column and the JSON object’s value key.

2. Explicit JSON Key Referencing
Use JSON_EXTRACT with explicit path syntax when accessing named fields that conflict with JSON_EACH’s column names:

SELECT json_extract(value, '$.value') AS extracted_value  
FROM JSON_EACH(
  JSON_ARRAY(
    JSON_OBJECT('arg',1,'value',2)
  )
);

Output:

extracted_value
---------------
2  

This bypasses the column name collision by using JSON path expressions rather than relying on default column outputs.

3. Enable Strict SQL Mode
Activate strict mode to enforce standard quote handling and surface quoting errors during development:

PRAGMA strict_mode=1;  -- Enable at connection start  

-- Now attempts to use double-quoted strings throw errors:
SELECT "test";  -- Fails with SQLITE_ERROR: no such column: test  

For existing codebases, implement gradual strictness migration using:

PRAGMA strict_mode=OFF;  -- Initial permissive mode  
PRAGMA strict_mode=ON;   -- Test strictness incrementally  

4. Compile-Time Quote Handling
Rebuild SQLite with -DSQLITE_DQS=0 to globally disable double-quoted string literals:

./configure CFLAGS="-DSQLITE_DQS=0"  # Compilation flag  

This makes double quotes exclusively identifier delimiters across all database connections, eliminating the ambiguity at the parser level. Requires application-wide testing as it breaks legacy code relying on double-quoted strings.

5. Column Aliasing
Disambiguate JSON_EACH columns using explicit aliases when expecting JSON keys named "value":

SELECT x.value AS json_value 
FROM JSON_EACH(
  JSON_ARRAY(JSON_OBJECT('value',42))
) AS x;

Output:

json_value
----------
42  

6. JSON Schema Validation
Implement JSON schema checks to prevent key name collisions with SQLite’s JSON function column names:

CREATE TRIGGER validate_json BEFORE INSERT ON target_table  
BEGIN
  SELECT 
    CASE WHEN json_extract(new.json_column, '$.*') LIKE '%"value"%' 
    THEN RAISE(ABORT, 'Forbidden key name: value') 
    END;
END;

7. Query Analysis Tools
Integrate SQL parsing tools like sqlfluff or sqlparse to detect non-standard quoting during CI/CD pipelines:

sqlfluff lint --dialect sqlite queries.sql  # Identify double-quoted strings  

8. Type Affinity Awareness
Understand how JSON_EACH’s value column type affinity affects results. When the JSON "value" key contains non-scalar data:

SELECT value 
FROM JSON_EACH(JSON_ARRAY(JSON_OBJECT('value', JSON_OBJECT('nested',1))));

Output becomes the string representation:

'{"nested":1}'  

Use json_extract(value,'$') to preserve JSON validity for nested structures.

9. Version-Specific Behavior
Note that SQLite 3.38.0+ improved JSON error handling – ensure consistent versions across environments. Check compatibility with:

SELECT sqlite_version();  -- Verify >=3.38.0 for latest JSON fixes  

10. ORM and Driver Configuration
For applications using ORMs (SQLAlchemy, Sequelize, etc.), configure drivers to:

• Always use parameter binding instead of inline values
• Enable strict mode by default
• Validate generated SQL for quote consistency

# SQLAlchemy example with strict mode
engine = create_engine("sqlite:///file.db", connect_args={"strict": True})

By methodically applying these strategies, developers can eliminate JSON parsing ambiguities while maintaining compatibility with SQLite’s flexible quote handling legacy. The key lies in enforcing consistent quoting discipline through tooling, configuration, and schema design that anticipates namespace collisions between JSON keys and SQL virtual table columns.