BigQuery JSON functions: JSON_VALUE, JSON_QUERY and PARSE_JSON explained

JSON_VALUE: get a scalar

SELECT
  JSON_VALUE(j, '$.user.name'),     -- ana
  JSON_VALUE(j, '$.id'),            -- 7
  JSON_VALUE(j, '$.items[0].sku'),  -- x1
  JSON_VALUE(j, '$.user'),          -- NULL
  JSON_VALUE(j, '$.missing'),       -- NULL
  JSON_VALUE(j, '$.note');          -- NULL

Three things to take from that:

• The result is always a STRING. $.id comes back as the text 7, not the number. Cast it: SAFE_CAST(JSON_VALUE(j, '$.id') AS INT64).
• An object returns NULL. $.user is an object, so JSON_VALUE has nothing scalar to return.
• You cannot tell a missing key from a JSON null. Both return SQL NULL.

Array elements are addressed with [0], counting from zero.

JSON_QUERY: get an object or array

SELECT
  JSON_QUERY(j, '$.user'),
  -- {"name":"ana","tags":["a","b"]}
  JSON_QUERY(j, '$.user.name');
  -- "ana"

JSON_QUERY returns whatever is at the path as JSON. Note the second result: on a string it returns the value with its quotes. If you compare that to 'ana' the filter matches nothing. For scalars use JSON_VALUE.

JSON_VALUE_ARRAY and JSON_QUERY_ARRAY

Arrays need the _ARRAY versions, which return a BigQuery array you can UNNEST.

SELECT
  JSON_VALUE_ARRAY(j, '$.user.tags'),
  -- [a, b]
  ARRAY_LENGTH(JSON_QUERY_ARRAY(j, '$.items'));
  -- 2

Use JSON_VALUE_ARRAY when the elements are scalars, JSON_QUERY_ARRAY when they are objects. To get one row per item:

SELECT
  JSON_VALUE(item, '$.sku') AS sku,
  SAFE_CAST(JSON_VALUE(item, '$.qty') AS INT64)
    AS qty
FROM t,
  UNNEST(JSON_QUERY_ARRAY(j, '$.items')) AS item;
-- x1, 2
-- y2, 1

That pattern, UNNEST(JSON_QUERY_ARRAY(...)) and then JSON_VALUE on each element, covers most nested JSON. More on the array side in array functions.

JSON_EXTRACT_SCALAR vs JSON_VALUE

The older functions still work and you will see them in existing queries. They map one to one:

The practical difference is how a key with special characters is written. The document above has a key called a.b:

SELECT
  JSON_VALUE(j, '$."a.b"'),            -- dot
  JSON_EXTRACT_SCALAR(j, "$['a.b']");  -- dot

The current functions use double quotes inside the path; the older ones use brackets and single quotes. Google’s JSON functions reference recommends the current names for new queries.

“Argument 2 must be a constant expression”

The path has to be a literal. If you pass a column, BigQuery answers:

Argument 2 to JSON_EXTRACT must be a
constant expression

JSON_VALUE gives the same message with its own name. You hit this when the key you want is itself stored in a column. The way around it is to convert the string to the JSON type, which accepts a dynamic key in brackets:

SELECT
  key,
  STRING(PARSE_JSON(j)[key]) AS value
FROM (SELECT '{"a":"1","b":"2"}' AS j),
  UNNEST(['a', 'b']) AS key;
-- a, 1
-- b, 2

PARSE_JSON and the JSON type

BigQuery has a native JSON data type. PARSE_JSON converts a string into it, and then you can reach fields with dots:

SELECT
  PARSE_JSON(j).user.name,           -- "ana"
  STRING(PARSE_JSON(j).user.name),   -- ana
  INT64(PARSE_JSON(j).id),           -- 7
  JSON_TYPE(PARSE_JSON(j).id);       -- number

Dot access returns JSON, so a string still carries its quotes. Convert with STRING(), INT64(), FLOAT64() or BOOL(). These are strict: INT64() on a JSON string fails. The LAX_ versions convert where they reasonably can:

SELECT LAX_INT64(PARSE_JSON('{"n":"12"}').n);
-- 12

PARSE_JSON stops the query on malformed input. On data you do not control, use SAFE.PARSE_JSON, which returns NULL:

SELECT SAFE.PARSE_JSON('{bad json');
-- NULL

If a column holds JSON you query often, storing it as JSON instead of STRING means it is parsed once, at load.

JSON to string, and back

SELECT
  TO_JSON_STRING(PARSE_JSON(j).user),
  -- {"name":"ana","tags":["a","b"]}
  TO_JSON_STRING(STRUCT('ana' AS name, 3 AS n));
  -- {"name":"ana","n":3}

TO_JSON_STRING turns a JSON value, a struct or an array into JSON text. TO_JSON does the same but returns the JSON type. To go from JSON to a plain string value, use JSON_VALUE or STRING() as above. Functions that build and edit JSON are in JSON data manipulation functions and creating JSON objects and arrays.

Why a JSON function returns NULL

In order of how often each is the cause:

1. The value is an object or array and you used JSON_VALUE. Use JSON_QUERY.
2. The path is wrong. Keys are case-sensitive, arrays start at [0], and a key with a dot or space needs quotes.
3. The key is missing in that row. Normal for event data.
4. The string is not valid JSON. JSON_VALUE('not json', '$.a') returns NULL, not an error.
5. The value is a JSON null.

Count them before you rely on the column: COUNTIF(JSON_VALUE(j, '$.id') IS NULL) tells you how many rows the extraction lost.

Parse it once

JSON columns are where one dataset quietly becomes several. One query reads $.user.name, another $.user.full_name after a schema change, a third forgets the cast and compares "7" to 7. Each returns rows, and the totals differ.

The steady fix is to extract the fields in one query, with the paths and casts written down, and have everything else read plain typed columns. In OWOX Data Marts that query is a Data Mart: your SQL, with a description and an owner, run in your warehouse. OWOX does not parse the JSON for you or guess the paths. It keeps the extraction you wrote as the one every report uses.

Google BigQueryBigQuery Date Functions: Syntax, Examples and Common ErrorsOctober 6, 2026→Google BigQueryBigQuery Conversion Functions: Every Type-to-Type ConversionOctober 6, 2026→Google BigQueryHow to Connect GA4 to BigQuery: Setup, Limits and First QueriesOctober 6, 2026→

See all articles →