Grouping Related Records into JSON Arrays with SQLite Queries

Understanding the Core Challenge of Aggregating Child Records in JSON Output

The central challenge in this scenario revolves around efficiently transforming a one-to-many relational database structure into nested JSON arrays while maintaining performance with large datasets. The database contains three tables with the following relationships:

1. Table a: Parent table containing titles (1:many relationship with b)
2. Table b: Child table containing URLs (many:1 relationship with a, 1:many relationship with c)
3. Table c: Grandchild table containing geospatial coordinates (many:1 relationship with a)

The desired output requires:

• One JSON object per b record
• Each object containing an array of all related c records
• Preservation of individual c_id values within location arrays
• Efficient handling of 800K+ records in a and million+ records in c

Critical Analysis of Query Structure and Performance Limitations

Fundamental Data Modeling Constraints

The primary obstacle stems from SQLite’s JSON capabilities operating at the row level rather than set level. When combining relational data with JSON aggregation, developers face three key challenges:

1. Nested Aggregation Limitations:

   • SQLite’s json_group_array() operates within a single grouping level
   • Multiple levels of aggregation require nested subqueries
   • Typical JOIN operations create Cartesian products that break aggregation

2. Indexing Requirements:

   • Without proper indexes on a_id columns, subqueries become full table scans
   • The current table structure lacks composite indexes for join optimization

3. Pagination Complexity:

   • Traditional LIMIT/OFFSET becomes impractical with large datasets
   • Offset-based pagination requires scanning all previous rows
   • Key-based pagination requires understanding of data distribution

Common Pitfalls in Initial Approaches

The original attempts demonstrate several conceptual misunderstandings:

1. Incorrect Grouping Level:

   • Grouping by c.id creates unnecessary fragmentation
   • Proper grouping should occur at the b.id level

2. JSON Function Misapplication:

   • Direct use of aggregated strings without json() conversion
   • Improper nesting of JSON objects within arrays

3. Suboptimal Join Strategies:

   • Cartesian product creation through multiple JOINs
   • Lack of correlation between main query and subqueries

Comprehensive Solution Strategy for Efficient JSON Aggregation

Step 1: Optimized Query Structure with Correlated Subqueries

Base Query Template:

WITH paginated_b AS (
  SELECT 
    b.id AS b_id,
    b.url,
    a.id AS a_id,
    a.a_title
  FROM b
  JOIN a ON b.a_id = a.id
  ORDER BY b.id
  LIMIT 30
)
SELECT json_group_array(
  json_object(
    'b_id', pb.b_id,
    'url', pb.url,
    'a_id', pb.a_id,
    'a_title', pb.a_title,
    'loc', (
      SELECT json_group_array(
        json_object(
          'c_id', c.id,
          'lat', c.lat,
          'lon', c.lon
        )
      )
      FROM c
      WHERE c.a_id = pb.a_id
    )
  )
)
FROM paginated_b pb;

Key Optimization Features:

• CTE for Pagination: Isolates the base b records first
• Correlated Subquery: Fetches locations per a_id without JOIN explosion
• Direct JSON Construction: Builds nested structures in single pass

Step 2: Index Optimization Strategy

Essential Indexes:

CREATE INDEX idx_b_a_id ON b(a_id);
CREATE INDEX idx_c_a_id ON c(a_id);
CREATE INDEX idx_b_pagination ON b(id, a_id);

Index Rationale:

1. idx_b_a_id: Enables fast lookups when joining b to a
2. idx_c_a_id: Optimizes location subqueries by a_id
3. idx_b_pagination: Supports both ordering and filtering in pagination

Step 3: Advanced Pagination Implementation

Offset-Free Pagination Technique:

WITH paginated_b AS (
  SELECT 
    b.id AS b_id,
    b.url,
    a.id AS a_id,
    a.a_title
  FROM b
  JOIN a ON b.a_id = a.id
  WHERE b.id > ?  -- Last seen b_id from previous page
  ORDER BY b.id
  LIMIT 30
)
-- Rest of query remains identical

Performance Benefits:

• Eliminates OFFSET scanning penalty
• Uses seek method with indexed b.id comparisons
• Consistent performance regardless of page depth

Step 4: Handling Sparse Locations

Modified Location Subquery:

