Module 18
Nested data and JSON
Expand records, lists and nested tables; flatten JSON.
You'll be able to
- Expand record columns
- Expand list columns
- Flatten nested JSON
Lessons
- 1.Flatten nested JSONExpand records and lists in the right order to flatten JSON.
- 2.Expand record columns safely when fields varyExpand records with optional fields without losing new fields or failing on null.
- 3.List columns: expand to rows or extract valuesPick the right treatment for a column of lists for the table you need.
- 4.Reference other queries: staging and reuseStructure a workbook as staging queries plus output queries that reference them.
Take the nested data and json quiz (8 questions)
Exercises
- advanced~14 minPro
Flatten nested JSON orders into one row per line (webshop)
Parse the JSON, expand the customer object and the list of order lines into a flat table.
- advanced~14 minPro
Flatten nested JSON orders into one row per line (marketplace)
Parse the JSON, expand the customer object and the list of order lines into a flat table.
- intermediate~8 minPro
Convert JSON records that don't all have the same fields (crm)
Some contacts have no email field and some no phone field at all. Build a table with all four columns.
- intermediate~8 minPro
Convert JSON records that don't all have the same fields (volunteers)
Some contacts have no email field and some no phone field at all. Build a table with all four columns.
- intermediate~9 minPro
Flatten a JSON object keyed by product code (garden)
The price feed is one object whose keys are SKUs and whose values are {name, price} objects. Make it a table.
- intermediate~9 minPro
Flatten a JSON object keyed by product code (electronics)
The price feed is one object whose keys are SKUs and whose values are {name, price} objects. Make it a table.
- intermediate~9 minPro
Summarise each order's lines without flattening them (webshop)
For every JSON order, count its lines and total its units directly from the nested list, keeping one row per order.
- intermediate~9 minPro
Summarise each order's lines without flattening them (marketplace)
For every JSON order, count its lines and total its units directly from the nested list, keeping one row per order.
- intermediate~8 minPro
Expand a nested record whose field names clash with existing columns (webshop)
Orders have an id, and the nested customer object also has an id. Expand the customer without mixing them up.
- intermediate~8 minPro
Expand a nested record whose field names clash with existing columns (marketplace)
Orders have an id, and the nested customer object also has an id. Expand the customer without mixing them up.
- advanced~13 minPro
Turn a list of name/value attributes into columns (apparel)
Each product has an attributes list like [{name: colour, value: red}, …]. Make one column per attribute name.
- advanced~13 minPro
Turn a list of name/value attributes into columns (furniture)
Each product has an attributes list like [{name: colour, value: red}, …]. Make one column per attribute name.
- intermediate~7 minPro
Build a table from a JSON columns + rows payload (analytics)
A reporting API returns {columns: [...], rows: [[...], ...]}. Turn it into a table with those column names.
- intermediate~7 minPro
Build a table from a JSON columns + rows payload (telemetry)
A reporting API returns {columns: [...], rows: [[...], ...]}. Turn it into a table with those column names.
- advanced~10 minPro
Keep orders whose lines include a gift item (webshop)
Without flattening, keep the JSON orders where any line's sku starts with GIFT, with the customer name.
- advanced~10 minPro
Keep orders whose lines include a gift item (marketplace)
Without flattening, keep the JSON orders where any line's sku starts with GIFT, with the customer name.
- intermediate~8 minPro
Predict how many rows each order becomes when lines are expanded (webshop)
Before running Expand to New Rows on lines, state how many rows each order will turn into.
- intermediate~8 minPro
Predict how many rows each order becomes when lines are expanded (marketplace)
Before running Expand to New Rows on lines, state how many rows each order will turn into.