learning-it by example

Back to the video

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

  1. 0:00 Shipping of 9 pounds became 17 after flattening.
  2. 0:30 The idea
  3. 1:06 Setup
  4. 1:31 The response
  5. 2:02 Keep, and flatten
  6. 2:32 Split into tables
  7. 2:57 Filling the tables
  8. 3:18 Querying the documents
  9. 3:38 Run the kept documents
  10. 4:05 Run the flat table
  11. 4:26 Querying the split tables
  12. 4:42 Run the split tables
  13. 5:08 What this does not show
  14. 5:26 Mistakes to avoid
  15. 6:04 My take and next steps
  16. 6:26 Recap

Files

  • run.py (62 lines)
  • shop.py (77 lines)

Download the code (zip)

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.