#267 Data modelling 6.9 min
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.
Predict the three models
Copy the code folder first. Read RESPONSE in shop.py, then predict the revenue, the shipping total over flat rows and the number of rows to edit if Asha moves. Run python run.py keep, python run.py flat and python run.py split to compare.
A hint
Asha appears in two orders, and she has 5 item rows in the flat table.
Fix the typo path
In run.py, in the keep function, correct the path $.custmer.name to $.customer.name. Predict what the line now prints, then run python run.py keep. When I ran it, the line printed the name Asha instead of None. The label in front of it still says typo path because it is only text.
A hint
The query returns the first row it finds.
Give order A2 a note
In shop.py, change the extras of order A2 from {} to {"note": "ring bell"}. Predict what the A2 note line prints, then run python run.py keep. When I ran it, the A2 note was ring bell and the other lines stayed the same.
A hint
Extras can hold different keys in every order.
Raise a price and a shipping charge
In shop.py, change the PEN price from 2 to 3 and run python run.py flat and python run.py split. Predict the new revenue and Asha's spend. Then put the price back and change order A2's shipping from 3 to 6, and run python run.py flat. When I ran it, revenue was 48 and Asha spent 38, and with the shipping change the sum over rows was 20 against 12 once per order.
A hint
PEN appears in orders A1 and A3, both Asha's.
These tasks are for you to try on your own machine. Nothing is checked or saved.
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.
Up next in Data modelling
More in Data modelling
- #110 Slowly Changing Dimensions in SQL: Type 1 vs Type 2
- #130 Define a Metric Once: Views and a Metrics Layer
- #250 Data Modelling Basics: Entities, Keys, Relationships
- #252 Normalisation 1NF to 3NF on a Messy Spreadsheet
- #253 Primary Keys: Natural, Surrogate and Hash Keys
- #254 Constraints That Catch Bad Data: PK, FK, CHECK