Google BigQuery

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.

Google BigQueryIevgen Krasovytskyi11 min read

BigQuery JSON arrays and objects: JSON_QUERY_ARRAY, UNNEST and JSON_OBJECT

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.name is the same as JSON_QUERY(payload, '$.customer.name'), Google’s JSON data guide says (read in October 2026). payload['customer']['name'] returned the same "anna".
  • JSON_VALUE returns a STRING without quotes, and NULL when the path points at an object or array. That is why value_tags is 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, STRING

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

Hand this to your AI

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

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

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_ARRAY on 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). UNNEST over that NULL gives zero rows, so a query that uses the wrong function finishes with an empty result. Order 3 is false only because its items array is empty.
  • JSON_QUERY_ARRAY on 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. Use JSON_VALUE_ARRAY for 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 NULL or ARRAY_LENGTH when 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 STRING
WITH 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

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 in COALESCE.
  • 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

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

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 data model every report and spreadsheet uses.

Hand this to your AI

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

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

FAQ

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.

Who wrote this

Ievgen Krasovytskyi

Ievgen Krasovytskyi Head of Marketing

Ievgen Krasovytskyi is the Head of Marketing at OWOX, leading strategy across content, SEO, product marketing, and AI-powered automation. With deep expertise in analytics infrastructure, data warehouses, and marketing technology, he builds systems that connect marketing performance to business outcomes. Ievgen writes about SaaS growth, analytics workflows, and the future of AI in marketing operations.