How to PARSE_JSON in BigQuery

What is PARSE_JSON in BigQuery?

PARSE_JSON converts a JSON-formatted STRING into a native JSON value, allowing dot-notation and JSON functions for fast, repeated access.

How do I write the PARSE_JSON syntax?

Use PARSE_JSON(string_expr) or SAFE.PARSE_JSON(string_expr). The safe variant returns NULL instead of an error when the input is invalid.

Basic syntax example

SELECT PARSE_JSON('{"id":1,"name":"Widget"}') AS product_json;

How can I parse JSON columns in an e-commerce database?

Assume Orders.order_details is a STRING. Parse once, then access nested fields.

SELECT
details.customer_id,
details.items[OFFSET(0)].product_id AS first_item
FROM (
SELECT PARSE_JSON(order_details) AS details FROM Orders)
WHERE order_date > CURRENT_DATE() - 30;

When should I use PARSE_JSON vs JSON_EXTRACT?

Choose PARSE_JSON to treat the whole document as JSON for multiple field reads or storage. Use JSON_EXTRACT(_SCALAR) for a single, ad-hoc pull from a STRING.

Best practices for parsing JSON

• Validate with SAFE.PARSE_JSON to keep pipelines running.
• Store raw JSON in staging, then cast to typed columns for analytics.
• Create views that expose parsed fields to simplify downstream SQL.
• Only parse fields you need to reduce scan costs.

Common PARSE_JSON mistakes and fixes

Passing non-STRING input

BigQuery errors on BYTES or JSON types. Cast: PARSE_JSON(CAST(raw AS STRING)).

Ignoring SAFE mode

Malformed JSON stops the query. Use SAFE.PARSE_JSON and handle NULLs with IFNULL.

Additional resources

Google Cloud docs: PARSE_JSON.