Skip to content
All articles

Ingesting JSON into BigQuery: 5 Data Modeling Strategies

Dean Jain

Dean Jain

Senior Staff Software Engineer · Enterprise AI, Data & Cloud Architect

· 6 min read

BigQueryData ModelingJSON
---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart LR
    P["⚡ Max performance"]:::good --- F["3 · Flatten"]:::danger
    F --- S["2 · STRUCT/ARRAY"]:::warn
    S --- H["4 · Hybrid"]:::server
    H --- J["1 · JSON column"]:::obs
    J --- V["5 · Raw + Views"]:::gate
    V --- R["🛡️ Max resilience"]:::good
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A
    classDef danger fill:#FFB3B3,stroke:#D14545,stroke-width:2px,color:#0F172A
    classDef warn fill:#FFE6A8,stroke:#E0A106,stroke-width:2px,color:#0F172A
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef obs fill:#AED6F1,stroke:#2E86C1,stroke-width:2px,color:#0F172A
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A

Figure 1: The five strategies on one axis every choice trades query performance against resilience to schema change.

Loading JSON into BigQuery sounds trivial until the JSON changes shape next week and your pipeline breaks. The root issue is a data model impedance mismatch: JSON is semi-structured and free to evolve, BigQuery is a columnar relational warehouse that loves fixed schemas. There are five well-worn ways to bridge that gap, and they all sit on a single spectrum how easy is it to query versus how well it survives the JSON changing. Pick wrong and you either fight schema migrations forever or pay a query-performance tax on every dashboard.

Summary

  • It’s one trade-off, five points on a line: performance ⟷ resilience. Structured columns query fast but break on schema change; raw JSON survives anything but queries slower.
  • 1 · Raw JSON column (STRING or native JSON) bulletproof against schema change, query with dot notation/JSON functions. The modern default for volatile data.
  • 2 · STRUCT/ARRAY mirror the JSON natively. Excellent query performance and full fidelity; you own schema evolution.
  • 3 · Flatten to a wide table fastest, most brittle. Avoid if the schema will move.
  • 4 · Hybrid extract the hot fields into typed columns, keep the full JSON as a fallback. The best general-purpose answer.
  • 5 · Raw + Views store one JSON column, expose fields through a view you edit as the JSON evolves. Great for exploration and rapidly changing schemas.

The impedance mismatch

Relational tables want every row to look the same: fixed columns, fixed types. JSON makes no such promise a field can appear, vanish, nest deeper, or turn from a scalar into an array between one payload and the next. Forcing that fluid shape into rigid columns is the impedance mismatch, and each strategy below is just a different answer to “how much structure do I impose at load time versus query time?” Impose it early (flatten) and queries fly but the pipeline is fragile; defer it (raw JSON) and the pipeline is unbreakable but queries do more work.

1. Store the entire JSON in one column

Load each object as a single value into a table with a key/timestamp plus the full payload as a STRING, or better, the native JSON type.

  • Resilience: excellent. Any structural change added, removed, or re-nested fields needs no schema update. The raw JSON is always preserved.
  • Querying: with the JSON type you use dot notation (SELECT json_data.user.name); with STRING you use functions like JSON_EXTRACT_SCALAR/JSON_QUERY verbose but functional.
  • Cost: less intuitive and generally slower than native columns for heavy filtering/aggregation on nested fields (though BigQuery keeps optimizing the JSON type).

This is the most modern, recommended approach when the structure is unpredictable and you value a dead-simple ingestion pipeline.

2. Use native STRUCT and ARRAY

Define a schema that mirrors the JSON STRUCT for objects, ARRAY for lists and load into it.

  • Querying: very good. Dot notation for structs (SELECT user.address.city), UNNEST for arrays, and excellent performance because everything is native and columnar.
  • Resilience: moderate. BigQuery supports schema evolution add new NULLABLE fields, relax REQUIRED to NULLABLE. But changing a field’s type, removing a field, or changing nesting (scalar ↔ array) is disruptive and may need a new table.

Reach for this when the JSON schema is relatively stable and you want full-fidelity nested data in native types and you accept owning the schema evolution.

3. Flatten into a wide table

Parse the JSON and map every leaf node to its own column, pre-processing before load.

  • Querying: best. Plain columns, full columnar optimization, simplest SQL.
  • Resilience: poor. Highly brittle any structural change likely breaks your pre-processing and the schema. Complex JSON produces very wide tables, and arrays force data duplication or gnarly pre-processing.

Only safe when the schema is frozen. If change is expected, avoid it.

4. Hybrid: extract hot fields, keep raw JSON

