Convert JSON to MySQL Queries!

Input JSON to convert it into CREATE TABLE and INSERT statements for MySQL and MariaDB!
JSON
Result

Convert JSON to MySQL Queries!

Looks at the values in a JSON array, infers MySQL/MariaDB column types, and produces a CREATE TABLE statement plus one INSERT containing every row. Paste the output into a MySQL client and the table is created and filled in one go.

Useful when you want to load data from an external API into a scratch table and aggregate it with SQL, or seed a development database with test rows.

How to use

  1. Paste an array of objects into the JSON box. A single object gives a one-row INSERT.
  2. Enter the table to create in Table name (default my_table). The name is kept in the URL as ?table=.
  3. Check the inferred column types, adjust lengths or indexes where needed, and run the SQL in MySQL Workbench, DBeaver or the mysql CLI.

Example

Input (JSON)
[
  { "id": 1, "email": "minji@example.com", "plan": "pro", "verified": true,
    "settings": { "lang": "ko" }, "joinedAt": "2024-03-01T09:30:00Z" },
  { "id": 2, "email": "tom@example.com", "plan": "free", "verified": false,
    "settings": null, "joinedAt": "2024-03-04T23:10:00Z" }
]
Output (MySQL)
CREATE TABLE `members` (
  `id` INT NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `plan` VARCHAR(255) NOT NULL,
  `verified` TINYINT(1) NOT NULL,
  `settings` JSON NULL,
  `joinedAt` DATETIME NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `members` (`id`, `email`, `plan`, `verified`, `settings`, `joinedAt`) VALUES
  (1, 'minji@example.com', 'pro', 1, '{"lang":"ko"}', '2024-03-01 09:30:00'),
  (2, 'tom@example.com', 'free', 0, NULL, '2024-03-04 23:10:00');

verified became TINYINT(1), and settings, which holds an object, became a JSON column that allows NULL because one row had null. ISO dates ending in Z (UTC) were rewritten to the '2024-03-01 09:30:00' form MySQL accepts, and since id is an integer present in every row, it became the primary key.

Supported features

  • Type inference: booleans → TINYINT(1), 32-bit integers → INT, larger integers → BIGINT, decimals → DOUBLE, objects and arrays → JSON.
  • String patterns: 2024-01-02 09:00:00 or ISO 8601 → DATETIME, 2024-01-02 → DATE, 09:30 → TIME, anything else → VARCHAR(255), or TEXT if any value exceeds 255 characters.
  • When rows disagree, the wider type wins: integer + decimal → DOUBLE, date + datetime → DATETIME, any other mix → VARCHAR(255).
  • A column is NULL if any row has null or lacks the key, otherwise NOT NULL.
  • An id key is used as the PRIMARY KEY; without one, an id BIGINT NOT NULL AUTO_INCREMENT column is added.
  • Identifiers are backtick-quoted and ' and \ in strings are escaped. Tables are created with ENGINE=InnoDB DEFAULT CHARSET=utf8mb4.

Use cases

Aggregating API data with SQL

If you have payment records or logs saved as JSON from an API, loading them into a table and running GROUP BY is often quicker than writing a script.

Test data for dev and staging databases

JSON from the mock data generator or a captured production response becomes INSERT statements for migration tests or UI development.

Notes and limitations

  • Column names are the JSON keys as is; camelCase like joinedAt is not converted to snake_case.
  • Nested objects are not normalized into separate tables; they go into a single JSON column.
  • VARCHAR(255) length, indexes, UNIQUE constraints and DECIMAL precision are not inferred. For money columns, switching DOUBLE to DECIMAL is recommended.
  • If id contains null, the table is created without a primary key.
  • Datetimes with a time zone are stored converted to UTC; adjust them yourself if you need local-time values.
  • All rows go into one INSERT statement, so tens of thousands of rows may hit the server's max_allowed_packet limit.

FAQ

Will the output run on PostgreSQL or SQLite?

Not unchanged: it uses MySQL syntax such as backtick identifiers, ENGINE=InnoDB, TINYINT(1) and AUTO_INCREMENT. Swap backticks for double quotes and AUTO_INCREMENT for GENERATED ... AS IDENTITY or the equivalent.

Why TINYINT(1) instead of BOOLEAN?

In MySQL, BOOLEAN is just an alias for TINYINT(1). Values are inserted as 1/0, and the type name matches what dumps and ORMs show.

Does the JSON column work on older servers?

The JSON type exists from MySQL 5.7.8 and MariaDB 10.2 (where it is an alias for LONGTEXT). On anything older, change it to TEXT.