Complex Data: Beginner

A JSON column is a text column with structure inside it. The text follows the JSON format: an object is a set of key-value pairs inside curly braces, a list is a sequence of values inside square brackets, and each value is a string in double quotes, a number, true, false, or null. The order_meta table below holds a single order per row, and everything the app recorded about that order sits in the payload column.

Teams store events this way because the shape keeps changing. When the app adds a scheduled_for field next month, nobody alters the table; the new key appears inside payload on new rows and old rows carry on without it. Order 5004 already shows the pattern: its payload has no contactless key, because that order was placed before the option existed.

JSON_EXTRACT(column, path) reads a single value out of the JSON text. The path is a string that starts with $, which stands for the whole document, and .key steps into one key. So '$.plan' means the value stored under plan at the top level, and '$.tip' means the value under tip. Play the query below and watch each path land on its value in every row.

Row by row, the path $.plan finds dashpass, standard, dashpass, standard, and $.tip finds 3, 0, 5, 2. The plan values come out wrapped in double quotes while the tips do not. That difference is the central fact about this function: it returns a value of type JSON, a fragment of JSON text, so a JSON string keeps the quotes it had in the document and a JSON number stays bare.

Add a third extraction for the contactless key and run the query. Order 5004 has no such key, and the result for that row shows how the function handles absence.

Order 5004 returns NULL for contactless because its payload lacks the key. The function raises no error for a missing key; it reports NULL and moves on, which is what you want when older events lack newer fields, and what you must remember when a typo in the path quietly returns NULL for every row.

The engine checks the form of every path when the query runs. A path opens with $, puts a dot before each key, and ends on a key. 'plan' has no $, '$plan' has no dot, and '$.plan.' ends on a dot with nothing after it, so each one stops the query with an invalid-path error. Form is all it checks. Keys match letter for letter, including case, so '$.Plan' is well formed, runs, and comes back NULL for all 4 orders, because every document spells the key plan.

Production payloads nest. The order_events table carries the complete order: a customer object with its own keys, an items list where every element is an object, a tags list of strings, and the tip.

A path can step through several keys. '$.customer.plan' reads the top-level customer object, then the plan key inside it. Each dot is one step down, and the result is whatever sits at the end of the path: a string here, but a number, a list or another object are all possible.

Square brackets step into a list by position, and JSON paths count positions from 0. '$.items[0]' is the first element of the list and '$.items[1]' the second. Positions and keys chain: '$.items[0].name' reads the first item, then its name. To count the elements rather than read one, wrap the extracted list in JSON_ARRAY_LENGTH.

Order 5003 has 3 items and its first is Margherita. Order 5004 has an empty items list: the length is 0, and $.items[0].name returns NULL because there is no first item to step into. JSON_ARRAY_LENGTH returns a BIGINT rather than a JSON value, so item_count is ready for SUM, AVG or a HAVING filter with no conversion.

A path does not have to end on a scalar. Stop it on an object or a list and JSON_EXTRACT hands back that entire branch as JSON text. The second item of each order, '$.items[1]', comes back as a complete object with its braces and quotes, or as NULL where the order has fewer than 2 items.

Branch extraction is how you hand a sub-document to a later step. The tags value here is still JSON, a list drawn with its brackets and quoted strings; the CAST for Complex Types section turns it into a SQL array you can search with CONTAINS.

Three different mistakes produce the same quiet NULL: a key that does not exist, a position past the end of a list, and a typo in the path. '$.customer.email' returns NULL on every row because no order stores an email. '$.items[5]' returns NULL because no order has 6 items. '$.custmer.plan' returns NULL on every row as well, and nothing warns you. When a new extraction returns NULL everywhere, read the path letter by letter against one real payload before you blame the data.

Reading a path left to right, one step at a time, is the surest way to predict what it returns. Here is '$.items[1].name' traced through order 5003, whose list holds Margherita, Garlic Knots and Lemonade.

Each step needs something to land on. Run the same path on order 5002, whose list holds only the Burrito, and step 3 finds no second object, so the whole path returns NULL. Swap .name for .qty at step 4 and order 5003 returns the bare number 1.

An array is a SQL value that holds several elements of one type, in a fixed order, under a single column. Its type names the element type: ARRAY(VARCHAR) holds strings and ARRAY(INTEGER) holds whole numbers. Order is part of the value, so an array of Mission then SoMa differs from an array of SoMa then Mission. A delivery route is the natural case: the same 3 neighborhoods in a different order is a different drive.

You can write an array directly with the ARRAY[...] constructor, as in ARRAY['Mission', 'SoMa', 'Marina']. Most arrays in a warehouse come from data rather than literals, and the most common source is delimited text. SPLIT(text, delimiter) cuts a string at each delimiter and returns the pieces as an ARRAY(VARCHAR), in the order they appeared.

The stops column stores each batch's route as neighborhoods joined by commas. That is a string, so LENGTH(stops) counts characters and says nothing about how many stops a route has. Splitting it on the comma gives an array with one element per stop, in driving order.

Batch 701 splits into 3 elements and the first is Mission. Batch 704 has no comma, so SPLIT returns an array of 1 element holding Nob Hill. The brackets after the array pick one position, and SQL arrays count positions from 1, so [1] is the first stop. A JSON path counts from 0, so the same bracket means a different thing depending on whether you are inside a path string or next to a SQL array.

