learning-it by example

#128 SQL and analytics 6.9 min

JSON in SQL: Query Semi-Structured Data in SQLite

In SQLite, a total stored as the text 9.50 passed a filter for totals over 20; a cast fixed it.

1. In SQLite, what does json_extract return when the key you ask for does not exist in the JSON, such as the total of Chen's signup event?
2. A SQLite JSON query asked for totals over 20 and returned 3 rows, including one whose total was 9.50. Why did 9.50 pass?
3. How does the video fix the JSON total filter so that only the orders with totals of 24.5 and 31 are left?
4. What does json_each do when used in the FROM clause on a list of items?
5. A sixth event is cut off halfway and is not valid JSON. What happened to the fields query, and how did the video find the bad row?

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

  1. Run the four groups

    Copy the code folder first. Run python run.py fields, python run.py types, python run.py items and python run.py broken. In the types output, write down which row was text and which two rows passed the filter once the cast was used.

    A hint

    The json_type group lists the kind of value each total has.

  2. Remove the quotes around the total

    In events.py, find Ben's event, where the total is written as "9.50" in quotes. Take the quotes off so it reads 9.50. Predict what python run.py types prints for the first group and for the json_type list. On this machine the first group has only rows 1 and 4, and the type for row 2 is real.

    A hint

    Without quotes the value is a JSON number, not a JSON string.

  3. Lower the limit and compare

    In queries.py, change the 20 to 10 in both BIG_WRONG and BIG_FIXED, then run python run.py types. Predict which rows each version returns. On this machine the uncast version returns rows 1, 2, 4 and 5, and the cast version returns rows 1, 4 and 5.

    A hint

    Row 5 has a total of 12.0. Row 2 is still text.

  4. Change a quantity

    In events.py, find Dina's event with a MUG quantity of 2 and change the 2 to 5. Predict the per sku totals from python run.py items before you run it. On this machine MUG shows 6, POT shows 1 and TEA shows 6.

    A hint

    MUG appears in two orders. Add the quantities from both.

These tasks are for you to try on your own machine. Nothing is checked or saved.

What this does not show

Five made-up events checked only in SQLite, with no indexes or timings, and no JSONB or arrow operators.

Up next in SQL and analytics

More in SQL and analytics

All SQL and analytics videos

Related videos