Unnesting JSON Arrays in BigQuery With JSON_QUERY_ARRAY

BigQuery does not expand a JSON array on its own. You turn it into a SQL array first, then UNNEST the SQL array into rows. That two-step shape is a little more verbose than other databases' single function, and it has the benefit of being explicit about types: the first step decides whether the elements are JSON values or strings, and the second decides how a parent with no elements is handled. This article shows the standard pattern, the positional and empty-array variants, nested arrays, filters that avoid unnesting and the places where the types bite.

The basic pattern

SELECT o.id,
       JSON_VALUE(item, '$.sku')  AS sku,
       INT64(item.qty)            AS qty
FROM orders AS o,
     UNNEST(JSON_QUERY_ARRAY(o.doc, '$.items')) AS item;

JSON_QUERY_ARRAY(doc, path) returns an ARRAY<JSON>: the elements of the JSON array at the path, each as a JSON value. UNNEST turns a SQL array into a set of rows, one per element, and the comma join runs it for each order. In the select list, item is a JSON value, so you use the usual extraction functions on it: JSON_VALUE(item, '$.sku') for text, field access item.qty for a JSON value, and a typed converter such as INT64() to make it a number. The functions are covered in extracting JSON fields in BigQuery.

For an array of scalars, there is a shortcut:

SELECT o.id, tag
FROM orders AS o,
     UNNEST(JSON_VALUE_ARRAY(o.doc, '$.tags')) AS tag;

JSON_VALUE_ARRAY returns ARRAY<STRING>: each scalar element becomes a string (numbers and booleans too), so tag is already a STRING and needs no conversion. If an element is itself an object or array, the documentation says it cannot be returned as a scalar; test with your data and use JSON_QUERY_ARRAY when the array is not purely scalars.

If the path does not point to an array, JSON_QUERY_ARRAY returns NULL; and UNNEST of a NULL array produces no rows. This makes a missing key and an empty array behave the same way, as the next section explains.

Empty and missing arrays

A comma join is a cross join with UNNEST, and cross joins on empty sets produce no rows. An order with "items": [], no items key, or "items": null disappears from the result. To keep it:

SELECT o.id,
       JSON_VALUE(item, '$.sku') AS sku
FROM orders AS o
LEFT JOIN UNNEST(JSON_QUERY_ARRAY(o.doc, '$.items')) AS item
  ON TRUE;

LEFT JOIN UNNEST(...) ON TRUE returns the order with NULL for item. This is the BigQuery form of SQL Server's OUTER APPLY (reading JSON with OPENJSON), Snowflake's OUTER => TRUE (flattening with LATERAL FLATTEN) and PostgreSQL's left join lateral ... on true. As in the others, a row with a NULL item could also come from an element that is a JSON null: add the offset to tell them apart.

Position of each element

WITH OFFSET gives the zero-based index:

SELECT o.id, pos, JSON_VALUE(item, '$.sku') AS sku
FROM orders AS o,
     UNNEST(JSON_QUERY_ARRAY(o.doc, '$.items')) AS item WITH OFFSET AS pos
ORDER BY o.id, pos;

UNNEST does not promise any output order unless you ask for it, and the position is the only reliable key if the order of the array has meaning (steps in a workflow, ranked results). Always include ORDER BY ... pos when order matters, and use the offset when you need to rebuild the array in the same order after changes.

Nested arrays

Each item has an opts array; unnest it inside the first unnest by referencing the first alias:

SELECT o.id,
       JSON_VALUE(item, '$.sku') AS sku,
       opt
FROM orders AS o,
     UNNEST(JSON_QUERY_ARRAY(o.doc, '$.items')) AS item
LEFT JOIN UNNEST(JSON_VALUE_ARRAY(item, '$.opts')) AS opt ON TRUE;

UNNEST can refer to a previous item in the same FROM clause (a correlated reference), so each item's opts array is expanded for that item. The LEFT JOIN ... ON TRUE on the inner level keeps items with no options, as B2 has here, with a NULL opt. Row counts multiply: an order with three items of two options each yields six rows.

Filtering without unnesting

To test whether any element matches, an EXISTS subquery on the unnested array avoids multiplying the outer rows:

SELECT o.id
FROM orders AS o
WHERE EXISTS (
  SELECT 1
  FROM UNNEST(JSON_QUERY_ARRAY(o.doc, '$.items')) AS item
  WHERE INT64(item.qty) >= 2
);

One row per order that has such an item, no duplicates, no DISTINCT. For arrays of scalars, 'sale' IN UNNEST(JSON_VALUE_ARRAY(o.doc, '$.tags')) is shorter. Counting elements without unnesting is ARRAY_LENGTH(JSON_QUERY_ARRAY(o.doc, '$.items')), which returns NULL if the path is not an array, so wrap it in IFNULL(..., 0).

Putting arrays back together

After filtering or transforming, rebuild with ARRAY_AGG and TO_JSON, or JSON_OBJECT:

SELECT o.id,
       TO_JSON(ARRAY_AGG(STRUCT(JSON_VALUE(item, '$.sku') AS sku, INT64(item.qty) AS qty) ORDER BY pos))
FROM orders AS o,
     UNNEST(JSON_QUERY_ARRAY(o.doc, '$.items')) AS item WITH OFFSET AS pos
GROUP BY o.id;

ARRAY_AGG ... ORDER BY pos restores the order, STRUCT shapes each element and TO_JSON turns the array into a JSON value. For the same reconstruction job in other engines, see aggregating rows into JSON with jsonb_agg and building JSON results with json_group_array.

Newer BigQuery releases also document a JSON_FLATTEN function that flattens nested arrays inside a JSON value. Check the current documentation for its exact behavior before using it in place of nested unnesting.

Types and errors

Because an element of JSON_QUERY_ARRAY is a JSON value, the conversion rules from the extraction article apply: INT64(item.qty) is strict and errors on a JSON string "2", while LAX_INT64(item.qty) coerces and SAFE.INT64(item.qty) returns NULL. When elements might not be objects at all (a mixed array), JSON_TYPE(item) tells you which: filter with WHERE JSON_TYPE(item) = 'object' before extracting fields. Quote keys with special characters in the path: JSON_VALUE(item, "$['order-id']").

Cost

BigQuery bills on the bytes it reads. Unnesting does not change the bytes read from the column; it changes the rows processed afterward, which affects slot time. Two habits keep cost down: filter on partition and clustering columns before unnesting, and select only the paths you need. If an array is unnested by every report, materialize the unnested rows into a table with typed columns and cluster it on the field people filter by. That is a one-time CREATE TABLE ... AS SELECT using the query above.

When UNNEST of JSON is not the best route

The array is already a SQL array. If the data were loaded as a repeated field (ARRAY<STRUCT>), you can UNNEST it directly without JSON functions, and queries are cheaper and typed. Prefer that when you control the load and the shape is stable.

You need the count or one element. ARRAY_LENGTH and a subscript (JSON_QUERY_ARRAY(...)[SAFE_OFFSET(0)]) avoid unnesting.

Elements have different shapes. A mixed array needs per-element type checks, which makes the query a data cleaning job; do it once in a staging table.

The array is huge. Millions of elements per row multiply work; consider storing them as rows from the start. A newline-delimited load (converting a JSON array to JSON Lines) is the usual ingest.

The reference is Google's documentation of JSON functions and the UNNEST operator. A sample array can be inspected in the browser with the JSON formatter. The comparison with the other commercial engines is in JSON in SQL Server, Oracle, Snowflake and BigQuery compared.