Extracting JSON Fields in BigQuery With JSON_VALUE

BigQuery has two ways of holding JSON: a STRING column that happens to contain JSON, which is how everyone began, and a native JSON type that stores a parsed representation. The extraction functions work on both. They also hide a rule that causes most mistakes: JSON_VALUE always returns a STRING. This article goes through the extraction functions, the field access operator that only the JSON type has, the typed converters, null handling and the SAFE. prefix.

Creating JSON values

A JSON column is filled from text with PARSE_JSON, or built with JSON_OBJECT, JSON_ARRAY or TO_JSON:

INSERT INTO orders (id, doc)
VALUES (1, PARSE_JSON('{"customer":{"name":"Ada","tier":"gold"},"items":[{"sku":"A1","qty":2}],"total":19.5,"paid":true,"note":null}'));

PARSE_JSON raises an error for text that is not valid JSON, and SAFE.PARSE_JSON returns NULL instead. When loading from newline-delimited files, a column declared as JSON receives each value as a parsed document (loading and converting JSON Lines explains the file shape the load jobs expect).

JSON_VALUE: one scalar, as text

SELECT JSON_VALUE(doc, '$.customer.name')  AS name,
       JSON_VALUE(doc, '$.total')          AS total,
       JSON_VALUE(doc, '$.paid')           AS paid,
       JSON_VALUE(doc, '$.items[0].sku')   AS first_sku
FROM orders;

The path starts with $, uses dots for keys and brackets for array positions. The result is a STRING for every one of these, including total (the text 19.5) and paid (the text true). That is the rule behind most of the confusion: if you compare it with a number, BigQuery either raises a type error or, in a CAST-free comparison, behaves unexpectedly. Convert deliberately:

WHERE CAST(JSON_VALUE(doc, '$.total') AS FLOAT64) > 100

JSON_VALUE returns NULL in three situations: the path does not exist, the value at the path is a JSON null, and the value is an object or array (not a scalar). The last is easy to forget. JSON_VALUE(doc, '$.items') is NULL, not the array's text. For a sub-document, use JSON_QUERY.

JSON_QUERY: objects and arrays

JSON_QUERY(doc, '$.items') returns a JSON value for an object or array (and for a scalar, a JSON scalar). A JSON null at the path gives a JSON null value, and a missing path gives SQL NULL, which is a difference from JSON_VALUE, where both are NULL. There are array variants that return a SQL array directly: JSON_QUERY_ARRAY(doc, '$.items') returns ARRAY<JSON> and JSON_VALUE_ARRAY(doc, '$.tags') returns ARRAY<STRING> for an array of scalars. These are what you pass to UNNEST (unnesting JSON arrays in BigQuery).

The field access operator

On a JSON-typed column (not on a string), BigQuery lets you navigate with dots and brackets as if the document were a struct:

SELECT doc.customer.name,
       doc.items[0].sku,
       doc['customer']['tier']
FROM orders;

The result of each expression is a JSON value, so the output is JSON-formatted (a string appears with double quotes) and you convert it with a typed function. A missing field gives NULL. The subscript form with a string, doc['order-id'], handles keys that are not valid identifiers. Field access is concise and works only on JSON; if the column is a STRING, parse it first or use JSON_VALUE.

Typed converters

Four functions turn a JSON value into a SQL type:

SELECT STRING(doc.customer.name) AS name,
       FLOAT64(doc.total)        AS total,
       INT64(doc.items[0].qty)   AS qty,
       BOOL(doc.paid)            AS paid
FROM orders;

They are strict. INT64 of a JSON number that is an integer returns the number; INT64 of the JSON string "7" raises an error, and so does STRING of a number. If the data is messy, the lax versions coerce: LAX_INT64, LAX_FLOAT64, LAX_STRING and LAX_BOOL convert a JSON string "7" to 7, a number to its text, and so on, and return NULL where no sensible conversion exists. The documentation lists the conversion rules for each. And any function can take the SAFE. prefix to turn an error into NULL: SAFE.INT64(doc.items[0].qty).

My preference, given the choice, is LAX_ for ingestion queries over data from other people, where a surprise should become NULL and be counted, and the strict functions in curated tables where a surprise should stop the job. The pair makes the policy visible in the SQL.

JSON_TYPE(doc.total) returns the type name (number, string, boolean, object, array, null) and is the audit tool: SELECT JSON_TYPE(doc.qty), COUNT(*) FROM ... GROUP BY 1 shows whether a field is consistent.

Null: what comes back

For the field note with the document value null, and a field gone that is absent:

According to the documentation, a JSON null is a value of the JSON type and is distinct from a SQL NULL, and the scalar-returning functions convert it to SQL NULL. When the difference matters, test JSON_TYPE(doc.note) = 'null'. This is the same family of trap as in Snowflake, MySQL and PostgreSQL, each with a different default.

The legacy functions

JSON_EXTRACT and JSON_EXTRACT_SCALAR predate the JSON type and work on strings. JSON_EXTRACT_SCALAR(str, '$.a') is what JSON_VALUE replaced, and the documentation recommends the new names. You will meet the old ones in existing queries; they are functionally close, but JSON_VALUE has more consistent behavior on edge cases such as keys with special characters. When you migrate, replace them one at a time and compare results on a sample.

Dates and numbers

JSON has no date type, so a field holding "2026-10-26" is a string. Convert with DATE(JSON_VALUE(doc, '$.placed')) or TIMESTAMP(JSON_VALUE(...)), which raise an error on a format they do not accept (use SAFE. in ingestion). For numbers, large integers need care: JSON numbers can hold integers beyond 2^53, and a conversion to FLOAT64 rounds them, while INT64 is exact up to 64 bits. Read identifiers that can exceed that as strings. The cross-engine discussion is in how JSON handles big numbers and JSON dates lack a date type.

Cost

BigQuery bills by bytes scanned, so the question is how much a path read costs. For a STRING column, the whole string is read for every query. The documentation describes the native JSON type as stored in a columnar way, so that a query can read only the paths it references, which is a reason to prefer it to strings. I could not measure that here, so check the bytes processed estimate in the console before and after you switch types: that estimate is the measurement. Whichever type you use, extracting the hot fields into typed, clustered columns in a derived table is the standard fix for cost and speed. For the engines I could test, the same advice with numbers is in JSON functions in SQLite.

When JSON_VALUE is the wrong function

The target is an object or array. It returns NULL; use JSON_QUERY.

You need the number as a number. Use INT64() or FLOAT64() on the JSON value, or cast, rather than comparing text.

The data is messy. Use the LAX_ converters and SAFE. and count the NULLs.

You need every element of an array. Use UNNEST(JSON_QUERY_ARRAY(...)).

The column is a STRING you control. Convert it to the JSON type; field access, typed converters and cheaper scans come with it.

The reference is Google's documentation of the JSON functions and of working with JSON data. A document can be formatted in the browser with the JSON formatter before you load it.