BigQuery JSON functions: the JSON type, JSON_SET, JSON_REMOVE and more
BigQuery stores JSON in a native JSON data type and has functions to read, convert, change and build it. This guide runs them on one document and shows the result: dot access, strict and lax conversion, JSON_SET, JSON_REMOVE, JSON_STRIP_NULLS, JSON_ARRAY_APPEND, JSON_OBJECT, JSON_KEYS and JSON_TYPE, plus how to turn a JSON array into rows, since BigQuery has no JSON_TABLE.
BigQuery has a native JSON data type. A JSON column stores the document parsed, so you can read a field with dot notation, payload.user.plan, change it with JSON_SET, and convert it to a SQL type with INT64 or LAX_INT64. Older JSON functions work on strings that hold JSON; this page is about the type and the functions built for it.
| To | Use |
|---|---|
| turn a string into JSON | PARSE_JSON(s) |
| read a field | j.user.plan or j['user']['plan'] |
| read it as a SQL value | STRING(...), INT64(...), FLOAT64(...), BOOL(...) |
| read it forgivingly | LAX_STRING, LAX_INT64, LAX_FLOAT64, LAX_BOOL |
| change a value | JSON_SET |
| remove a key | JSON_REMOVE |
| drop NULLs | JSON_STRIP_NULLS |
| add to an array | JSON_ARRAY_APPEND |
| build JSON | JSON_OBJECT, JSON_ARRAY, TO_JSON |
| list the keys | JSON_KEYS |
| check a type | JSON_TYPE |
| array to rows | UNNEST(JSON_QUERY_ARRAY(...)) |
Extracting values from JSON strings, with JSON_VALUE and JSON_QUERY, and the “path must be a constant expression” error are covered in the JSON parsing and conversion guide.
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 document used in every example
The document used in every example
WITH t AS (
SELECT PARSE_JSON('''{
"user": {"id": 7, "plan": "pro", "tags": ["a", "b"]},
"amount": "12.50",
"coupon": null
}''') AS j
)Note that amount is a string, "12.50", not a number. Data from APIs and event trackers often looks like this.
Read a field
| Expression | Result | Type |
|---|---|---|
j.user.plan |
“pro” | JSON |
j['user']['tags'][1] |
“b” | JSON |
JSON_VALUE(j, '$.user.plan') |
pro | STRING |
INT64(j.user.id) |
7 | INT64 |
JSON_TYPE(j.amount) |
string | STRING |
Dot access returns JSON, not a SQL value: you cannot sum it or compare it to a number yet. Convert it with one of the type functions first. Array positions start at 0, so [1] is the second tag. JSON_TYPE tells you what a field holds before you convert it.
A JSON column holds the fields you report on, and you need to read, change or strip them without breaking the document.
Paste this into your AI assistant. It reads the JSON columns in your BigQuery tables and writes the SQL that extracts, converts or changes the fields you need.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/bigquery-json-data-manipulation-functions.md. The task: write me BigQuery SQL for the JSON column in my table: pull the fields I report on into typed columns, use lax conversion where values are messy, and strip the nulls. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Strict or lax conversion
amount is a JSON string, so the strict converters refuse it:
| Expression | Result |
|---|---|
INT64(PARSE_JSON('{"qty":"3"}').qty) |
error: “The provided JSON input is not an integer” |
SAFE.FLOAT64(j.amount) |
NULL |
LAX_FLOAT64(j.amount) |
12.5 |
- Strict (
INT64,FLOAT64,STRING,BOOL) fails the query if the JSON type is wrong. Use it when the data is clean and you want to know when it is not. SAFE.in front returns NULL instead of failing. The value is lost silently.- Lax (
LAX_INT64and the rest) converts what it reasonably can: the string “12.50” becomes 12.5, and “3” becomes 3.
For event data where the same field arrives as a number from one source and a string from another, the lax functions are usually what you want. Count the NULLs they still return, so a value that could not be read does not disappear unnoticed.
Change a document
| Expression | Result |
|---|---|
JSON_SET(j, '$.user.plan', 'team') |
{"amount":"12.50","coupon":null,"user":{"id":7,"plan":"team","tags":["a","b"]}} |
JSON_REMOVE(j, '$.coupon') |
{"amount":"12.50","user":{"id":7,"plan":"pro","tags":["a","b"]}} |
JSON_STRIP_NULLS(j) |
{"amount":"12.50","user":{"id":7,"plan":"pro","tags":["a","b"]}} |
JSON_ARRAY_APPEND(j, '$.user.tags', 'c') |
...,"tags":["a","b","c"]}} |
Each returns a new JSON value; the column is not changed until you write it back with UPDATE. JSON_REMOVE drops a key you name; JSON_STRIP_NULLS drops every key whose value is null. Both are useful before sending a document on to a system that rejects nulls.
Build JSON
| Expression | Result |
|---|---|
JSON_OBJECT('id', 7, 'plan', 'pro') |
{"id":7,"plan":"pro"} |
JSON_ARRAY(1, 'two', NULL) |
[1,"two",null] |
JSON_KEYS(j) |
["amount","coupon","user","user.id","user.plan","user.tags"] |
JSON_KEYS lists nested keys too, by their path. That is a quick way to see what a column of semi-structured events actually contains before you write a query on it. TO_JSON(t) turns a whole row or struct into JSON, and TO_JSON_STRING gives it as text.
A JSON array to rows
BigQuery has no JSON_TABLE; a query using it fails with a syntax error. The equivalent is JSON_QUERY_ARRAY and UNNEST:
SELECT
JSON_VALUE(item, '$.sku') AS sku,
LAX_INT64(item.qty) AS qty
FROM UNNEST(JSON_QUERY_ARRAY(
PARSE_JSON('{"items":[{"sku":"A1","qty":2},{"sku":"B7","qty":"3"}]}'),
'$.items')) AS item;| sku | qty |
|---|---|
| A1 | 2 |
| B7 | 3 |
The second item has qty as a string, and LAX_INT64 still reads it. Arrays and UNNEST in general are in the array functions guide, and STRUCT and ARRAY types in JSON objects and arrays.
Getting JSON into BigQuery
Newline-delimited JSON files load straight into a table, and a column can be declared as JSON in the schema. The routes and their limits are in loading data into BigQuery, and the schema options in the BigQuery schema guide.
JSON or columns
JSON is the right type for data whose shape changes: API responses, event parameters, settings. It is the wrong place to leave the fields every report uses. Each query that reads payload.order.amount has to convert it, handle the string case, and decide what a missing value means, and two queries will decide differently.
Extract those fields into typed columns once, in one query, and let reports read the columns. In OWOX Data Marts that query is a Data Mart with a description on each extracted column, so the conversion rules are written down where people see them. OWOX Data Marts runs the SQL as written, in your own BigQuery project, and does not transform the data.
A JSON column holds the fields you report on, and you need to read, change or strip them without breaking the document.
Paste this into your AI assistant. It reads the JSON columns in your BigQuery tables and writes the SQL that extracts, converts or changes the fields you need.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /blog/articles/bigquery-json-data-manipulation-functions.md. The task: write me BigQuery SQL for the JSON column in my table: pull the fields I report on into typed columns, use lax conversion where values are messy, and strip the nulls. Tag the links you give me with utm_campaign=ai-task and utm_term=data-foundation. Use what you know about my context and goals
Prefer to start on your own? Get started free
Frequently Asked Questions
How do I change a value inside a JSON column in BigQuery?
JSON_SET(j, '$.user.plan', 'team') returns the document with that value replaced; JSON_REMOVE drops a key and JSON_ARRAY_APPEND adds to an array. Each returns a new value, so write it back with UPDATE to change the stored column.
What is the difference between INT64 and LAX_INT64 on JSON in BigQuery?
INT64 is strict: if the JSON value is the string "3" rather than the number 3, the query fails with 'The provided JSON input is not an integer'. LAX_INT64 converts it to 3. SAFE.INT64 returns NULL instead of failing.
Does BigQuery have JSON_TABLE?
No. A query with JSON_TABLE fails with a syntax error. To turn a JSON array into rows, use UNNEST(JSON_QUERY_ARRAY(j, '$.items')) and read each element with JSON_VALUE or a LAX_ function.
How do I see which keys a JSON column contains?
JSON_KEYS(j) returns every key as an array, nested ones by their path, for example user.plan. JSON_TYPE(j.field) returns the type of a value: object, array, string, number, boolean or null.
Can I query nested JSON in BigQuery?
Yes. With the JSON type, use dot or bracket access: j.user.plan or j['user']['tags'][1], where array positions start at 0. The result is JSON; convert it with STRING, INT64, FLOAT64 or BOOL before comparing or summing.
How does BigQuery store JSON?
In a native JSON data type: the document is parsed when written, so fields can be read with dot notation. PARSE_JSON turns a string into JSON, and TO_JSON_STRING turns JSON back into text.
What are JSON manipulation functions in BigQuery?
Functions that change or build JSON values: JSON_SET, JSON_REMOVE, JSON_STRIP_NULLS, JSON_ARRAY_APPEND, JSON_OBJECT, JSON_ARRAY and TO_JSON. Reading values out of JSON is done with field access, JSON_VALUE and the type conversion functions.
