# JSON in BigQuery

> BigQuery has a native JSON type with dot access, and JSON_VALUE / JSON_QUERY functions for JSON stored as text. Field access returns JSON, not strings, so it needs a conversion before you compare. Reading one JSON field is billed for that field only.

Source: https://docs.querylab.io/bigquery/json/

Updated: 2026-10-06

BigQuery stores JSON in two ways. A `JSON` column holds parsed JSON that BigQuery encodes field by field, and you read it with dots like `payload.user.id`. A `STRING` column can hold JSON text, which you read with functions like `JSON_VALUE(body, '$.user.id')`. The `JSON` type is cheaper to query and easier to write, but everything you read from it is still JSON, so you convert it before comparing it with a number or a string.

## Reading fields from a JSON column

Use dots for object fields and `[n]` for array elements. A path that doesn't exist returns NULL rather than an error:

**dot access on a JSON value**

```sql
WITH events AS (
  SELECT JSON '{"user": {"id": 42, "plan": "pro"}, "tags": ["a", "b"]}' AS payload
)
SELECT
  payload.user.id AS user_id,
  payload.user.plan AS plan,
  payload.tags[0] AS first_tag,
  payload.missing AS missing
FROM events
```

Result in BigQuery (1 row):

| user_id | plan | first_tag | missing |
| --- | --- | --- | --- |
| 42 | "pro" | "a" | NULL |

Look at `plan`: the result is `"pro"` with quotes. Field access returns a `JSON` value, here a JSON string, not a SQL `STRING`.

## Convert JSON before comparing

Because of that, comparing a JSON field with a SQL value fails:

**payload.plan = 'pro'**

```sql
WITH events AS (SELECT JSON '{"plan": "pro"}' AS payload)
SELECT payload.plan = 'pro' AS is_pro
FROM events
```

BigQuery rejects this query:

```text
No matching signature for operator = for argument types: JSON, STRING
  Signature: T1 = T1
    Unable to find common supertype for templated argument <T1>
      Input types for <T1>: {STRING, JSON} at [2:8]
```

Convert the JSON value to a SQL type first. `STRING()`, `INT64()`, `FLOAT64()` and `BOOL()` convert strictly, the `LAX_` versions also convert between types (a JSON string `"12.50"` to a number), and `SAFE.` turns a failed conversion into NULL:

**STRING, INT64, LAX_FLOAT64 and SAFE.INT64**

```sql
WITH events AS (
  SELECT JSON '{"plan": "pro", "seats": 5, "price": "12.50"}' AS payload
)
SELECT
  STRING(payload.plan) = 'pro' AS is_pro,
  INT64(payload.seats) * 2 AS double_seats,
  LAX_FLOAT64(payload.price) AS price_from_string,
  SAFE.INT64(payload.plan) AS plan_as_int
FROM events
```

Result in BigQuery (1 row):

| is_pro | double_seats | price_from_string | plan_as_int |
| --- | --- | --- | --- |
| true | 10 | 12.5 | NULL |

The strict functions fail when the JSON type doesn't match. A price stored as a JSON string is not a number:

**FLOAT64 on a JSON string**

```sql
WITH events AS (SELECT JSON '{"price": "12.50"}' AS payload)
SELECT FLOAT64(payload.price) AS price
FROM events
```

BigQuery rejects this query:

```text
The provided JSON input is not a number; error in FLOAT64 expression
```

`JSON` values also can't be grouped, sorted or joined on. Convert the field first:

**GROUP BY a JSON field**

```sql
WITH t AS (SELECT JSON '{"plan": "pro"}' AS payload)
SELECT payload.plan, COUNT(*) AS n
FROM t
GROUP BY payload.plan
```

BigQuery rejects this query:

```text
Grouping by expressions of type JSON is not allowed at [4:10]
```

## JSON_VALUE vs JSON_QUERY

The path functions work on both `JSON` and `STRING` input:

**the same paths through both functions**

```sql
WITH events AS (
  SELECT JSON '{"user": {"id": 42, "plan": "pro"}}' AS payload
)
SELECT
  JSON_VALUE(payload, '$.user.plan') AS json_value_plan,
  JSON_QUERY(payload, '$.user.plan') AS json_query_plan,
  JSON_VALUE(payload, '$.user') AS json_value_object,
  JSON_QUERY(payload, '$.user') AS json_query_object
FROM events
```

Result in BigQuery (1 row):

| json_value_plan | json_query_plan | json_value_object | json_query_object |
| --- | --- | --- | --- |
| pro | "pro" | NULL | {"id":42,"plan":"pro"} |

- `JSON_VALUE` returns a scalar as a SQL `STRING`, without quotes. For an object or array it returns NULL.
- `JSON_QUERY` returns the JSON at that path, objects and arrays included. Its result is `JSON` for `JSON` input and a JSON-formatted `STRING` for `STRING` input.

So `JSON_VALUE` is for pulling out a value to compare or cast, and `JSON_QUERY` is for passing a piece of JSON on. On a `JSON` column, `STRING(payload.user.plan)` and `JSON_VALUE(payload, '$.user.plan')` give the same result.

## JSON stored as text

Older tables often keep raw JSON in a `STRING` column. The same functions read it:

**JSON paths on a STRING column**

```sql
WITH raw AS (
  SELECT '{"user": {"id": 42, "plan": "pro"}, "items": [{"sku": "a"}, {"sku": "b"}]}' AS body
)
SELECT
  JSON_VALUE(body, '$.user.plan') AS plan,
  JSON_VALUE(body, '$.items[1].sku') AS second_sku,
  ARRAY_LENGTH(JSON_QUERY_ARRAY(body, '$.items')) AS item_count
FROM raw
```