SELECT COALESCE(
  json_group_array(
    json_object(
      'c_id', c.id,
      'lat', c.lat,
      'lon', c.lon
    )
  ),
  json_array()  -- Handle cases with zero locations
)
FROM c
WHERE c.a_id = pb.a_id

Key Features:

• COALESCE ensures valid JSON arrays even with missing locations
• Maintains consistent output structure across all records
• Prevents NULL values from breaking JSON structure

Step 5: Query Plan Analysis and Tuning

Execution Plan Examination:

EXPLAIN QUERY PLAN
WITH paginated_b AS (...)
SELECT ...;

Expected Optimal Plan:

• SCAN TABLE b USING INDEX idx_b_pagination
• SEARCH TABLE a USING INTEGER PRIMARY KEY (rowid=?)
• SEARCH TABLE c USING INDEX idx_c_a_id (a_id=?)

Tuning Considerations:

• Ensure all subqueries show USING INDEX
• Watch for TEMPORARY B-TREE in EXPLAIN OUTPUT (indicates missed indexes)
• Consider increasing cache size for large working sets: PRAGMA cache_size = -10000;

Step 6: Memory Management for Large Results

Configuration Tweaks:

PRAGMA mmap_size = 1073741824;  -- 1GB memory mapping
PRAGMA temp_store = MEMORY;     -- Keep temp structures in RAM
PRAGMA journal_mode = OFF;      -- For read-only queries

Application-Level Streaming:

• Process JSON results in chunks using Python’s ijson or similar
• Avoid loading entire JSON array into memory
• Use generator patterns for response serialization

Step 7: Alternative Materialized View Approach

For read-heavy workloads with infrequent data changes:

Materialized View Creation:

CREATE TABLE b_loc_cache (
  b_id INTEGER PRIMARY KEY,
  url TEXT,
  a_id INTEGER,
  a_title TEXT,
  loc_json TEXT
);

CREATE INDEX idx_b_loc_a ON b_loc_cache(a_id);

Refresh Strategy:

• Use triggers on underlying tables
• Periodic rebuilds during maintenance windows
• Query becomes simple SELECT loc_json FROM b_loc_cache

Step 8: Benchmarking and Validation

Performance Test Methodology:

1. Warm cache with PRAGMA schema.wal_checkpoint(TRUNCATE);
2. Time first-run vs cached execution
3. Monitor memory usage with sqlite3_status()
4. Verify JSON output validity using json_valid()

Expected Metrics:

• Sub-100ms response for 30-record pages with proper indexes
• Linear scalability up to 1M records
• Memory usage under 50MB for typical configurations

Step 9: Client-Side Processing Tradeoffs

When SQL-side JSON becomes prohibitive:

Alternative Approach:

SELECT 
  b.id AS b_id,
  b.url,
  a.id AS a_id,
  a.a_title,
  c.id AS c_id,
  c.lat,
  c.lon
FROM b
JOIN a ON b.a_id = a.id
LEFT JOIN c ON c.a_id = a.id
WHERE b.id > ?
ORDER BY b.id
LIMIT 30;

Client-Side Processing:

• Group records by b_id in application code
• Build JSON structure programmatically
• Benefits from simpler SQL but increases network payload

Step 10: Concurrency Considerations

Isolation Levels:

• Use BEGIN IMMEDIATE for write operations
• READ UNCOMMITTED acceptable for bulk reads
• Enable WAL mode for concurrent access: PRAGMA journal_mode=WAL;

Connection Pooling:

• Reuse prepared statements
• Set appropriate busy timeouts
• Monitor for lock contention during heavy writes

Final Implementation Checklist

1. Index Verification:

   • Confirm all a_id columns are indexed
   • Validate composite index on b(id, a_id)

2. Query Validation:

   • Test with empty c records
   • Verify pagination boundary conditions
   • Check JSON output validity

3. Performance Profiling:

   • Run EXPLAIN QUERY PLAN
   • Monitor memory usage during large exports
   • Test with production-scale data samples

4. Application Integration:

   • Implement error handling for JSON parsing
   • Add timeout controls for long-running queries
   • Consider connection pool sizing

This comprehensive approach addresses both the immediate JSON formatting requirements and the broader performance considerations inherent in large-scale SQLite deployments. The solution balances SQL capabilities with application-level optimizations, providing a robust foundation for high-performance JSON aggregation in resource-constrained environments.