Asking for a position the array does not have raises an error that stops the whole query: SPLIT(stops, ',')[3] fails on batch 702, which has 2 stops. ELEMENT_AT(array, position) does the same lookup but returns NULL when the position is out of range, which makes it the right choice when routes differ in length. For the last element, hand ELEMENT_AT the array's own size: CARDINALITY(array) counts the elements, so ELEMENT_AT(array, CARDINALITY(array)) lands on the last one whatever the length.

Batches 702, 704 and 705 have no third stop, and ELEMENT_AT reports NULL for each instead of halting the query. The last_stop column is right for every row regardless of length, because each row's position comes from its own element count: 3 for batch 701, 1 for batch 704.

CARDINALITY(array) returns the number of elements as a BIGINT, and CONTAINS(array, value) returns true when any element equals the value. Both return ordinary SQL types, so they slot into WHERE, GROUP BY and HAVING without any conversion.

Two of the 5 batches run 2 stops, and the others run 1, 3 and 4. A WHERE clause can lean on CONTAINS the same way: WHERE CONTAINS(SPLIT(stops, ','), 'Marina') keeps batches 701, 703 and 705, the 3 routes that pass through the Marina, and drops the other 2.

Two habits carried over from strings and JSON paths trip people up here. LENGTH counts characters in a string, and CARDINALITY counts elements in an array, so on a route string they answer different questions. A SQL array also has no position 0: the first element is [1], and [0] stops the query with an error saying SQL array indices start at 1. Batch 703 shows both.

The 29 counts every letter and all 3 commas in Sunset,Richmond,Haight,Marina; the 4 is the number of stops Amara drives.

A map holds pairs: each key appears once and points to one value. Its type names both halves, MAP(VARCHAR, INTEGER) for string keys with whole-number values. Where an array answers the question of what sits at position 3, a map answers what is stored under the key sauce. Keys carry the meaning, so you look things up by name and never count positions.

The order_extras table records how many of each extra a customer requested, 1 column per extra. The restaurant team is about to add chopsticks, straws and extra ice to the list. Adding a column for every new extra means altering the table and every query on it each time. Packing the counts into a map keyed by the extra's name gives one extras value per order that grows by gaining keys.

MAP(keys, values) builds a map from 2 arrays of the same length: the first supplies the keys, the second the values, paired up by position. Here the keys array is a literal and the values array is built from the row's columns, so each row gets a map of its own counts.

The result prints each pair as key=value inside braces. Every row now holds one value of type MAP(VARCHAR, INTEGER), and a fourth extra would be a fourth key inside that value, with no change to the column list.

Square brackets after a map take a key instead of a position. extras['sauce'] returns the value stored under sauce. As with arrays, the bracket form raises an error if the key is absent, and ELEMENT_AT(map, key) returns NULL instead. Run both against a key that exists and one that does not.

Order 5002 requested 0 sauce packets, and 0 is a stored value, so the lookup returns 0. No order has a chopsticks key yet, so ELEMENT_AT returns NULL on every row. Writing ['chopsticks'] with brackets would stop the query with an error naming the missing key, which is the behavior you want during development and the one you avoid in a scheduled report.

MAP_KEYS(map) returns the keys as an array, MAP_VALUES(map) returns the values as an array in the matching order, and CARDINALITY(map) counts the pairs. These are how you inspect a map whose keys you do not know in advance, such as an extras map after the team has added a few more.

Every value JSON_EXTRACT returns is of type JSON, including the ones that look like text or numbers. The engine will not compare a JSON value with = to a VARCHAR literal, and will not add JSON numbers with SUM or average them with AVG. Grouping by an uncast JSON value does run, but the group labels keep their quotes, "dashpass" rather than dashpass, and stay JSON rather than text you can filter or join on. CAST to VARCHAR or INTEGER fixes all 3. The query below is the first thing most people try, and it fails.

CAST to VARCHAR turns a JSON string into plain text and drops the quotes. CAST to INTEGER turns a JSON number into a whole number, CAST to BOOLEAN turns true or false into a SQL boolean, and both a missing value and a JSON null become SQL NULL through any cast.

With the plan cast to VARCHAR the engine can group by it, and with the tip cast to INTEGER, SUM can add it. The query below totals tips per plan over order_meta.

Order 5002 drops out because its value is false. Order 5004 drops out because its cast returns NULL, and NULL = true is neither true nor false under three-valued logic, so WHERE discards the row. Two rows survive, and the plan column now reads dashpass with no quotes.

CAST to ARRAY(VARCHAR) turns a JSON list of strings into a SQL array, and from there everything in the arrays section applies. The question from the intro, how many orders included a drink, is a CONTAINS on the cast tags list.

CAST to MAP(VARCHAR, VARCHAR) turns a JSON object into a map keyed by its field names. Every value is converted to the map's value type, so the customer id 88, a JSON number, becomes the text 88 here. Lookups then use the map bracket from the maps section, customer['plan'].

Casting text to JSON does something most people do not expect: CAST(payload AS JSON) wraps the entire string as one JSON string value, braces and all, rather than reading its structure. JSON_PARSE(payload) reads the structure and returns the document as a JSON value you can extract from. Going the other way, JSON_FORMAT(json_value) returns the JSON text as a VARCHAR, which is how you store an extracted branch back into a text column.

Counting orders per plan puts the cast to work in GROUP BY, where 2 mistakes stop the query. GROUP BY reads the columns of the FROM table, so GROUP BY plan fails: order_meta has order_id and payload, and the alias plan exists only in the SELECT list. A GROUP BY expression that differs from the selected one fails too, because the selected plan is then neither grouped nor aggregated.