learning-it by example

#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.

1. Three shop orders had shipping charges adding up to 9 pounds. After the JSON was flattened to one row per item and the shipping column summed, what total came out, and why?
2. When a shop's nested order JSON is split into SQLite tables, which part stays as JSON and why?
3. When made-up order JSON documents were kept whole in one SQLite text column, what happened when a query used the mistyped path $.custmer.name?
4. In SQLite, what happened when an order whose item had a quantity of 0 was stored as a whole JSON document, and when that item was added to the split tables?
5. When storing nested JSON in a SQL database, what rule of thumb decides what to split into tables and what to keep as JSON?

Practice only: nothing is saved and no score counts towards anything. Each answer comes with the reason.

  1. 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.

  2. 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.

  3. 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.

  4. 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

All Data modelling videos

Related videos