learning-it by example

Back to the video

JSON in SQL: Query Semi-Structured Data in SQLite

In SQLite, a total stored as the text 9.50 passed a filter for totals over 20; a cast fixed it.

Video #128 ยท SQL and analytics

Steps in the video

  1. 0:00 Total over twenty? SQLite said yes to nine fifty
  2. 0:28 The idea
  3. 0:49 Setup
  4. 1:22 Five made-up events
  5. 1:50 One broken row, kept aside
  6. 2:08 Fields, and a filter
  7. 2:31 The run
  8. 3:34 Lists into rows
  9. 5:00 What this does not show
  10. 5:32 Mistakes to avoid
  11. 6:04 My take and next steps
  12. 6:26 Recap

Files

  • events.py (23 lines)
  • queries.py (27 lines)
  • run.py (27 lines)

Download the code (zip)

Tested with

Python 3.12.15

What this does not show

Five made-up events checked only in SQLite, with no indexes or timings, and no JSONB or arrow operators.

Sources

Learning IT by Example.