Auto-Generating CREATE TABLE from JSON Structure

Inferring CREATE TABLE from JSON

Before inserting JSON data, you often need to create the target table. The converter can analyze JSON structure and value types to generate an appropriate CREATE TABLE statement.

Example JSON

[
  { "id": 1, "name": "Alice", "email": "alice@example.com", "age": 30, "score": 98.5, "active": true, "bio": null },
  { "id": 2, "name": "Bob", "email": "bob@example.com", "age": null, "score": 87.0, "active": false, "bio": "Developer" }
]

Generated PostgreSQL DDL

CREATE TABLE users (
    id INTEGER NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    age INTEGER,
    score DOUBLE PRECISION NOT NULL,
    active BOOLEAN NOT NULL,
    bio TEXT,
    PRIMARY KEY (id)
);

Type Inference Rules

The converter scans all values for each key across the entire array:

JSON values seen	Inferred SQL type
Only integers	INTEGER (or BIGINT if > 2^31)
Any decimal	DOUBLE PRECISION / FLOAT
Only strings, short	VARCHAR(255)
Strings > 255 chars	TEXT
Only booleans	BOOLEAN
Mixed types	TEXT (safest fallback)

Nullable Detection

If any row has null for a column, or a row is missing that key entirely, the column is nullable. Otherwise, NOT NULL is added.

Primary Key Heuristics

The converter checks for common primary key patterns:

1. Column named id with unique integer values
2. Column named _id (MongoDB convention)
3. Column named uuid with unique string values

MySQL DDL

CREATE TABLE users (
    id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    age INT DEFAULT NULL,
    score DOUBLE NOT NULL,
    active TINYINT(1) NOT NULL,
    bio TEXT DEFAULT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Combining CREATE TABLE + INSERT

The converter can generate both statements together, ensuring column types match the inserted values:

CREATE TABLE IF NOT EXISTS users ( ... );
INSERT INTO users (...) VALUES (...);