How to Query JSON in BigQuery
Last updated July 26, 2026 · By the SaturnSQL team
JSON_VALUE extracts a scalar as a string, JSON_QUERY extracts a JSON fragment, and the native JSON type lets you use dot and bracket access directly.
SELECT
JSON_VALUE(payload, '$.user.email') AS user_email,
JSON_QUERY(payload, '$.items') AS items_json
FROM events
WHERE payload IS NOT NULL;Native JSON type access
If the column is declared as JSON rather than STRING, you can skip the function calls and use path syntax directly, which also preserves types instead of returning everything as a string.
SELECT payload.user.email AS user_email,
INT64(payload.item_count) AS item_count
FROM events
WHERE payload IS NOT NULL;JSON_EXTRACT_SCALAR and JSON_EXTRACT are the older names for JSON_VALUE and JSON_QUERY; both pairs still work, but Google recommends the newer names for new code.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free