Home
BigQuery Schema

JSON to Google BigQuery Table Schema Generator

RECORD & NDJSON

Infer and convert JSON objects into Google Cloud BigQuery Table Schema JSON with RECORD mode, STRING, INTEGER, NUMERIC, and TIMESTAMP fields.

Presets:
BigQuery Schema & Ingestion Optionsecommerce_dw.fact_order_events
Input JSON / NDJSON / JSONL Payload
Lines: 55•Format: Standard JSON•Payload: 1 Row(s)
1,443 Chars: • 1.4 KB
1[
2  {
3    "name": "event_id",
4    "type": "STRING",
5    "mode": "REQUIRED",
6    "description": "Field representing event id"
7  },
8  {
9    "name": "order_id",
10    "type": "STRING",
11    "mode": "REQUIRED",
12    "description": "Field representing order id"
13  },
14  {
15    "name": "order_status",
16    "type": "STRING",
17    "mode": "REQUIRED",
18    "description": "Field representing order status"
19  },
20  {
21    "name": "event_timestamp",
22    "type": "TIMESTAMP",
23    "mode": "REQUIRED",
24    "description": "Timestamp indicating when event timestamp occurred"
25  },
26  {
27    "name": "order_date",
28    "type": "DATE",
29    "mode": "REQUIRED",
30    "description": "Calendar date for order date"
31  },
32  {
33    "name": "subtotal_amount",
34    "type": "NUMERIC",
35    "mode": "REQUIRED",
36    "description": "Monetary or numeric measure for subtotal amount"
37  },
38  {
39    "name": "tax_amount",
40    "type": "NUMERIC",
41    "mode": "REQUIRED",
42    "description": "Monetary or numeric measure for tax amount"
43  },
44  {
45    "name": "discount_amount",
46    "type": "INT64",
47    "mode": "REQUIRED",
48    "description": "Integer count or identifier for discount amount"
49  },
50  {
51    "name": "grand_total",
52    "type": "NUMERIC",
53    "mode": "REQUIRED",
54    "description": "Monetary or numeric measure for grand total"
55  },
56  {
57    "name": "currency",
58    "type": "STRING",
59    "mode": "REQUIRED",
60    "description": "Field representing currency"
61  },
62  {
63    "name": "is_expedited",
64    "type": "BOOL",
65    "mode": "REQUIRED",
66    "description": "Boolean indicator flag for is expedited"
67  },
68  {
69    "name": "customer",
70    "type": "RECORD",
71    "mode": "REQUIRED",
72    "description": "Nested struct containing customer attributes",
73    "fields": [
74      {
75        "name": "customer_id",
76        "type": "STRING",
77        "mode": "REQUIRED",
78        "description": "Field representing customer id"
79      },
80      {
81        "name": "email",
82        "type": "STRING",
83        "mode": "REQUIRED",
84        "description": "Field representing email"
85      },
86      {
87        "name": "loyalty_tier",
88        "type": "STRING",
89        "mode": "REQUIRED",
90        "description": "Field representing loyalty tier"
91      },
92      {
93        "name": "account_created_at",
94        "type": "TIMESTAMP",
95        "mode": "REQUIRED",
96        "description": "Timestamp indicating when account created at occurred"
97      }
98    ]
99  },
100  {
101    "name": "shipping_address",
102    "type": "RECORD",
103    "mode": "REQUIRED",
104    "description": "Nested struct containing shipping address attributes",
105    "fields": [
106      {
107        "name": "recipient_name",
108        "type": "STRING",
109        "mode": "REQUIRED",
110        "description": "Field representing recipient name"
111      },
112      {
113        "name": "street_address",
114        "type": "STRING",
115        "mode": "REQUIRED",
116        "description": "Field representing street address"
117      },
118      {
119        "name": "city",
120        "type": "STRING",
121        "mode": "REQUIRED",
122        "description": "Field representing city"
123      },
124      {
125        "name": "state_code",
126        "type": "STRING",
127        "mode": "REQUIRED",
128        "description": "Field representing state code"
129      },
130      {
131        "name": "postal_code",
132        "type": "STRING",
133        "mode": "REQUIRED",
134        "description": "Field representing postal code"
135      },
136      {
137        "name": "country_code",
138        "type": "STRING",
139        "mode": "REQUIRED",
140        "description": "Field representing country code"
141      }
142    ]
143  },
144  {
145    "name": "line_items",
146    "type": "RECORD",
147    "mode": "REPEATED",
148    "description": "Repeated array of line items entries",
149    "fields": [
150      {
151        "name": "item_id",
152        "type": "STRING",
153        "mode": "REQUIRED",
154        "description": "Field representing item id"
155      },
156      {
157        "name": "sku",
158        "type": "STRING",
159        "mode": "REQUIRED",
160        "description": "Field representing sku"
161      },
162      {
163        "name": "product_name",
164        "type": "STRING",
165        "mode": "REQUIRED",
166        "description": "Field representing product name"
167      },
168      {
169        "name": "quantity",
170        "type": "INT64",
171        "mode": "REQUIRED",
172        "description": "Integer count or identifier for quantity"
173      },
174      {
175        "name": "unit_price",
176        "type": "NUMERIC",
177        "mode": "REQUIRED",
178        "description": "Monetary or numeric measure for unit price"
179      },
180      {
181        "name": "category",
182        "type": "STRING",
183        "mode": "REQUIRED",
184        "description": "Field representing category"
185      }
186    ]
187  },
188  {
189    "name": "tracking_tags",
190    "type": "STRING",
191    "mode": "REPEATED",
192    "description": "Repeated array of tracking tags entries"
193  },
194  {
195    "name": "warehouse_location",
196    "type": "GEOGRAPHY",
197    "mode": "REQUIRED",
198    "description": "Geospatial coordinates for warehouse location"
199  },
200  {
201    "name": "cancellation_reason",
202    "type": "STRING",
203    "mode": "NULLABLE",
204    "description": "Field representing cancellation reason"
205  }
206]
Columns: 33•STRUCTs: 3•ARRAYs: 2•
5,062 Chars: • 4.9 KB
Schema Fields & Columns33 columns (30 required)
Hierarchy & Struct NestingLevel 0 depth (3 nested records)
Field Modes & NullabilityPartition: event_timestamp • Standard SQL

