Deleting JSON-Encoded Rows by Month and Device in SQLite

Understanding JSON Date Filtering and Row Deletion Constraints

Core Challenge: Selective Deletion in JSON-Structured Data

The central problem revolves around deleting specific rows from an SQLite table where date information is stored as JSON-encoded strings. The Energy table contains two columns: DEVICE (text) and DATA (a JSON blob with an ISO 8601 timestamp under the iso key). The goal is twofold:

1. Delete all rows from a specific month (e.g., September 2022).
2. Delete a subset of rows (e.g., two arbitrary rows) for a specific device (e.g., IP22) within that month.

This requires precise extraction of date components from JSON, correct filtering logic, and efficient use of SQLite’s deletion mechanics. Missteps in any of these areas lead to incomplete deletions, unintended data loss, or performance bottlenecks.

Common Pitfalls in Date Extraction and Row Selection

1. Incorrect JSON Path Syntax

The JSON iso field is nested within the DATA column. Using improper JSON path operators (->> vs. ->) or mismatched quotes can cause extraction failures. For example:

-- Wrong: Missing quotes around JSON path
json_extract(DATA, $.iso) 
-- Correct: Valid path syntax
json_extract(DATA, '$.iso')

2. Date Formatting Ambiguities

ISO 8601 timestamps like 2022-09-06T08:30:46.921Z require careful parsing. Attempting to use strftime('%Y-%m', ...) directly on the raw string will fail because strftime expects a valid datetime format. The timezone suffix (Z) and fractional seconds are not natively parsed by SQLite’s date functions unless explicitly handled.

3. Subquery Misuse in DELETE Operations

Using rowid = (SELECT ... LIMIT 2) instead of rowid IN (SELECT ... LIMIT 2) is a critical error. The = operator expects a single value, while IN accepts multiple values. This mistake results in syntax errors or partial deletions when more than one row matches the criteria.

4. Unoptimized Query Plans

Failing to structure the WHERE clause efficiently—for example, placing device filters in the outer query instead of the subquery—forces SQLite to scan the entire table. Without indexes on DEVICE or virtual columns for extracted dates, performance degrades exponentially with table size.

5. Implicit Row Ordering with LIMIT

Using LIMIT 2 without an ORDER BY clause introduces non-determinism. SQLite may return any two rows matching the criteria, leading to unpredictable deletions. This is especially problematic in applications requiring auditability.

Structured Solutions for Precise and Efficient Deletion

Step 1: Validate JSON Extraction and Date Parsing

Before writing deletion queries, verify that JSON extraction and date formatting work as intended. Use a diagnostic SELECT statement:

SELECT 
  DEVICE,
  json_extract(DATA, '$.iso') AS iso,
  strftime('%Y-%m', json_extract(DATA, '$.iso')) AS month
FROM Energy;

This reveals whether the iso field is correctly parsed and whether strftime can process the timestamp. If strftime returns NULL, preprocess the string to remove non-standard elements:

-- Remove the 'Z' timezone and fractional seconds
strftime('%Y-%m', 
  substr(json_extract(DATA, '$.iso'), 1, 19)
)

Step 2: Delete All Rows for a Specific Month

To delete all September 2022 rows regardless of device:

DELETE FROM Energy
WHERE strftime('%Y-%m', 
  substr(json_extract(DATA, '$.iso'), 1, 19)
) = '2022-09';

Optimization: Add a generated column to persist the month value, reducing runtime computation:

ALTER TABLE Energy ADD COLUMN month TEXT 
GENERATED ALWAYS AS (
  strftime('%Y-%m', substr(json_extract(DATA, '$.iso'), 1, 19))
);
CREATE INDEX idx_energy_month ON Energy(month);
DELETE FROM Energy WHERE month = '2022-09';

Step 3: Delete Rows for a Specific Device and Month

To target IP22 devices in September 2022:

DELETE FROM Energy
WHERE DEVICE = 'IP22'
AND strftime('%Y-%m', 
  substr(json_extract(DATA, '$.iso'), 1, 19)
) = '2022-09';

Performance Note: If the table has an index on DEVICE, SQLite will use it to narrow down rows before applying the date filter. Without an index, consider a covering index:

CREATE INDEX idx_energy_device_month ON Energy(DEVICE, month);

Step 4: Delete a Limited Number of Rows

To delete exactly two IP22 rows from September 2022:

DELETE FROM Energy
WHERE rowid IN (
  SELECT rowid
  FROM Energy
  WHERE DEVICE = 'IP22'
  AND strftime('%Y-%m', 
    substr(json_extract(DATA, '$.iso'), 1, 19)
  ) = '2022-09'
  LIMIT 2
);

Critical Considerations:

• Always use rowid IN (SELECT ...) instead of rowid = (SELECT ...) for multi-row deletion.
• Add ORDER BY to ensure deterministic row selection (e.g., delete oldest entries first):

  ORDER BY json_extract(DATA, '$.iso') ASC
  

Step 5: Benchmark and Optimize Queries

Use EXPLAIN QUERY PLAN to analyze efficiency:

EXPLAIN QUERY PLAN
DELETE FROM Energy WHERE ...;

Look for:

• SCAN TABLE Energy: Indicates a full table scan; add indexes.
• USE TEMP B-TREE FOR ORDER BY: Suggests sorting overhead; pre-sort data in subqueries.

Step 6: Handle Edge Cases and Data Integrity

• Time Zones: If timestamps include time zones (e.g., +00:00 instead of Z), adjust the substr bounds:

  substr(json_extract(DATA, '$.iso'), 1, 23)
  

• Fractional Seconds: Truncate or round if necessary:

  strftime('%Y-%m', 
    substr(json_extract(DATA, '$.iso'), 1, 19)
  )
  

• Transaction Safety: Wrap deletions in transactions to avoid partial commits:

  BEGIN TRANSACTION;
  DELETE FROM Energy WHERE ...;
  COMMIT;
  

Step 7: Alternative Approaches for Complex Scenarios

For systems without JSON1 extension (rare in modern SQLite), use INSTR and SUBSTR:

DELETE FROM Energy
WHERE DEVICE = 'IP22'
AND SUBSTR(DATA, INSTR(DATA, '"iso":"') + 7, 7) = '2022-09';

This manually extracts the date substring from the JSON blob. While fragile, it works if JSON structure is consistent.

Summary of Key Fixes and Best Practices

1. Precompute Frequently Queried Values: Use generated columns and indexes for JSON-extracted dates.
2. Leverage Deterministic Ordering: Always pair LIMIT with ORDER BY in deletion subqueries.
3. Validate Extraction Logic: Test JSON path queries and date parsing independently before deletion.
4. Monitor Query Plans: Regularly analyze performance with EXPLAIN QUERY PLAN and adjust indexes.
5. Isolate Deletion Criteria: Place device and date filters in the subquery to minimize scanned rows.

By systematically addressing JSON parsing, query structure, and performance considerations, users can achieve reliable and efficient row deletions in SQLite JSON datasets.