Worked example

Two order records from an e-commerce API, with the customer nested, an array of items, a field that is null in one record and a coupon code that appears only once. Table name orders, nested objects flattened:

[
  {
    "orderId": 10231,
    "customer": {
      "email": "asha@example.com",
      "country": "IN"
    },
    "total": 1499.5,
    "currency": "INR",
    "status": "shipped",
    "placedAt": "2026-10-01T09:30:00+05:30",
    "items": [
      {
        "sku": "BK-101",
        "qty": 2
      }
    ],
    "giftWrap": false
  },
  {
    "orderId": 10232,
    "customer": {
      "email": "li@example.com",
      "country": "SG"
    },
    "total": 89,
    "currency": "SGD",
    "status": "pending",
    "placedAt": "2026-10-01T11:02:41+08:00",
    "items": [],
    "giftWrap": null,
    "couponCode": "WELCOME10"
  }
]

PostgreSQL · 2 records · 10 columns

Download .sql

-- Proposed by the JSON to PostgreSQL DDL tool from 2 sample records.
-- Review types, nullability and keys before running it.
CREATE TABLE orders (
    order_id          bigint,
    customer_email    text,
    customer_country  text,
    total             numeric,
    currency          text,
    status            text,
    placed_at         timestamptz,
    items             jsonb,
    gift_wrap         boolean,
    coupon_code       text
);

Before you run it

All columns are nullable: a sample cannot prove a field is always present. Tick the NOT NULL option to constrain fields that were present in every record.

How each column was decided
Column	JSON key	Type	Seen in	Why
`order_id`	`orderId`	`bigint`	2/2	Whole numbers; bigint because identifiers tend to outgrow integer.
`customer_email`	`customer.email`	`text`	2/2	—
`customer_country`	`customer.country`	`text`	2/2	—
`total`	`total`	`numeric`	2/2	Seen fractions or exponents; numeric is exact. Use double precision for measurements.
`currency`	`currency`	`text`	2/2	—
`status`	`status`	`text`	2/2	—
`placed_at`	`placedAt`	`timestamptz`	2/2	ISO 8601 date-times with a UTC offset.
`items`	`items`	`jsonb`	2/2	Arrays are kept as jsonb.
`gift_wrap`	`giftWrap`	`boolean`	2/2 (1 null)	—
`coupon_code`	`couponCode`	`text`	1/2	—

How types are inferred

Every value of every field is looked at, and the column gets the narrowest type that fits all of them:

Values in the sample	PostgreSQL type
Whole numbers within ±2,147,483,647	`integer` (`bigint` for `id` and `*_id` columns)
Larger whole numbers, up to 9.2 × 10¹⁸	`bigint`
Any number with a fraction or exponent	`numeric`: exact, safe for money
`true` / `false`	`boolean`
Strings that are all ISO 8601 dates (`2026-10-01`)	`date`
ISO 8601 date-times without an offset	`timestamp`
ISO 8601 date-times with `Z` or `+05:30`	`timestamptz`
Strings that are all canonical UUIDs	`uuid`
Any other strings, or a mix of the above	`text`
Objects and arrays	`jsonb` (objects can be flattened instead)
Only `null`	`text`, flagged as unknown

Numbers are read without floating-point rounding, so 1499.50 stays exact. Strings are never turned into numbers: "00123" is a code, not the number 123, and stays text. When a field mixes kinds, such as a number in one record and a string in another, the column falls back to text (or jsonb when objects or arrays are involved) and the table of decisions says so.

Column names and quoting

Keys become lowercase snake_case: placedAt → placed_at, Unit Price (€) → unit_price. Accents are simplified and other symbols removed.
Flattened keys join with an underscore: customer.email → customer_email.
Names are cut to PostgreSQL's 63-byte limit; clashes get _2, _3; keys with no letters or digits become column_N.
Reserved words such as select, order or user are double-quoted, so the statement still runs.

Nulls, missing fields and keys

A sample shows what can happen, never what cannot. So every column is nullable by default, and the "Seen in" column tells you how many records had the field and how many of those were null. If you tick the NOT NULL option, it is added only to fields that were present and non-null in every record. A field called id gets a reminder to add a primary key, but the tool cannot know whether it is unique.

Limitations

Inference sees only your sample. Rare values, such as a string in a numeric field once a month, will not be reflected unless they are in it.
Semantic types are not guessed: emails, phone numbers and currency codes stay text. Add CHECK constraints, enums or lengths yourself.
Dates in other formats (01/10/2026, Unix timestamps) stay text or numbers. Convert them when you load the data.
Arrays are not split into child tables. For one-to-many data, model a separate table with a foreign key.
The output is PostgreSQL only. Other databases have different types and quoting rules.

Frequently asked questions

Should I use jsonb or flatten nested JSON?

Flatten fields you will filter, join or aggregate on, because ordinary columns are typed, indexed and validated. Keep jsonb for parts whose shape varies or that you mostly store and return as-is. A common pattern is both: typed columns for the important fields plus a jsonb column with the raw payload.

Why numeric instead of double precision for decimals?

numeric stores exactly what you give it, which matters for money and anything you compare for equality. double precision is faster and smaller but approximate; switch to it for measurements such as sensor readings or coordinates.

Why are my ISO date strings typed as text?

At least one value did not match: an invalid date such as 2026-02-30, a different format, or an empty string. Check the decision table; it notes when some but not all values looked like dates.

Is my JSON stored or used for anything else?

No. It is parsed in memory to produce the answer and then discarded. It is not written to a database or a log, and it is not sent to any other service or AI model. Still, use non-sensitive sample data.

Can I load the JSON into the table afterwards?

Yes. A common route is to load the raw records into a jsonb staging column, then INSERT … SELECT with casts such as (doc->>'placed_at')::timestamptz into the typed table. That way bad rows fail loudly instead of silently.

JSON to PostgreSQL DDL Generator

Create a free account to use the generator