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.

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 3

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

All BigQuery lessons