How to flatten json for bigquery schema design
- Step 1Export the nested JSON from Firestore or your API — Get a representative sample of documents — 10–100 records — that covers the full variety of nested fields your documents contain. Rare nested fields that do not appear in the sample will not be represented in the flattened schema.
- Step 2Flatten with underscore separator — Choose '_' as the key separator. BigQuery column names must not contain dots, so underscore is the correct separator for flat BigQuery schemas.
- Step 3Review the flat key set for your schema — The flattened keys are your BigQuery column names. Review for any keys that are too verbose (e.g. event_properties_page_properties_utm_campaign) and consider truncating or renaming using the JSON Key Renamer tool.
- Step 4Load the flat JSON to BigQuery — Upload the flattened JSON as a newline-delimited JSON file (NDJSON) to a GCS bucket, then run bq load --source_format=NEWLINE_DELIMITED_JSON --autodetect myproject:mydataset.mytable gs://bucket/flat.ndjson.
Frequently asked questions
Should I always prefer a flat schema over nested RECORD types in BigQuery?+
For analytical workloads with aggregations and GROUP BY, flat schemas are simpler. For event data with deeply variable properties or semi-structured payloads, BigQuery's JSON column type (GA4 BigQuery export uses this) or STRUCT types preserve the original structure more efficiently. Use flat schemas for known, stable field sets; use STRUCT or JSON for highly variable payloads.
How do I handle arrays of objects in the JSON for BigQuery?+
Arrays of objects (like a nested line_items array on an order) should be stored in BigQuery as ARRAY<STRUCT> types — a REPEATED RECORD field. These can not be meaningfully flattened to single-depth without creating a separate table. Keep array-of-object fields as JSON strings in a single column or model them as a separate BigQuery table with a foreign key to the parent.
Is the Firestore or API data sent to JAD Apps?+
No. Flattening runs entirely in your browser. Firestore document data, event payloads, and any PII are never transmitted to JAD Apps servers.
Privacy first
Conversion runs locally in your browser. No file is uploaded — only metadata counters are saved for signed-in dashboard stats.