100% In-Browser BigQuery Schema Inference

Zero server uploads or cloud API calls. Complex JSON trees, deep structs, and NDJSON streaming batches are parsed directly within browser memory.

Production BigQuery Type System & Modes

Accurately maps JSON primitives to standard SQL INT64, NUMERIC, TIMESTAMP, DATE, GEOGRAPHY, and RECORD fields with strict NULLABLE, REQUIRED, or REPEATED modes.

Multi-Target Cloud Data Platform Ready

Export table definitions for bq CLI loading, Google Cloud Terraform/Pulumi IaC, BigQuery Python Client SDK, and ANSI Standard SQL DDL with partitioning.

Technical Reference & Architecture Guide

JSON to Google BigQuery Table Schema Generator — Reference Guide

Infer and convert JSON objects into Google Cloud BigQuery Table Schema JSON with RECORD mode, STRING, INTEGER, NUMERIC, and TIMESTAMP fields.

Key Parameters & Controls

defaultModestring

Specifies default column nullability mode for top-level and nested primitive fields (NULLABLE vs REQUIRED).

Default: NULLABLE
timestampDetectionboolean

Automatically inspects ISO 8601 string patterns (e.g., 2026-10-17T04:49:28Z) and maps them to TIMESTAMP instead of generic STRING.

Default: true
numericPrecisionstring

Controls floating-point precision mapping. Choose between FLOAT64 for scientific data or NUMERIC / BIGNUMERIC for financial accuracy.

Default: NUMERIC
sanitizeFieldNamesboolean

Transforms invalid JSON keys (hyphens, spaces, symbols, leading digits) into compliant BigQuery column identifiers ([a-zA-Z_][a-zA-Z0-9_]*).

Default: true

Pro Tips & Best Practices

When converting newline-delimited JSON