Result in BigQuery (1 row):

| plan | second_sku | item_count |
| --- | --- | --- |
| pro | b | 2 |

`JSON_QUERY_ARRAY` returns a JSON array as a SQL array, which you can measure or unnest. To get one row per element:

**one row per item in a JSON array**

```sql
WITH orders AS (
  SELECT 'o1' AS order_id, JSON '{"items": [{"sku": "apple", "qty": 3}, {"sku": "pear", "qty": 1}]}' AS payload
)
SELECT
  order_id,
  JSON_VALUE(item, '$.sku') AS sku,
  LAX_INT64(item.qty) AS qty
FROM orders, UNNEST(JSON_QUERY_ARRAY(payload, '$.items')) AS item
ORDER BY sku
```

Result in BigQuery (2 rows):

| order_id | sku | qty |
| --- | --- | --- |
| o1 | apple | 3 |
| o1 | pear | 1 |

`JSON_VALUE_ARRAY` does the same for an array of scalars and returns `ARRAY<STRING>`.

## JSON null vs a missing field

A field that holds JSON `null` and a field that isn't there are different. The missing field is SQL NULL. The JSON `null` is a JSON value, so `IS NULL` is false:

**{"a": null} vs no b at all**

```sql
WITH t AS (
  SELECT JSON '{"a": null}' AS payload
)
SELECT
  payload.a IS NULL AS sql_null_a,
  JSON_TYPE(payload.a) AS type_of_a,
  payload.b IS NULL AS sql_null_b
FROM t
```

Result in BigQuery (1 row):

| sql_null_a | type_of_a | sql_null_b |
| --- | --- | --- |
| false | null | true |

Use `JSON_TYPE(x) = 'null'` to find JSON nulls, or convert first: `LAX_STRING(payload.a) IS NULL` is true for both.

## Building JSON

`TO_JSON` turns any SQL value into `JSON`, `TO_JSON_STRING` into JSON text, `JSON_OBJECT` and `JSON_ARRAY` build from parts, and `JSON_SET`, `JSON_REMOVE` and friends edit a value:

**four ways to produce JSON**

```sql
SELECT
  TO_JSON(STRUCT('o1' AS id, [1, 2] AS lines)) AS as_json,
  TO_JSON_STRING(STRUCT('o1' AS id, [1, 2] AS lines)) AS as_text,
  JSON_OBJECT('id', 'o1', 'paid', TRUE) AS built,
  JSON_SET(JSON '{"a": 1}', '$.b', 2) AS with_b
```

Result in BigQuery (1 row):

| as_json | as_text | built | with_b |
| --- | --- | --- | --- |
| {"id":"o1","lines":[1,2]} | {"id":"o1","lines":[1,2]} | {"id":"o1","paid":true} | {"a":1,"b":2} |

## Parsing

`PARSE_JSON` turns text into `JSON` and is strict. A trailing comma, single quotes or comments make it fail:

**a trailing comma**

```sql
SELECT PARSE_JSON('{"a": 1,}') AS bad
```

BigQuery rejects this query:

```text
Invalid input: syntax error while parsing object key - unexpected '}'; expected string literal; error in PARSE_JSON expression
```

Use `SAFE.PARSE_JSON` to get NULL for bad input instead.

`JSON` stores numbers exactly only if they fit a 64-bit integer (signed or unsigned) or a `FLOAT64`. Any other number, such as `1.23456789012345678901` or a 21-digit integer, is an error by default. `wide_number_mode => 'round'` accepts it rounded to a `FLOAT64` instead. A number that already fits is kept as it is, so this 20-digit id, which fits an unsigned 64-bit integer, keeps every digit:

**a 20-digit id that fits**

```sql
SELECT
  PARSE_JSON('{"id": 12345678901234567890}', wide_number_mode => 'round') AS rounded
```

Result in BigQuery (1 row):

| rounded |
| --- |
| {"id":12345678901234567890} |

An id one digit longer would be rounded. Store long IDs as JSON strings if you need every digit.

## What JSON costs

BigQuery encodes each field of a `JSON` column separately, and reading one field is billed for that field. We tested it on 10,000 rows of `{"a": <number>, "b": <200 characters>}`:

| Query                                             | Dry-run estimate     | Bytes processed when run |
| ------------------------------------------------- | -------------------- | ------------------------ |
| `SELECT payload` (JSON column)                    | 2.0 MB               | 2.0 MB                   |
| `SELECT payload.a` (JSON column)                  | 2.0 MB (upper bound) | 78 KB                    |
| `JSON_VALUE(payload_text, '$.a')` (STRING column) | 2.1 MB               | 2.1 MB                   |

Two things follow. The dry-run estimate for a `JSON` column is an upper bound, the whole column, even when the query reads one field; the bill can be much smaller. And JSON kept as text is read in full every time, whatever path you extract. If you query a few fields of large JSON documents, store them as `JSON`, or extract those fields into their own columns at load time.

## Limits

- Nesting is limited to 500 levels.
- Row-level access policies can't be applied to `JSON` columns.
- A batch load can fill a `JSON` column only from CSV, Avro or newline-delimited JSON files.

See [STRUCT](https://docs.querylab.io/bigquery/structs/) for data with a fixed shape, which is checked on write and cheaper to read than either JSON form.

Pull a field out of JSON with JSON_VALUE and JSON_QUERY. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Work with JSON data](https://cloud.google.com/bigquery/docs/json-data)

- [JSON functions](https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions)

- [Data types, JSON type](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#json_type)