SQLite JSON Aggregate Functions Returning Empty Arrays/Objects vs. NULL for Empty Aggregations

JSON Aggregate Functions Return Empty Arrays/Objects Instead of NULL When No Rows Match

Issue Overview: JSON Aggregate Functions vs. Traditional SQL Aggregate Behavior in Empty Result Scenarios

In SQLite, the json_group_array() and json_group_object() functions return empty JSON arrays ([]) and empty JSON objects ({}), respectively, when no rows match the query’s WHERE clause. This contrasts with traditional SQL aggregate functions like sum() or avg(), which return NULL when no rows are aggregated. Similarly, the group_concat() function returns NULL when no rows are matched, unless a separator is explicitly provided.

For example, consider a table test(id, value) with three rows:

CREATE TABLE test(id, value);
INSERT INTO test VALUES (1,'a'), (2,'b'), (3,'c');

Running these queries:

-- Returns [[1,"a"],[2,"b"],[3,"c"]]  
SELECT json_group_array(json_array(id, value)) FROM test WHERE id > 0;  

-- Returns [] (empty array)  
SELECT json_group_array(json_array(id, value)) FROM test WHERE id > 3;  

-- Returns {"1":"a","2":"b","3":"c"}  
SELECT json_group_object(id, value) FROM test WHERE id > 0;  

-- Returns {} (empty object)  
SELECT json_group_object(id, value) FROM test WHERE id > 4;  

Meanwhile, group_concat() behaves differently:

-- Returns [[1,"a"],[2,"b"],[3,"c"]]  
SELECT '[' || group_concat('[' || id || ',"' || value || '"]') || ']' FROM test WHERE id > 0;  

-- Returns NULL  
SELECT '[' || group_concat('[' || id || ',"' || value || '"]') || ']' FROM test WHERE id > 3;  

The key issue is whether SQLite’s behavior of returning empty JSON structures (instead of NULL) is intentional, documented, and reliable for future use. Developers relying on these functions must understand whether their applications can safely depend on this behavior or if it might change in future SQLite versions due to compatibility concerns with other SQL implementations (e.g., PostgreSQL’s json_agg(), which returns NULL for empty aggregates).

Possible Causes: Design Philosophy, Compatibility Trade-offs, and Type Safety

1. SQLite’s Design Philosophy for JSON Aggregates

SQLite’s JSON aggregate functions prioritize type consistency and usability over strict SQL tradition. When aggregating zero rows:

• json_group_array() returns [] to signify an empty list of JSON elements.
• json_group_object() returns {} to represent an empty key-value map.

This aligns with JSON’s native handling of empty collections. Returning NULL would force developers to handle type mismatches (e.g., expecting an array but receiving NULL). For example, parsing a NULL result as JSON would require additional checks, whereas an empty array/object can be processed directly.

2. Divergence from Traditional SQL Aggregate Behavior

Most SQL aggregates return NULL for empty input sets. For instance:

SELECT sum(a) FROM t1; -- NULL if t1 is empty  
SELECT group_concat(a) FROM t1; -- NULL if t1 is empty  

This tradition stems from mathematical set theory, where operations on empty sets are undefined. However, SQLite’s total() function (a variant of sum()) returns 0.0 instead of NULL for empty inputs, reflecting a pragmatic design choice. Similarly, JSON aggregates favor returning valid JSON structures to avoid forcing developers into unnecessary NULL checks.

3. Compatibility Concerns with Other Databases

PostgreSQL’s json_agg() returns NULL for empty inputs, creating a compatibility gap. SQLite’s current behavior risks confusing developers accustomed to PostgreSQL or other systems. However, changing SQLite’s behavior now could break existing applications that rely on empty JSON structures. This trade-off between consistency and backward compatibility is central to the debate.

4. Window Function Consistency

JSON aggregates in window functions exhibit the same behavior. For example:

CREATE TABLE t1(a, b);
INSERT INTO t1 VALUES(1, 'abc'), (2, 'def'), (3, 'ghi');
SELECT json_group_array(b) OVER (
  ORDER BY a ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING
) AS j FROM t1;  