When converting newline-delimited JSON (NDJSON), feed multiple diverse records into the inferrer so sparse keys and optional fields are not omitted from the final schema.

Prevent Precision Loss on Financial Data

Map monetary values and decimals to BigQuery NUMERIC (fixed-point precision up to 38 digits) or BIGNUMERIC (76 digits) rather than standard IEEE 754 FLOAT64.

Direct Integration with bq CLI

Save the generated output to schema.json and load NDJSON immediately using: bq load --source_format=NEWLINE_DELIMITED_JSON my_dataset.my_table gs://my-bucket/data.json ./schema.json

Nested Array Modeling

Arrays of objects are converted into type: 'RECORD' with mode: 'REPEATED'. Ensure leaf fields inside repeated records remain NULLABLE or REQUIRED.

Common Gotchas & Troubleshooting

Invalid column name error: 'Fields must contain only letters, numbers, and underscores'

Enable sanitizeFieldNames or manually rename object keys containing hyphens (user-id), periods, or leading numerals to valid snake_case identifiers (user_id).

Type mismatch across records (e.g., field is INTEGER in row 1 but STRING in row 2)

Unify the data upstream, widen the field type to STRING in the generated schema, or use BigQuery's native JSON data type for dynamic payloads.

Exceeded maximum nesting depth of 15 levels

BigQuery enforces a maximum struct nesting depth of 15. Flatten deeply nested JSON trees or map sub-hierarchies into a single JSON-typed column.

Frequently Asked Questions

How do nested JSON objects and arrays map into BigQuery schema definitions?▼

A JSON object maps to type: 'RECORD' (STRUCT). An array of primitive values maps to that primitive type with mode: 'REPEATED'. An array of objects maps to type: 'RECORD' with mode: 'REPEATED' and a child 'fields' array.

How do I load data into BigQuery using the generated schema JSON?▼

Execute the Google Cloud SDK command: 'bq load --source_format=NEWLINE_DELIMITED_JSON my_dataset.my_table data.ndjson ./schema.json'. Alternatively, pass the schema JSON inside the 'schema' property of an insert job in the BigQuery REST API or client libraries.

When should I use BigQuery's native JSON type instead of explicit schema fields?▼

Use explicit schema fields when query performance, columnar indexing, partitioning, and strict type validation matter. Use the native BigQuery JSON type for rapidly changing schemaless payloads, highly polymorphic attributes, or log blobs.

What are the naming conventions and restrictions for BigQuery column names?▼

Column names must be 1-300 characters long, start with a letter or underscore, and contain only letters, numbers, and underscores. Names are case-insensitive by default in BigQuery queries.

Related Tools & Utilities

All Tools →
JSON to Snowflake CREATE TABLE DDL GeneratorDeveloper

Translate JSON structures into Snowflake SQL CREATE TABLE statements with VARIANT, VARCHAR, NUMBER, BOOLEAN, and TIMESTAMP_NTZ data types.

Open tool→
JSON / YAML to Terraform HCL Variable & Locals GeneratorDeveloper

Convert JSON and YAML payloads into Terraform HCL variable type constraints (type = object({...})), default values, and terraform.tfvars definitions.

Open tool→
OpenAPI / Swagger to GraphQL SDL Schema ConverterDeveloper

Convert OpenAPI 3.0 REST endpoints, request bodies, and JSON schemas into GraphQL SDL types, queries, mutations, and input definitions.

Open tool→
SQL DDL to TypeBox TypeScript Schema GeneratorDeveloper

Convert SQL CREATE TABLE statements into @sinclair/typebox runtime validation schemas using Type.Object, Type.String, Type.Number, and Type.Optional.

Open tool→
cURL to Rust (reqwest & tokio) Code GeneratorDeveloper

Transform cURL commands into idiomatic, asynchronous Rust HTTP requests using reqwest, serde_json, custom headers, and tokio runtime.

Open tool→
cURL to Ruby (Net::HTTP & Faraday) Code GeneratorDeveloper

Convert cURL HTTP requests into clean, native Ruby code using Net::HTTP, URI, Faraday, and JSON request/response handling.

Open tool→