Model Nested JSON: When to Flatten, When to Keep
Three orders' shipping adds up to 9 pounds; after flattening the JSON into item rows it summed to 17.
Video #267 ยท Data modelling
Steps in the video
- 0:00 Shipping of 9 pounds became 17 after flattening.
- 0:30 The idea
- 1:06 Setup
- 1:31 The response
- 2:02 Keep, and flatten
- 2:32 Split into tables
- 2:57 Filling the tables
- 3:18 Querying the documents
- 3:38 Run the kept documents
- 4:05 Run the flat table
- 4:26 Querying the split tables
- 4:42 Run the split tables
- 5:08 What this does not show
- 5:26 Mistakes to avoid
- 6:04 My take and next steps
- 6:26 Recap
Files
- run.py (62 lines)
- shop.py (77 lines)
Tested with
Python 3.12.15
What this does not show
Three made-up orders with one level of nesting in SQLite; no speed or size is measured and schema changes over time are not shown.
Sources
Learning IT by Example.