BigQuery JSON arrays and objects: JSON_QUERY_ARRAY, UNNEST and JSON_OBJECT
A JSON array in BigQuery becomes rows with JSON_QUERY_ARRAY or JSON_VALUE_ARRAY plus UNNEST, and rows become JSON again with JSON_OBJECT, JSON_ARRAY and TO_JSON. This guide runs every step on one three-order table and shows the result: dot notation on a JSON column, JSON_VALUE against JSON_QUERY, the orders UNNEST silently drops, JSON_KEYS for finding keys that only some rows have, and the traps in building JSON.
A JSON array in BigQuery is one value in one cell. To count, sum or join what is inside it, you turn it into rows: JSON_QUERY_ARRAY when the elements are objects, JSON_VALUE_ARRAY when they are plain values, and UNNEST around either. Going the other way, JSON_OBJECT, JSON_ARRAY and TO_JSON build JSON from columns.
| Task | Use | Returns | Watch out for |
|---|---|---|---|
Read a field from a JSON column |
payload.customer.name |
JSON |
the string keeps its quotes |
| Read a scalar as text | JSON_VALUE(payload, '$.path') |
STRING |
an object or array gives NULL |
| Read an object or array | JSON_QUERY(payload, '$.path') |
JSON |
|
| Array of objects to rows | UNNEST(JSON_QUERY_ARRAY(...)) |
one JSON per element |
an empty or missing array drops the row |
| Array of values to rows | UNNEST(JSON_VALUE_ARRAY(...)) |
one STRING per element |
an array of objects gives NULL |
| List the keys | JSON_KEYS(payload) |
ARRAY<STRING> |
keys inside arrays are skipped by default |
| Build an object | JSON_OBJECT('k', v, ...) |
JSON |
NULL key is an error; key order is not kept |
| Build an array | JSON_ARRAY(v, ...) |
JSON |
|
| Convert a row or struct | TO_JSON(...) |
JSON |
wide NUMERIC loses digits unless you stringify |
Note: Originally published in August 2025. Fully rewritten in October 2026: every result below was produced by running the query in BigQuery on 16 October 2026.
Reading to get this done?
The table used in every example
The table used in every example
Paste this before any query below and it runs as is:
WITH orders AS (
SELECT 1 AS order_id, JSON '''{
"customer": {"name": "anna", "tags": ["new", "promo"]},
"items": [{"sku": "A1", "qty": 2, "price": 10.5},
{"sku": "B2", "qty": 1, "price": 40}],
"status": "paid"}''' AS payload
UNION ALL SELECT 2, JSON '''{
"customer": {"name": "ben", "tags": []},
"items": [{"sku": "A1", "qty": 1, "price": 10.5}],
"status": "refunded"}'''
UNION ALL SELECT 3, JSON '''{
"customer": {"name": "cara"},
"items": [],
"status": "paid", "coupon": "FALL10"}'''
)Three orders, stored in a column of BigQuery’s native JSON type. Order 2 has an empty tag list, order 3 has no tag list at all, no items, and the only coupon. Those gaps are where most JSON queries go wrong.
Reading one field: dot notation, JSON_VALUE and JSON_QUERY
Reading one field: dot notation, JSON_VALUE and JSON_QUERY
SELECT
order_id,
payload.customer.name AS dot_name,
JSON_VALUE(payload, '$.customer.name') AS value_name,
JSON_QUERY(payload, '$.customer.tags') AS query_tags,
JSON_VALUE(payload, '$.customer.tags') AS value_tags,
payload.coupon AS coupon
FROM orders
ORDER BY order_id;| order_id | dot_name | value_name | query_tags | value_tags | coupon |
|---|---|---|---|---|---|
| 1 | “anna” | anna | [“new”,“promo”] | NULL | NULL |
| 2 | “ben” | ben | [] | NULL | NULL |
| 3 | “cara” | cara | NULL | NULL | “FALL10” |
- Dot notation returns
JSON, so a string keeps its quotes.payload.customer.nameis the same asJSON_QUERY(payload, '$.customer.name'), Google’s JSON data guide says (read in October 2026).payload['customer']['name']returned the same"anna". JSON_VALUEreturns aSTRINGwithout quotes, and NULL when the path points at an object or array. That is whyvalue_tagsis NULL on every row.- A missing key returns NULL, not an error. Order 3 has no tags, orders 1 and 2 have no coupon. The same document says the operators return SQL NULL when a member is not found.
The quotes matter as soon as you compare. WHERE payload.status = 'paid' does not run at all:
No matching signature for operator = for argument types: JSON, STRINGConvert first: STRING(payload.status) = 'paid' or JSON_VALUE(payload, '$.status') = 'paid'. Both returned true, false, true.
JSON_EXTRACT is the older name
JSON_EXTRACT_SCALAR(payload, '$.customer.name') returned anna and JSON_EXTRACT(payload, '$.customer.name') returned "anna", the same as JSON_VALUE and JSON_QUERY. Google’s JSON functions reference now marks the JSON_EXTRACT family as deprecated and points to JSON_QUERY and JSON_VALUE (read in October 2026). They still run, so old queries do not break, but write new ones with the current names. Paths with special characters, the “constant expression” error and PARSE_JSON are covered in JSON_VALUE, JSON_QUERY and PARSE_JSON.
Each order in your BigQuery table holds its items as a JSON array, and you need one row per item to count and sum them.
Paste this into your AI assistant. It writes the JSON_QUERY_ARRAY and UNNEST query for your table, keeps the orders a plain UNNEST would drop, and returns one row per item.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/bigquery-json-objects-and-arrays.md. The task: write me a BigQuery query that turns the JSON items array in my orders table into one row per item, keeps orders with an empty array, and totals quantity and revenue by product. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
JSON_QUERY_ARRAY and UNNEST: one row per item
JSON_QUERY_ARRAY and UNNEST: one row per item
SELECT
order_id,
pos,
JSON_VALUE(item, '$.sku') AS sku,
INT64(item.qty) AS qty,
FLOAT64(item.price) AS price,
INT64(item.qty) * FLOAT64(item.price) AS line_total
FROM orders,
UNNEST(JSON_QUERY_ARRAY(payload, '$.items')) AS item
WITH OFFSET AS pos
ORDER BY order_id, pos;| order_id | pos | sku | qty | price | line_total |
|---|---|---|---|---|---|
| 1 | 0 | A1 | 2 | 10.5 | 21 |
| 1 | 1 | B2 | 1 | 40 | 40 |
| 2 | 0 | A1 | 1 | 10.5 | 10.5 |
JSON_QUERY_ARRAY turns the items array into a BigQuery array, one JSON value per element. UNNEST makes each element a row, and WITH OFFSET adds its position, counted from 0. Each item is a JSON object, so dot notation works on it, and INT64() and FLOAT64() turn the values into numbers you can multiply.
Order 3 is gone. Its items array is empty, and the comma in FROM orders, UNNEST(...) is a cross join: an order with no elements produces no rows. Here that is what you want, since there are no lines to show. When you count orders or sum per customer it is not, and the next section shows the fix.
JSON_VALUE_ARRAY and the orders UNNEST drops
JSON_VALUE_ARRAY and the orders UNNEST drops
Tags are plain strings, so JSON_VALUE_ARRAY fits. The same query with a comma join and with LEFT JOIN:
SELECT 'comma' AS join_type, order_id, tag
FROM orders,
UNNEST(JSON_VALUE_ARRAY(payload, '$.customer.tags')) AS tag
UNION ALL
SELECT 'left join', order_id, tag
FROM orders
LEFT JOIN UNNEST(JSON_VALUE_ARRAY(payload, '$.customer.tags')) AS tag
ORDER BY join_type, order_id, tag;| join_type | order_id | tag |
|---|---|---|
| comma | 1 | new |
| comma | 1 | promo |
| left join | 1 | new |
| left join | 1 | promo |
| left join | 2 | NULL |
| left join | 3 | NULL |
The comma join returned one order; LEFT JOIN UNNEST kept all three. Order 2’s empty array and order 3’s missing key both become a row with a NULL tag. Use LEFT JOIN whenever the row count of the outer table matters, for example “orders by tag, including untagged”. The same applies to JSON_QUERY_ARRAY.
Which _ARRAY function: the trap in each
Which _ARRAY function: the trap in each
SELECT
order_id,
JSON_VALUE_ARRAY(payload, '$.items') IS NULL AS value_array_on_items_is_null,
ARRAY_LENGTH(JSON_QUERY_ARRAY(payload, '$.items')) AS n_items,
JSON_QUERY_ARRAY(payload, '$.customer.tags') IS NULL AS tags_is_null,
TO_JSON_STRING(JSON_QUERY_ARRAY(payload, '$.customer.tags')) AS tags_as_json
FROM orders
ORDER BY order_id;| order_id | value_array_on_items_is_null | n_items | tags_is_null | tags_as_json |
|---|---|---|---|---|
| 1 | true | 2 | false | [“new”,“promo”] |
| 2 | true | 1 | false | [] |
| 3 | false | 0 | true | null |
JSON_VALUE_ARRAYon an array of objects returns NULL, not an error. Google’s reference says it returns SQL NULL if the value “isn’t an array or not an array containing only scalar values” (read in October 2026).UNNESTover that NULL gives zero rows, so a query that uses the wrong function finishes with an empty result. Order 3 is false only because itsitemsarray is empty.JSON_QUERY_ARRAYon strings keeps the quotes. Each tag comes back as the JSON value"new": comparing it with the string'new'fails with a type error on a JSON column, and on a STRING column it returns false because of the quotes. UseJSON_VALUE_ARRAYfor strings and numbers.- Missing and empty are different values. Order 2’s empty tag list is an empty array; order 3’s missing one is NULL. They can look alike in a results grid, so test with
IS NULLorARRAY_LENGTHwhen it matters.
The same functions on a STRING column
The same functions on a STRING column
Many tables store JSON as text in a STRING column. The JSON_ functions accept either type, but dot notation does not:
WITH raw AS (
SELECT '{"customer": {"name": "anna"}, "items": [{"sku": "A1", "qty": 2}]}' AS s
)
SELECT s.customer.name FROM raw;Cannot access field customer on a value with type STRINGWITH raw AS (
SELECT '{"customer": {"name": "anna"}, "items": [{"sku": "A1", "qty": 2}]}' AS s
)
SELECT
JSON_VALUE(s, '$.customer.name') AS from_string,
JSON_QUERY_ARRAY(s, '$.items')[OFFSET(0)] AS first_item,
PARSE_JSON(s).customer.name AS after_parse
FROM raw;| from_string | first_item | after_parse |
|---|---|---|
| anna | {“sku”:“A1”,“qty”:2} | “anna” |
On a STRING, JSON_QUERY_ARRAY returns ARRAY<STRING>, so first_item is text, not JSON, and first_item.sku would fail the same way. PARSE_JSON converts the text to JSON and dot notation works again. If you query the column often, store it as JSON: it is parsed once, at load. Getting JSON files into a table is in loading data into BigQuery.
JSON_KEYS: which keys only some rows have
JSON_KEYS: which keys only some rows have
Event and API data drifts: a field is added, renamed or sent only sometimes. JSON_KEYS lists the keys in a value, and grouping over it shows how many rows carry each one:
SELECT key, COUNT(*) AS orders_with_key
FROM orders, UNNEST(JSON_KEYS(payload)) AS key
GROUP BY key
ORDER BY orders_with_key, key;| key | orders_with_key |
|---|---|
| coupon | 1 |
| customer.tags | 2 |
| customer | 3 |
| customer.name | 3 |
| items | 3 |
| status | 3 |
Nested keys come back as paths, such as customer.tags. By default (mode => 'strict') keys inside arrays are skipped, which is why items.sku is not listed; JSON_KEYS(payload, mode => 'lax') added items.price, items.qty and items.sku for orders 1 and 2. A second argument limits depth: JSON_KEYS(payload, 1) returned only the top-level keys. Any key with a count below the row total is a field your query has to treat as optional.
Building JSON: JSON_OBJECT
SELECT
order_id,
JSON_OBJECT(
'order_id', order_id,
'customer', JSON_VALUE(payload, '$.customer.name'),
'items', ARRAY_LENGTH(JSON_QUERY_ARRAY(payload, '$.items')),
'coupon', JSON_VALUE(payload, '$.coupon')
) AS summary
FROM orders
ORDER BY order_id;| order_id | summary |
|---|---|
| 1 | {“coupon”:null,“customer”:“anna”,“items”:2,“order_id”:1} |
| 2 | {“coupon”:null,“customer”:“ben”,“items”:1,“order_id”:2} |
| 3 | {“coupon”:“FALL10”,“customer”:“cara”,“items”:0,“order_id”:3} |
- Keys come out in alphabetical order, not the order you wrote them. Google’s reference says the order of key-value pairs isn’t preserved. JSON readers do not care; a test that compares the JSON as text does.
- A NULL value is kept as JSON
null. The key stays in the object. - A NULL key stops the query:
A key cannot be NULL; error in JSON_OBJECT expression. Wrap a key that comes from a column inCOALESCE. - An odd number of arguments does not run:
JSON_OBJECT('a', 1, 'b')fails with “Expected a multiple of 2 but got 3 repeated arguments”. - A repeated key keeps the first value:
JSON_OBJECT('a', 1, 'a', 2)returned{"a":1}. JSON_OBJECT(['x', 'y'], [10, 20])builds the same thing from two arrays:{"x":10,"y":20}.JSON_OBJECT()with no arguments returns{}.
JSON_ARRAY and TO_JSON
SELECT
JSON_ARRAY(1, 'two', NULL, [3, 4]) AS arr,
JSON_ARRAY() AS empty_arr,
TO_JSON(STRUCT(1 AS id,
['new', 'promo'] AS tags,
STRUCT('anna' AS name) AS customer)) AS from_struct;| arr | empty_arr | from_struct |
|---|---|---|
| [1,“two”,null,[3,4]] | [] | {“customer”:{“name”:“anna”},“id”:1,“tags”:[“new”,“promo”]} |
JSON_ARRAY takes a fixed list of values; a BigQuery array inside it becomes a nested JSON array. TO_JSON converts any value, including a struct or a whole row (TO_JSON(t)), and nested structs become nested objects. That is usually the shortest way to a nested payload. TO_JSON_STRING does the same and returns text instead of the JSON type.
From rows back to one JSON array
JSON_ARRAY cannot collect rows. For that, aggregate first with ARRAY_AGG, then convert. Here are the order lines regrouped per SKU:
SELECT
sku,
TO_JSON(ARRAY_AGG(STRUCT(order_id, qty) ORDER BY order_id)) AS orders_json
FROM (
SELECT JSON_VALUE(item, '$.sku') AS sku,
order_id,
INT64(item.qty) AS qty
FROM orders, UNNEST(JSON_QUERY_ARRAY(payload, '$.items')) AS item
)
GROUP BY sku
ORDER BY sku;| sku | orders_json |
|---|---|
| A1 | [{“order_id”:1,“qty”:2},{“order_id”:2,“qty”:1}] |
| B2 | [{“order_id”:1,“qty”:1}] |
Unnest, regroup, convert: the round trip is three steps. Without ORDER BY inside ARRAY_AGG, the order of the elements is not guaranteed; more on that in aggregate functions.
TO_JSON and wide numbers
Results from a test run:
| Expression | Result |
|---|---|
TO_JSON(NUMERIC '12345678901234567890.123456789') |
1.2345678901234567e+19 |
same, with stringify_wide_numbers => TRUE |
“12345678901234567890.123456789” |
TO_JSON(9007199254740993, stringify_wide_numbers => TRUE) |
“9007199254740993” |
TO_JSON(9007199254740992, stringify_wide_numbers => TRUE) |
9007199254740992 |
Google’s reference is of two minds here: the function description says a value that cannot be stored without losing precision throws an error by default, while its encoding table says such a NUMERIC is “encoded as a number”. Our run matched the table: the wide NUMERIC was rounded, with no error. With stringify_wide_numbers => TRUE, numbers that a FLOAT64 cannot hold exactly (Google calls this outside the type domain) are written as strings and keep every digit; the integer that fits stays a number. Use it for IDs and money that leave BigQuery as JSON.
TO_JSON also converted GEOGRAPHY, BYTES and INTERVAL without error in our test: to "POINT(30.5 50.4)", base64 "YWJj" and "P2D". Dates and timestamps become strings such as "2026-10-16".
To change JSON that already exists, adding to an array, setting or removing a key, see JSON data manipulation functions. UNNEST and what to do with an array once you have one are in array functions.
Extract the fields once
A JSON column is a place where one dataset quietly becomes several. One query unnests with a comma join and loses the orders with no items; another uses LEFT JOIN; a third reads $.items with JSON_VALUE_ARRAY and gets nothing. Each returns rows, and the totals differ.
The steady fix is to unnest and cast in one query, with the join type and the optional keys written down, and have every report read plain typed columns. In OWOX Data Marts that query is a Data Mart: your SQL, with a description and an owner, run as written in your own BigQuery project. OWOX Data Marts does not transform the data or guess the paths; it keeps the extraction you wrote as the one model every report and spreadsheet uses.
Each order in your BigQuery table holds its items as a JSON array, and you need one row per item to count and sum them.
Paste this into your AI assistant. It writes the JSON_QUERY_ARRAY and UNNEST query for your table, keeps the orders a plain UNNEST would drop, and returns one row per item.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/bigquery-json-objects-and-arrays.md. The task: write me a BigQuery query that turns the JSON items array in my orders table into one row per item, keeps orders with an empty array, and totals quantity and revenue by product. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Prefer to start on your own? Get started free
Frequently Asked Questions
How do I turn a JSON array into rows in BigQuery?
Wrap JSON_QUERY_ARRAY (for an array of objects) or JSON_VALUE_ARRAY (for an array of strings or numbers) in UNNEST, then read each element with JSON_VALUE or dot notation. Use LEFT JOIN UNNEST instead of a comma join if rows with an empty or missing array must stay in the result.
How do I handle NULL values when building JSON in BigQuery?
A NULL value in JSON_OBJECT or JSON_ARRAY is written as JSON null and the key stays in the object. A NULL key stops the query with an error, so wrap any key that comes from a column in COALESCE.
What is the difference between JSON_QUERY_ARRAY and JSON_VALUE_ARRAY?
JSON_QUERY_ARRAY returns each element as JSON, so it works on arrays of objects but keeps the quotes around strings. JSON_VALUE_ARRAY returns plain strings without quotes, and returns NULL when the array contains objects.
What errors should I watch for when building JSON in BigQuery?
JSON_OBJECT fails on a NULL key and on an odd number of arguments, keeps only the first value of a repeated key, and does not keep key order. TO_JSON can round a very wide NUMERIC to a float, so pass stringify_wide_numbers => TRUE for IDs and money.
How do I create nested JSON or a JSON array from rows in BigQuery?
Use TO_JSON on a STRUCT that contains other structs or arrays; nested structs become nested objects. To collect rows into one JSON array, aggregate them with ARRAY_AGG and convert the result with TO_JSON.
Should I use JSON_EXTRACT or JSON_QUERY in BigQuery?
Use JSON_QUERY and JSON_VALUE. Google marks JSON_EXTRACT, JSON_EXTRACT_SCALAR and the other JSON_EXTRACT functions as deprecated; they still run and return the same results, so existing queries keep working.