The pragmatic favorite. Identify the critical, frequently-queried fields, extract them into dedicated typed columns, and store the entire original JSON in a separate STRING/JSON column as a fallback.

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart LR
    J["📦 Incoming JSON"]:::obs --> E["✂️ Extract hot fields"]:::gate
    E --> C1["id (INT)"]:::good
    E --> C2["status (STRING)"]:::good
    J --> RAW["🗄️ raw_json (full payload)"]:::server
    classDef obs fill:#AED6F1,stroke:#2E86C1,stroke-width:2px,color:#0F172A
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A

Figure 2: Hybrid typed columns for the fields you query constantly, plus the full JSON for everything else.

  • Querying: good. The hot fields are fast native columns; everything else is reachable in the raw column via JSON functions/dot notation.
  • Resilience: good. Changes to fields living only in the raw JSON don’t touch your defined columns; only changes to the few extracted fields need schema evolution and the full payload is always safe.

This gives you the best performance where it matters without sacrificing the safety net for the volatile, rarely-queried rest.

5. Raw + Views

Ingest the JSON as-is into a single json_data column, then expose fields through a view using JSON functions:

CREATE VIEW myview AS
SELECT JSON_VALUE(json_data, '$.id')   AS id,
       JSON_VALUE(json_data, '$.name') AS name,
       JSON_VALUE(json_data, '$.age')  AS age
FROM mytable;

Querying SELECT * FROM myview now returns a clean, structured result, and the base table never needs a schema change.

  • Strengths: simple, schema-free ingestion and total flexibility when a field is added, you just edit the view (CREATE OR REPLACE VIEW … add email …); no table migration. Removed fields return NULL automatically.
  • Weaknesses: the view parses JSON on every query, which is slower and costlier on large data, and those fields miss columnar storage stats/optimization. Nested structures and inconsistent types (e.g. age sometimes string, sometimes number) make view maintenance fiddly.

Ideal for rapidly changing schemas and exploratory analysis where you’re still learning the JSON’s shape.

The five at a glance

#StrategyQuery perfResilienceBest when
1Raw JSON columnModerateExcellentStructure changes unpredictably; want simple ingestion
2STRUCT/ARRAYExcellentModerateSchema stable; want native nested fidelity
3Flatten wideBestPoorSchema frozen; max query speed
4HybridGood (hot fields)GoodA few hot fields + volatile remainder
5Raw + ViewsLowerExcellentRapidly changing schema; exploration

Figure 3: The five strategies and the conditions each one is built for.

How to choose

---
config:
  theme: dark
  fontSize: 17
  themeVariables:
    fontFamily: "Comic Sans MS, Comic Neue, Chalkboard SE, cursive"
---
flowchart TD
    Q1{"Schema stable<br/>&amp; predictable?"}:::gate -->|"yes"| Q2{"Frozen forever?"}:::gate
    Q2 -->|"yes"| FLAT["3 · Flatten"]:::danger
    Q2 -->|"no"| STRUCT["2 · STRUCT/ARRAY"]:::warn
    Q1 -->|"no"| Q3{"A few hot fields<br/>need top perf?"}:::gate
    Q3 -->|"yes"| HYB["4 · Hybrid"]:::server
    Q3 -->|"no"| Q4{"Still exploring<br/>the shape?"}:::gate
    Q4 -->|"yes"| VIEW["5 · Raw + Views"]:::obs
    Q4 -->|"no"| JSON["1 · JSON column"]:::good
    classDef gate fill:#D7C3F2,stroke:#8E5BD0,stroke-width:2px,color:#0F172A
    classDef danger fill:#FFB3B3,stroke:#D14545,stroke-width:2px,color:#0F172A
    classDef warn fill:#FFE6A8,stroke:#E0A106,stroke-width:2px,color:#0F172A
    classDef server fill:#A8E6D0,stroke:#2FA37C,stroke-width:2px,color:#0F172A
    classDef obs fill:#AED6F1,stroke:#2E86C1,stroke-width:2px,color:#0F172A
    classDef good fill:#BFEFC8,stroke:#3FA34D,stroke-width:2px,color:#0F172A

Figure 4: A decision tree most production tables land on Hybrid (4) or the native JSON column (1).

For most teams the answer is the native JSON column (1) or the Hybrid approach (4), depending on whether you have a clear set of performance-critical fields. Both keep the raw payload safe; both query well enough. Use STRUCT/ARRAY (2) only when the schema is genuinely stable, and avoid flattening (3) whenever change is on the horizon.

On ingestion itself: batch-load newline-delimited JSON from GCS (BigQuery can auto-detect or take your schema), or stream individual records for real-time data (you specify the streaming schema). The strategy you picked above dictates how the load maps into the table.

Why it matters: there’s no universally “right” way to put JSON in BigQuery there’s the right point on the performance-vs-resilience line for your data’s volatility and your query patterns. Default to keeping the raw JSON (strategy 1 or 4) so a surprise schema change never takes down your pipeline, and only harden into rigid columns for the fields whose query speed you can’t compromise.

References