JSON to SQL: Generate CREATE TABLE Fast
You have a JSON sample from an API response or a log export and you need to load it into Postgres or MySQL. The schema does not write itself. Auto-detect in tools like pgloader or BigQuery's autodetect gets the shape mostly right, but always misses something. Here's how the type mapping actually works, where the edge cases hide, and what to do about nested objects and arrays that do not fit the relational model cleanly.
JSON to SQL Type Mapping
| JSON Type | Postgres | MySQL | Notes |
|---|---|---|---|
"string" short | VARCHAR(n) | VARCHAR(n) | Pick n based on observed max length |
"string" long | TEXT | TEXT | Use for descriptions, posts, anything unbounded |
123 integer | INTEGER | INT | Up to ~2.1 billion |
1234567890 large | BIGINT | BIGINT | Snowflake IDs, ms timestamps, external IDs |
1.5 money | NUMERIC(p,s) | DECIMAL(p,s) | Never FLOAT |
1.5 scientific | DOUBLE PRECISION | DOUBLE | Sensor data, scores, ratios |
true | BOOLEAN | TINYINT(1) | MySQL has no real boolean type |
"2026-01-15T10:30Z" | TIMESTAMPTZ | DATETIME | Always pick the timezone-aware variant |
"2026-01-15" | DATE | DATE | |
null | nullable column | nullable column | Add NOT NULL only when guaranteed present |
{} nested | JSONB or split table | JSON or split table | Decision below |
[1, 2, 3] | JSONB or junction | JSON or junction | Decision below |
"550e8400-e29b..." | UUID | CHAR(36) or BINARY(16) | Postgres has a native type |
NUMERIC (or DECIMAL) is the right pick for any money value. FLOAT and DOUBLE use binary representation, so 0.1 + 0.2 does not equal 0.3. The first time someone notices an invoice off by a cent, you will wish you had picked NUMERIC(12,2) from day one.
A Basic CREATE TABLE
Given this API response:
The Postgres schema:
Three things to notice. First, id is BIGINT, not INTEGER. External IDs blow past 2.1 billion more often than you think, especially if the source uses Twitter-style snowflake IDs or millisecond timestamps as keys. Second, created_at uses TIMESTAMPTZ, the timezone-aware version. Always use it in Postgres. Third, the unique constraints on username and email are not in the JSON, but they are in the application's contract. The generated DDL is a starting point you tighten by hand.
Nullable Fields
Any field that can be null or missing in the JSON needs to be nullable in SQL:
Columns are nullable by default in both Postgres and MySQL. Add NOT NULL only when you have verified the field is always present in every record. The opposite mistake, marking a sometimes-missing field as NOT NULL, is one of the most common reasons bulk imports fail at row 4,712 with a confusing error.
Nested Objects: Flatten, JSONB, or Split?
JSON nests freely. SQL does not. You have three reasonable options for this kind of payload:
Flatten when the nested object is small and fixed:
Use a JSONB column when the shape varies or you rarely query the nested fields:
Split into a separate table when the nested object has its own identity and is shared across rows:
The right answer depends on your queries. If you filter WHERE user.city = 'London' constantly, flatten or split. If the nested data is mostly read whole and rarely queried by inner field, JSONB with a GIN index is faster than you would expect and saves you from writing a migration every time the API adds a field.
Arrays Are Always a Choice
Junction table, the relational way:
Or store the array directly. Postgres has native array types:
MySQL has no array type, so you store arrays as JSON and use functions like JSON_CONTAINS. The junction table is more work upfront but gives you fast joins, foreign key integrity, and standard SQL queries. Pick it when tags have their own metadata or when you will query "all posts with tag X" hundreds of times per second. For tags that are essentially labels with no other attributes, the Postgres array column is enough and a lot less typing.
Postgres vs MySQL Quick Reference
| Concern | Postgres | MySQL |
|---|---|---|
| JSON column | JSONB (binary, indexed) | JSON (text-stored, parsed on read) |
| Array column | TEXT[], INTEGER[] etc | None, use JSON |
| Boolean | BOOLEAN | TINYINT(1) |
| Timezone-aware timestamp | TIMESTAMPTZ | TIMESTAMP (limited range) |
| Auto-increment | BIGSERIAL or GENERATED AS IDENTITY | BIGINT AUTO_INCREMENT |
| UUID | Native UUID | CHAR(36) or BINARY(16) |
| Default case sensitivity | Case-sensitive | Collation-dependent, usually insensitive |
JSONB is dramatically better than MySQL JSON. It is stored binary, supports GIN indexes for arbitrary key lookup, and parses once at write time. If your data is irregular and you are still choosing between databases, this alone is reason to pick Postgres.
Generating the Schema
For a one-off conversion, paste the JSON into the JSON to SQL converter to get a starting CREATE TABLE statement. It infers column types from the values, handles snake_case columns, and produces dialect-aware DDL. From there you usually tighten VARCHAR lengths, add indexes, and decide what to do about nested objects.
If the JSON file came from an unfamiliar source, run it through the JSON validator first. Trailing commas, single quotes, and unquoted keys are common in hand-edited samples and break every downstream tool with unhelpful errors. The JSON viewer is the fastest way to eyeball the structure of a deeply nested payload before deciding which fields to flatten and which to keep as JSONB.
One Last Tip
Always run a sample INSERT against the new schema with real data before kicking off the bulk import. The CREATE TABLE looks fine until you hit the first row with a 300-character "username" against your VARCHAR(64), or a numeric overflow because someone's user ID exceeded INTEGER's ceiling. A single round-trip catches both, and it costs you twenty seconds compared to debugging a half-loaded table at 2am.