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.

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:

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

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

JSON is not a string: convert before comparing

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

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

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:

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

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

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

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

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

JSON_VALUE vs JSON_QUERY

The path functions work on both JSON and STRING input:

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

• 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:

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

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

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

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:

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

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:

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

Parsing

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

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

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:

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

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>}:

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 for data with a fixed shape, which is checked on write and cheaper to read than either JSON form.