JSON to SQL: Infer Table Schemas from JSON Objects

You’re working with a JSON-heavy system — an API, a NoSQL export, a webhook payload — and you need to design a relational schema to store the data. The shape of the JSON is clear; translating it to SQL tables is the tedious part.

The JSON to SQL converter takes a JSON object and drafts CREATE TABLE statements from it. It’s a scaffolding tool — a starting point that eliminates the purely mechanical work.

A Simple Example

Input:

{
  "id": 1,
  "username": "alice",
  "email": "alice@example.com",
  "isActive": true,
  "createdAt": "2026-01-15T09:00:00Z",
  "score": 98.5
}

Output (PostgreSQL):

CREATE TABLE root (
  id INTEGER NOT NULL,
  username TEXT NOT NULL,
  email TEXT NOT NULL,
  is_active BOOLEAN NOT NULL,
  created_at TIMESTAMP NOT NULL,
  score DOUBLE PRECISION NOT NULL
);

Types are inferred from the values: integer → INTEGER, string → TEXT, boolean → BOOLEAN, ISO date string → TIMESTAMP, decimal number → DOUBLE PRECISION.

Nested Object Handling

JSON commonly contains nested objects. The converter flattens or links them:

{
  "id": 1,
  "name": "Alice",
  "address": {
    "street": "123 Main St",
    "city": "Springfield",
    "country": "US"
  }
}

Output:

CREATE TABLE root (
  id INTEGER NOT NULL,
  name TEXT NOT NULL,
  address_id INTEGER
);

CREATE TABLE address (
  id INTEGER,
  street TEXT NOT NULL,
  city TEXT NOT NULL,
  country TEXT NOT NULL
);

Nested objects become separate tables with a foreign key reference in the parent. This gives you a normalized starting point.

For flatter schemas, you can choose to collapse the nested fields with a address_street, address_city, address_country pattern — the converter’s output gives you the normalized version, which you can then denormalize as needed.

Arrays of Objects

Arrays of objects are the most valuable thing the converter handles. They map directly to child tables:

{
  "id": 1,
  "customerName": "Alice",
  "items": [
    {
      "productId": 10,
      "productName": "Keyboard",
      "quantity": 1,
      "price": 79.99
    }
  ]
}

Output:

CREATE TABLE root (
  id INTEGER NOT NULL,
  customer_name TEXT NOT NULL
);

CREATE TABLE items (
  product_id INTEGER NOT NULL,
  product_name TEXT NOT NULL,
  quantity INTEGER NOT NULL,
  price DOUBLE PRECISION NOT NULL
);

The items array maps to its own table. In a real schema you’d add root_id INTEGER REFERENCES root(id) as a foreign key — the converter gives you the table structures, and you wire up the relationships.

Arrays of Primitives

Arrays of primitive values (strings, numbers) don’t have a clean single-table mapping:

{
  "id": 1,
  "tags": ["developer", "typescript", "react"]
}

Primitive arrays are noted in the output but not mapped to a table automatically, since there are multiple valid patterns (junction table, JSON column, comma-separated denormalized column) and the right choice depends on your query patterns.

The converter focuses on object-valued arrays, which have a clear normalized mapping.

Type Inference Logic

String values that look like ISO 8601 timestamps (2026-01-15T09:00:00Z, 2026-01-15) are inferred as timestamp/date types rather than plain text.

Reading the Size Stats

After generating, the tool shows the input and output sizes. This is less about optimization and more about confirming the full payload was processed — especially with large JSON objects that contain many nested levels.

Practical Use Cases

Designing tables for an API integration

You’re consuming a third-party API and want to store the data. Paste a sample API response, generate the schema draft, adjust the column names to match your conventions, and use it as a basis for your migration. If you also have C# entity classes for the same data model, the C# to SQL guide generates dialect-correct DDL with proper type mappings and primary key inference.

Documenting a NoSQL schema as relational

You’re working with MongoDB or DynamoDB and need to explain the document structure to someone who thinks in relational terms. Convert a sample document to DDL and use it as a communication tool.

Prototyping a data model

You have a rough data model in mind and you’ve expressed it as JSON. Instead of writing DDL from scratch, paste the JSON and generate the table structure. Iterate on the JSON until the shape is right, then refine the DDL.

Migrating from a document store to a relational database

When moving from a document store to PostgreSQL or MySQL, convert sample documents to get table drafts. The nesting structure of the documents maps directly to the table relationships you need.

Understanding a log structure

Application logs are often structured JSON. If you want to store them in a relational database for querying, generate a schema from a sample log event. The fields become columns.

What to Adjust After Generation

The converter gives you a structural starting point. Before using the DDL:

1. Add primary keys explicitly

CREATE TABLE root (
  id SERIAL PRIMARY KEY,  -- add SERIAL/IDENTITY/AUTO_INCREMENT
  ...
);

2. Add foreign keys between parent and child tables

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  root_id INTEGER NOT NULL REFERENCES root(id),
  ...
);

3. Set appropriate VARCHAR lengths for SQL Server / MySQL

NVARCHAR(255) or VARCHAR(255) is the default. If a column holds email addresses or short codes, it’s appropriately sized. If it holds long text, change it to TEXT or NVARCHAR(MAX).

4. Review timestamp precision

TIMESTAMP vs TIMESTAMPTZ in PostgreSQL matters if your application handles multiple timezones. Default to TIMESTAMPTZ if timestamps represent moments in time (not just local time readings).

5. Add indexes for query-critical columns

No indexes are generated. Add them based on your query patterns — at minimum, a primary key index on the id column and foreign key indexes on _id columns.

Limitations

The schema is a draft, not production-ready. It needs PK/FK wiring, constraint review, and naming adjustments.

Null inference is conservative. JSON null values produce nullable columns. If a field is never null in practice but appears as null in one sample record, the column will be incorrectly nullable.

Sample quality affects output quality. The generator infers types from the values present in the sample. If a field contains 1 in your sample but holds null in production, the generated column will be INTEGER NOT NULL instead of INTEGER NULL.

Use a real sample. For best results, paste an actual API response or database record rather than a hand-crafted example.

Before pasting a sample into the converter, run it through the JSON Formatter to confirm it’s valid and see its structure clearly — this makes it easier to spot nested objects or arrays that will produce separate tables. Once the DDL is generated, the SQL Formatter normalizes the indentation before you add it to a migration file.

The JSON to SQL converter saves you 10–20 minutes of mechanical typing per table. For a schema with 5–10 entities, that’s a meaningful time saving. More importantly, it forces you to look at the JSON structure and the relational mapping at the same time — which often surfaces normalization questions worth addressing early.