Semi-structured data: JSON
Some systems store a whole record as one JSON value in a single column. BigQuery reads fields out of it with a path, and what comes back has to be converted before you can count or compare with it.
How it works
A JSON value is a tree of objects with named keys, arrays, and plain values such as text, numbers and true or false. A path says where to look. In a path, $ is the whole value, .vehicle steps into a key and [0] takes the first element of an array. The function JSON_VALUE(payload, path) returns a plain value as a STRING. The function JSON_QUERY(payload, path) returns the JSON found at the path, whatever it is. Dot access, as in payload.vehicle.model, returns JSON too. JSON cannot be compared with a number or a text, so you convert it first.
- The table
raw_test_payloadsholds one JSON document for each MOT test of 2024, as a garage app might have sent it. No garage sent these. The course built the table from the MOT sample, so the documents carry the same facts asfact_mot_test. The columnpayloadhas the typeJSON. JSON_VALUE(payload, '$.vehicle.fuel_type')returns the fuel type as aSTRING, for exampleDiesel.JSON_QUERY(payload, '$.vehicle.fuel_type')returns"Diesel", a JSON string, which shows with its quotes. For an object or an array,JSON_VALUEreturnsNULLandJSON_QUERYreturns the whole object or array. Dot access,payload.vehicle.fuel_type, gives the same JSON asJSON_QUERY.- To count or compare, convert the JSON to a SQL type.
INT64(x),FLOAT64(x),STRING(x)andBOOL(x)stop the query when the value is not that kind of JSON. A JSONnullis not a number, soINT64fails on it. TheLAX_versions, such asLAX_INT64(x), convert what they can, so JSON text"42"becomes 42, and give SQLNULLfor what they cannot.SAFE.INT64(x)givesNULLinstead of failing too. A cast of the text works as well:CAST(JSON_VALUE(payload, '$.vehicle.manufacture_year') AS INT64). - Text compares letter by letter, so
'9'is greater than'10'. Convert a number toINT64before you compare it, and do not compare the textJSON_VALUEreturns with a quoted number.JSONvalues also cannot be grouped, sorted or compared with=, so read the field withJSON_VALUEor a conversion first. - A JSON array is not a SQL array.
JSON_QUERY_ARRAY(x, '$.tags')returns anARRAY<JSON>, andJSON_VALUE_ARRAY(x, '$.tags')returns anARRAY<STRING>when the elements are plain values. Put the result inUNNESTto get one row for each element:FROM t, UNNEST(JSON_QUERY_ARRAY(x, '$.tags')) AS tag. Read an element that is an object withJSON_VALUE(tag, '$.name'). An array with no elements gives no rows. - A key can hold JSON
null, or it can be missing. On aJSONvalue,JSON_VALUEand everyLAX_function give SQLNULLfor both. Dot access andJSON_QUERYgive SQLNULLonly for the missing key. For a JSONnullthey give a JSON value, andx.note IS NULLis false.JSON_TYPE(x.note)says what is there:'null'for a JSONnull,NULLfor a missing key, and'string','number','boolean','object'or'array'otherwise. - JSON can also sit in a
STRINGcolumn as text.JSON_VALUE,JSON_QUERYand the array readers read text too. The conversions,JSON_TYPEand dot access need aJSONvalue, so runPARSE_JSON(text)first. On text,JSON_QUERYgivesNULLfor a JSONnullas well, so it cannot tell anullfrom a missing key. - Older code uses
JSON_EXTRACT,JSON_EXTRACT_SCALAR,JSON_EXTRACT_ARRAYandJSON_EXTRACT_STRING_ARRAY. They are the old names ofJSON_QUERY,JSON_VALUE,JSON_QUERY_ARRAYandJSON_VALUE_ARRAY, and they still work. The docs article on JSON in BigQuery also coversTO_JSON,JSON_SETand what reading a JSON column costs.
Common mistake
A path with a typo returns NULL and no error. $.Vehicle is not $.vehicle. When a column of readings is all NULL, check the path against one payload before you count with it.
Worked example
SELECT
mot_test_id,
JSON_VALUE(payload, '$.vehicle.fuel_type') AS fuel_text,
JSON_QUERY(payload, '$.vehicle.fuel_type') AS fuel_json,
LAX_INT64(payload.vehicle.manufacture_year) AS built
FROM `learn.mot.raw_test_payloads`
ORDER BY mot_test_id
LIMIT 3fuel_text is a STRING, so it shows Diesel. fuel_json is JSON, so it shows "Diesel" with the quotes.
built reads a number from the JSON with dot access and LAX_INT64, so it can be added and compared.