Optimizing JSON Queries for Nested Sensor Data in SQLite

Issue Overview: Extracting Specific Sensor Data from Nested JSON Structures

The core challenge revolves around efficiently querying nested JSON structures stored in SQLite when dealing with sensor data. The user’s database schema stores sensor readings in a JSON column (DATA), with a hierarchical structure where IP addresses (e.g., IP90, IP15) act as top-level keys. Each IP key contains an array of sensor readings, including fields like humidity, temperature, location, and timestamp. The goal is to retrieve humidity values and timestamps filtered by specific locations (e.g., "Bedroom 4 ( Cupboard )") without executing separate queries for each location.

Key Observations from the Schema and Data

1. JSON Structure Complexity:
   The DATA column contains an array of JSON objects. Each object has a single key representing an IP address (e.g., "IP15"), which maps to an array of sensor readings. This creates a three-level hierarchy:

   • Level 1: Array of IP address objects ($.DATA[*])
   • Level 2: IP address keys ($.DATA[*].IP15, $.DATA[*].IP90, etc.)
   • Level 3: Sensor reading arrays under each IP address ($.DATA[*].IP15[0], $.DATA[*].IP90[1], etc.)

   Example snippet:

   {
     "DATA": [
       { "IP15": [ { "humidity": 87, "location": "Garage", ... } ] },
       { "IP90": [ 
         { "humidity": 45.6, "location": "Small Loft", ... },
         { "humidity": 45.6, "location": "Bedroom 4 ( Cupboard )", ... }
       ] }
     ]
   }
   

2. Current Query Workflow:
   The user’s existing query uses json_tree() to flatten the JSON structure and json_extract() to filter records by location:

   SELECT 
     json_extract(value, '$.humidity') AS HUMIDITY,
     json_extract(value, '$.iso') AS time
   FROM 
     DATABASE, 
     json_tree(Database.DATA, '$.IP90') 
   WHERE 
     type = 'object' 
     AND json_extract(value, '$.location') = "Bedroom 4 ( Cupboard )";
   

   This approach works but raises concerns about performance, readability, and scalability.

3. Performance Implications:

   • Full JSON Traversal: The json_tree(Database.DATA, '$.IP90') clause limits traversal to the IP90 array. However, json_tree() recursively parses the entire subtree under $.IP90, which is redundant if only leaf nodes (sensor readings) are needed.
   • Runtime Extraction Overhead: Repeated json_extract() calls on the value column force SQLite to parse JSON strings at runtime, bypassing indexing opportunities.
   • Hardcoded IP Addresses: The query explicitly references $.IP90, requiring manual adjustments for other IP addresses (e.g., IP15), which complicates maintenance.

Possible Causes of Inefficiency and Design Limitations

1. Suboptimal JSON Schema Design

Storing sensor data under IP address keys creates unnecessary fragmentation. Each IP address is a separate object in the DATA array, forcing queries to navigate multiple levels of nesting. A more efficient schema would:

• Use a single array for all sensor readings.
• Include the IP address as a field within each sensor reading object.

Example of Improved Schema:

{
  "DATA": [
    {
      "ip": "IP15",
      "humidity": 87,
      "location": "Garage",
      ...
    },
    {
      "ip": "IP90",
      "humidity": 45.6,
      "location": "Bedroom 4 ( Cupboard )",
      ...
    }
  ]
}

This simplifies queries by reducing nesting levels and enabling unified filtering on ip and location.

2. Misuse of JSON Functions

The original query uses json_tree() with a path argument ($.IP90) that prematurely narrows the traversal scope. This leads to two issues:

• Redundant Traversal: json_tree() explores all child nodes under $.IP90, including keys like humidity, location, etc., even though the WHERE type = 'object' clause later discards non-object nodes.
• Fragility: Hardcoding $.IP90 requires query modification for different IP addresses. A dynamic approach that searches across all IPs is preferable.

A better strategy is to traverse the entire DATA array and filter records using SQL conditions instead of hardcoded JSON paths.

3. Lack of Indexing on JSON Data

SQLite’s JSON functions do not leverage traditional indexes unless virtual columns or generated columns are used. Without indexing, every query triggers a full scan of the JSON structure, which becomes prohibitively slow as the dataset grows (e.g., 3,000+ entries).

4. Overreliance on JSON for Structured Data

While SQLite’s JSON support is powerful, it is not ideal for frequently queried fields like location or ip. These fields should be stored in regular columns to enable indexing and type enforcement. Hybrid approaches (e.g., storing metadata in columns and variable fields in JSON) often yield better performance.

Troubleshooting Steps, Solutions & Fixes

Step 1: Schema Refactoring for Simplified Queries

Action: Flatten the JSON structure to reduce nesting and include the IP address as a queryable field.

Migration Script:

-- Create a new table with hybrid storage (columns + JSON)
CREATE TABLE sensor_readings (
  ip TEXT NOT NULL,
  location TEXT NOT NULL,
  timestamp INTEGER NOT NULL,
  data JSON CHECK(json_valid(data))
);

-- Migrate existing JSON data into the new schema
INSERT INTO sensor_readings (ip, location, timestamp, data)
SELECT 
  json_extract(ip_obj.value, '$.' || json_each.key) AS ip,
  json_extract(sensor.value, '$.location') AS location,
  json_extract(sensor.value, '$.timestamp') AS timestamp,
  sensor.value AS data
