Skip to content
Tutorials Center
My learning

Topics

Lesson 18 of 37 ยท Nested and Repeated Data

JSON and Array Aggregation Patterns in BigQuery

Query semi-structured data in BigQuery with the JSON type: JSON_VALUE, JSON_QUERY, JSON_EXTRACT_ARRAY, TO_JSON_STRING, JSON_OBJECT, plus array patterns for dedup, pivots and sessions, with stand-ins.

Not all data has a fixed schema. Webhooks, app events and third-party APIs send JSON whose fields change from record to record. BigQuery handles this in two ways: structured nesting (STRUCT and ARRAY, as in the last lessons) when you know the shape, and the JSON type when you do not. This lesson covers reading and building JSON, then collects a handful of array patterns you will reuse constantly.

SQLite has JSON functions with similar ideas, so many examples run here. Where a function is BigQuery-only, it is shown read-only with the SQLite equivalent.

STRUCT or JSON?

STRUCT / ARRAY JSON
Schema Fixed, declared Flexible, any shape
Type safety Real types per field Values accessed as strings or numbers
Cost Reads only referenced leaf fields Reads more, and is slower to scan
Best for Known, stable structure Unknown, evolving or sparse fields

A common pattern is to keep the columns you query daily as normal fields and store the rest as one JSON column for later.

Creating and reading JSON

SQL
SELECT
  JSON '{"user": {"id": 7, "plan": "pro"}, "tags": ["a", "b"]}' AS payload;

BigQuery has two families of extraction functions:

Function Returns Use
JSON_VALUE(json, '$.path') A scalar as STRING Reading one leaf value
JSON_QUERY(json, '$.path') A JSON fragment (object or array) Reading a nested part
JSON_VALUE_ARRAY(json, '$.path') ARRAY<STRING> Arrays of scalars
JSON_QUERY_ARRAY(json, '$.path') ARRAY<JSON> Arrays of objects
LAX_INT64(...), INT64(...), FLOAT64(...), BOOL(...) Typed values from JSON Converting JSON to a SQL type
SQL
SELECT
  JSON_VALUE(payload, '$.user.plan')                AS plan,
  INT64(payload.user.id)                            AS user_id,   -- dot access works on the JSON type
  JSON_VALUE_ARRAY(payload, '$.tags')               AS tags
FROM `my-project.app.events`;

On a column of type JSON, you can also use dot and bracket access (payload.user.plan, payload.tags[0]). If a path does not exist, the result is NULL rather than an error. Older tables hold JSON as STRING; use PARSE_JSON(string_col) to convert it.

The SQLite equivalent of JSON_VALUE is json_extract (or the ->> operator):

SQL Stand-in: read values out of JSON text ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Event 3 lacks a plan, tags and an amount. The missing path returns NULL; wrap with COALESCE for defaults. The array length of a missing array is also NULL.

Building JSON

SQL
SELECT
  TO_JSON_STRING(STRUCT(id, name, price))      AS row_as_json_text,
  TO_JSON(STRUCT(id, name))                    AS row_as_json_type,
  JSON_OBJECT('id', id, 'tags', ['a', 'b'])    AS made_json,
  TO_JSON_STRING(t)                            AS whole_row
FROM `my-project.shop.products` AS t;

TO_JSON_STRING(t) serialises an entire row, a quick way to export or log. The portable idea runs here:

SQL Stand-in: build JSON objects from rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Exploding a JSON array

JSON_QUERY_ARRAY plus UNNEST turns a JSON list into rows:

SQL
SELECT
  e.id,
  INT64(item.product_id) AS product_id,
  INT64(item.qty)        AS qty
FROM `my-project.app.events` AS e,
     UNNEST(JSON_QUERY_ARRAY(e.payload, '$.items')) AS item;

SQLite does it with json_each:

SQL Stand-in: JSON array to rows ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Array patterns you will reuse

1. Latest row per key. ARRAY_AGG with ORDER BY and LIMIT 1 picks one record per group without a window function:

SQL
SELECT
  user_id,
  ARRAY_AGG(STRUCT(plan, updated_at) ORDER BY updated_at DESC LIMIT 1)[OFFSET(0)].*
FROM `my-project.crm.plan_changes`
GROUP BY user_id;

2. Top N inside a group.

SQL
SELECT
  category,
  ARRAY_AGG(name ORDER BY price DESC LIMIT 3) AS top_three
FROM `my-project.shop.products`
GROUP BY category;

3. Pivot rows to columns. Conditional aggregation is portable:

SQL Stand-in: pivot with conditional sums ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

4. Funnels and sessions. Collect each user's ordered events into an array and test the sequence with ARRAY_TO_STRING and LIKE/REGEXP_CONTAINS:

SQL
SELECT user_id
FROM (
  SELECT user_id,
         ARRAY_TO_STRING(ARRAY_AGG(event_name ORDER BY event_ts), '>') AS path
  FROM `my-project.app.events`
  GROUP BY user_id
)
WHERE path LIKE '%view>cart>purchase%';

The same funnel idea with group_concat on the stand-in:

SQL Stand-in: the path each customer took ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

5. Compare arrays or find overlaps. ARRAY_INTERSECT-style logic is done with UNNEST and INTERSECT DISTINCT:

SQL
SELECT x FROM UNNEST([1, 2, 3, 4]) AS x
INTERSECT DISTINCT
SELECT y FROM UNNEST([3, 4, 5]) AS y;

Cost note for JSON

A JSON column is stored in an optimised columnar way (each path is stored separately), so reading payload.user.plan does not read the whole payload. A JSON STRING column is much worse: every query must scan and parse the entire text. When you control the load, use the JSON type or promote hot fields to real columns.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which function reads one scalar leaf from JSON and returns a STRING?

    JSON_VALUE returns a scalar as STRING; JSON_QUERY returns a JSON fragment.
  2. 2. What does a missing JSON path return?

    BigQuery returns NULL for missing paths, so you can default with COALESCE.
  3. 3. How do you turn a JSON array into rows?

    JSON_QUERY_ARRAY gives an ARRAY, and UNNEST makes it rows.
  4. 4. Why prefer the JSON type over a STRING column holding JSON text?

    Native JSON avoids scanning and parsing the whole text for each query.

Summary

  • Use STRUCT/ARRAY for known shapes, JSON for flexible ones.
  • JSON_VALUE, JSON_QUERY, JSON_QUERY_ARRAY, PARSE_JSON, TO_JSON_STRING read and build JSON.
  • UNNEST(JSON_QUERY_ARRAY(...)) explodes JSON arrays.
  • ARRAY_AGG with ORDER BY ... LIMIT gives latest-per-key and top-N-per-group patterns.
  • The native JSON type is cheaper to query than JSON stored as text.

Next: Partitioned Tables