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