FROM 
  DATABASE, 
  json_tree(DATABASE.DATA) AS ip_obj,
  json_tree(ip_obj.value) AS sensor
WHERE 
  ip_obj.type = 'object' 
  AND sensor.type = 'object'
  AND sensor.key IS NULL;  -- Filter leaf objects (sensor readings)

Benefits:

• The ip and location fields are now standalone columns, enabling direct filtering without JSON functions.
• The data column retains the original JSON for flexibility.
• Queries become simpler and faster:

  SELECT 
    json_extract(data, '$.humidity') AS humidity,
    json_extract(data, '$.iso') AS time
  FROM 
    sensor_readings
  WHERE 
    location = 'Bedroom 4 ( Cupboard )';
  

Step 2: Optimize JSON Traversal with Targeted Paths

Action: Replace json_tree() with json_each() to avoid recursive parsing when only leaf nodes are needed.

Original Query Revised:

SELECT 
  json_extract(sensor.value, '$.humidity') AS humidity,
  json_extract(sensor.value, '$.iso') AS time
FROM 
  DATABASE, 
  json_tree(DATABASE.DATA) AS ip_obj,
  json_tree(ip_obj.value) AS sensor
WHERE 
  ip_obj.type = 'object' 
  AND sensor.type = 'object'
  AND json_extract(sensor.value, '$.location') = 'Bedroom 4 ( Cupboard )';

Explanation:

• The first json_tree(DATABASE.DATA) iterates over the DATA array’s objects (e.g., IP15, IP90).
• The second json_tree(ip_obj.value) iterates over the sensor arrays under each IP.
• Filtering sensor.type = 'object' isolates sensor reading objects.

Performance Gain: This avoids parsing every JSON node (keys, values, arrays) and focuses only on sensor reading objects.

Step 3: Implement Indexing via Generated Columns

Action: Create virtual columns for frequently queried fields to enable indexing.

Schema Modification:

ALTER TABLE DATABASE ADD COLUMN location TEXT GENERATED ALWAYS AS (
  json_extract(data, '$.location')
);

CREATE INDEX idx_location ON DATABASE(location);

Query Using Index:

SELECT 
  json_extract(data, '$.humidity') AS humidity,
  json_extract(data, '$.iso') AS time
FROM 
  DATABASE
WHERE 
  location = 'Bedroom 4 ( Cupboard )';

Benefits:

• The generated column location is indexed, allowing SQLite to perform indexed lookups instead of full JSON scans.
• Queries are simplified by replacing json_extract() with direct column references.

Step 4: Use JSONB for Binary JSON Storage (SQLite 3.45+)

Action: Leverage SQLite’s JSONB type for faster parsing and storage efficiency.

Schema Modification:

CREATE TABLE sensor_data (
  id INTEGER PRIMARY KEY,
  data JSONB  -- Requires SQLite 3.45 or later
);

Performance Impact:

• JSONB stores JSON in a binary format, reducing parsing overhead.
• Queries using json_extract() on JSONB columns execute 20-30% faster than with text JSON.

Step 5: Transition to a Hybrid Data Model

Action: Store static metadata in columns and dynamic fields in JSON.

Example Schema:

CREATE TABLE sensors (
  id INTEGER PRIMARY KEY,
  ip TEXT NOT NULL,
  location TEXT NOT NULL,
  last_updated INTEGER NOT NULL,
  readings JSON  -- Stores array of {humidity, temperature, iso, timestamp}
);

-- Query for humidity in a specific location
SELECT 
  json_extract(readings.value, '$.humidity') AS humidity,
  json_extract(readings.value, '$.iso') AS time
FROM 
  sensors, 
  json_tree(sensors.readings) AS readings
WHERE 
  sensors.location = 'Bedroom 4 ( Cupboard )'
  AND readings.type = 'object';

Advantages:

• Static fields (ip, location) are indexed and queryable without JSON functions.
• Dynamic sensor readings remain flexible in JSON.

Step 6: Benchmark and Compare Approaches

Action: Measure query performance across different schema designs using SQLite’s EXPLAIN QUERY PLAN and runtime profiling.

Example Benchmark:

-- Original Schema
EXPLAIN QUERY PLAN
SELECT ... [original query];

-- Hybrid Schema
EXPLAIN QUERY PLAN
SELECT ... [hybrid schema query];

-- Run-time profiling (using CLI)
.timer on
SELECT ... [original query];
SELECT ... [hybrid schema query];

Expected Outcomes:

• Hybrid schemas with indexed columns show SCAN TABLE or SEARCH TABLE USING INDEX in query plans.
• Pure JSON schemas show SCAN SUBQUERY or SCAN JSON TREE, which are slower.

Final Recommendation: When to Avoid JSON in SQLite

While JSON support in SQLite is robust, it is not optimal for:

• High-frequency queries on large datasets (>10,000 records).
• Fields used in WHERE, JOIN, or ORDER BY clauses.
• Applications requiring ACID compliance on nested data.

For such cases, consider:

• Relational Design: Normalize data into tables.
• External Tools: Use jq for JSON processing in scripts or Node.js with better-sqlite3 for hybrid workflows.

By systematically addressing schema design, JSON function usage, and indexing, users can achieve sub-100ms query times even for datasets with 3,000+ entries. The key is balancing JSON’s flexibility with relational principles for critical fields.