The last row returns [], not NULL. This consistency ensures predictable results in analytical queries, where NULL could complicate result processing.

Troubleshooting Steps, Solutions & Fixes: Ensuring Compatibility and Handling Edge Cases

1. Validate Current Behavior Against Application Requirements

• Scenario: You need an empty JSON array when no rows match.

  • Solution: Rely on json_group_array()/json_group_object() as-is.
  • Example:

    SELECT json_group_array(value) FROM test WHERE id > 3; -- []  
    

• Scenario: You require NULL instead of an empty JSON structure.

  • Solution: Use CASE to convert empty results to NULL:

    SELECT CASE
      WHEN json_group_array(value) = '[]' THEN NULL
      ELSE json_group_array(value)
    END FROM test WHERE id > 3;  
    

2. Cross-Database Compatibility Workarounds

• Problem: Your application must behave identically in SQLite and PostgreSQL.
  • Solution: Normalize the output using COALESCE() or wrapper functions:

    -- Force NULL for empty aggregates  
    SELECT COALESCE(
      NULLIF(json_group_array(value), '[]'),
      NULL
    ) FROM test WHERE id > 3;  
    

3. Future-Proofing Against SQLite Updates

• Risk: SQLite might align JSON aggregates with traditional SQL behavior.
  • Mitigation: Use feature detection in application code. For example:

    -- Check the SQLite version and function behavior at runtime  
    SELECT sqlite_version();  
    -- If version >= 3.45.0 (hypothetical future release), adjust logic  
    

  • Documentation Monitoring: Track SQLite’s JSON1 documentation for changes to aggregate behavior.

4. Handling Mixed Aggregates in Complex Queries

• Problem: Combining JSON and traditional aggregates when some return NULL.
  • Example:

    SELECT
      json_group_array(value) AS json_data,
      sum(id) AS total_id  
    FROM test WHERE id > 3;  
    

    Here, json_data is [], and total_id is NULL.

  • Solution: Use COALESCE to harmonize outputs:

    SELECT
      COALESCE(json_group_array(value), '[]') AS json_data,
      COALESCE(sum(id), 0) AS total_id  
    FROM test WHERE id > 3;  
    

5. Edge Case: Empty Inputs in Subqueries or CTEs

• Problem: Subqueries returning empty sets may propagate unexpected JSON structures.
  • Example:

    WITH cte AS (SELECT * FROM test WHERE id > 3)
    SELECT json_group_array(value) FROM cte;  
    

    This returns [].

  • Solution: Explicitly check for empty results in outer queries:

    SELECT
      CASE WHEN EXISTS (SELECT 1 FROM cte)  
        THEN json_group_array(value)  
        ELSE NULL  
      END  
    FROM cte;  
    

6. Unit Testing Strategies

• Best Practice: Write tests covering empty input scenarios for JSON aggregates.
  • Example (Python):

    def test_empty_json_aggregate():
        cursor = db.execute("SELECT json_group_array(value) FROM test WHERE id > 3")
        result = cursor.fetchone()[0]
        assert result == '[]'  # Or adjust if behavior changes  
    

7. Migration Guidance for Legacy Systems

• Scenario: Migrating from PostgreSQL to SQLite.
  • Challenge: PostgreSQL’s json_agg() returns NULL for empty inputs.
  • Fix: Modify queries to use COALESCE:

    -- PostgreSQL  
    SELECT json_agg(value) FROM test WHERE id > 3; -- NULL  
    
    -- SQLite equivalent  
    SELECT COALESCE(
      json_group_array(value),
      'null'  -- Returns a JSON null  
    ) FROM test WHERE id > 3;  
    

8. Performance Considerations

• Myth: Returning NULL is faster than constructing empty JSON structures.
  • Reality: The performance difference is negligible. SQLite’s JSON functions are optimized to handle empty outputs efficiently.

By understanding SQLite’s design choices and applying targeted workarounds, developers can confidently use JSON aggregates while ensuring compatibility and robustness against future changes.