How to Parse JSON in BigQuery

How do I use JSON_EXTRACT and related functions in BigQuery?

BigQuery’s JSON functions let you read, write, and filter semi-structured JSON data stored in STRING or JSON columns.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What is BigQuery’s JSON data type?

BigQuery supports a native JSON data type alongside JSON-in-STRING columns. Native JSON lets you store objects and arrays while preserving order and duplicate keys, enabling faster, type-aware parsing.

When should I use JSON functions instead of STRUCTs?

Use JSON when your source data is unpredictable or has many optional fields.Convert to STRUCTs only when the schema stabilizes and you need columnar performance or strict typing.

How do I extract a JSON value?

Quick example

Use JSON_VALUE for scalars, JSON_QUERY for objects/arrays, and JSON_EXTRACT for raw JSON.

SELECT JSON_VALUE(shipping_info, '$.carrier') AS carrier FROM Orders;

How do I query nested arrays?

Combine UNNEST with JSON_QUERY_ARRAY or JSON_EXTRACT_ARRAY to flatten arrays before filtering.

SELECT o.id, item FROM Orders AS o, UNNEST(JSON_QUERY_ARRAY(shipping_info, '$.tracking_events')) AS item;

How can I update JSON fields?

Use JSON_SET or JSON_INSERT inside an UPDATE statement (Preview feature) to modify specific paths without rewriting the whole document.

UPDATE Orders SET shipping_info = JSON_SET(shipping_info, '$.status', 'delivered') WHERE id = 42;

What best practices speed up JSON queries?

Create materialized views that project heavily used JSON paths, avoid wildcards in JSONPath expressions, and filter early with JSON_VALUE instead of post-filtering.

Common mistakes with JSON in BigQuery

Avoid treating JSON_VALUE results as JSON; they return STRING.Also watch for NULL vs. empty string when a path is missing.

Why How to Parse JSON in BigQuery is important

How to Parse JSON in BigQuery Example Usage


-- Find all orders with a damaged shipment recorded in tracking_events JSON array
SELECT o.id, c.name, o.order_date
FROM   Orders        AS o
JOIN   Customers     AS c ON c.id = o.customer_id
WHERE  EXISTS (
  SELECT 1 FROM UNNEST(JSON_QUERY_ARRAY(o.shipping_info, '$.tracking_events')) AS ev
  WHERE  JSON_VALUE(ev, '$.status') = 'damaged'
);

How to Parse JSON in BigQuery Syntax


-- Extract a scalar (returns STRING)
JSON_VALUE(json_expression, json_path)

-- Extract an object/array (returns JSON)
JSON_QUERY(json_expression, json_path [ { 'WRAPPER' | 'WITHOUT WRAPPER' } ] )

-- Extract raw JSON (alias of JSON_QUERY with WITHOUT WRAPPER)
JSON_EXTRACT(json_expression, json_path)

-- Ecommerce context example
SELECT id,
       JSON_VALUE(shipping_info, '$.carrier')             AS carrier,
       JSON_VALUE(shipping_info, '$.address.city')        AS city,
       JSON_QUERY(shipping_info, '$.tracking_events')     AS events
FROM   Orders;

Common Mistakes

Using JSON_VALUE to return objects or arrays. JSON_VALUE only extracts scalar values; use JSON_QUERY/JSON_EXTRACT for non-scalars to avoid NULL results.
Forgetting to cast JSON_VALUE output. It returns STRING, so comparisons like JSON_VALUE(col,'$.qty') > 5 fail; cast to INT64 first (CAST(JSON_VALUE(...) AS INT64)).