Flatten JSON Online - Convert Nested JSON to Dot Notation

A worked example: mapping a webhook into a table

The most common reason to flatten JSON is to get it into something with columns - a spreadsheet, a warehouse table, a CSV export. Take this payment webhook, modelled on the events that payment providers post to your server when a charge succeeds:

{
  "id": "evt_3Q9xKc",
  "type": "payment.succeeded",
  "created": 1759132800,
  "data": {
    "id": "pay_71AfZ",
    "amount": 4200,
    "currency": "eur",
    "customer": { "id": "cus_88Tq", "email": "lena@example.com" },
    "payment_method": {
      "type": "card",
      "card": { "brand": "visa", "last4": "4242", "exp_month": 12, "exp_year": 2028 }
    },
    "lines": [
      { "sku": "TEA-01", "qty": 2, "unit_amount": 1500 },
      { "sku": "MUG-03", "qty": 1, "unit_amount": 1200,
        "discount": { "code": "AUTUMN10", "percent": 10 } }
    ],
    "refunds": [],
    "metadata": { "order_ref": "A-10293", "campaign": "autumn" }
  }
}

Flattened with array indices collapsed, it becomes this inventory of 28 paths:

id
type
created
data
data.id
data.amount
data.currency
data.customer
data.customer.id
data.customer.email
data.payment_method
data.payment_method.type
data.payment_method.card
data.payment_method.card.brand
data.payment_method.card.last4
data.payment_method.card.exp_month
data.payment_method.card.exp_year
data.lines
data.lines[].sku
data.lines[].qty
data.lines[].unit_amount
data.lines[].discount
data.lines[].discount.code
data.lines[].discount.percent
data.refunds
data.metadata
data.metadata.order_ref
data.metadata.campaign

Read the brackets first: they tell you how many tables you need

Before mapping a single column, scan the collapsed list for []. Every path that contains one lives inside an array, and an array means "any number of these per event". You cannot put those fields in the same row as the event without either duplicating the event or losing lines.

So this one payload splits cleanly into two tables:

Table	One row per	Columns come from
`payments`	event	Every leaf path without `[]`: `id`, `data.amount`, `data.customer.email`, `data.payment_method.card.brand`, ...
`payment_lines`	element of `data.lines`	Every path under `data.lines[]`, plus the event `id` as a foreign key

Nested objects that are not in an array - customer, payment_method.card, metadata - never need their own table. There is exactly one of each per event, so they flatten straight into columns like customer_email and card_brand.

Arrays nested inside arrays repeat the rule: a path with two [] needs a third table, keyed on both of its parents.

Watch for fields the sample cannot show you

Two lines in the output deserve suspicion rather than a column.

data.refunds has no children. The array was empty in this sample, so there is nothing to say what a refund looks like. It is still listed, which is the useful part: it tells you a payment_refunds table probably exists in your future, and that you need a sample with a refund in it before you can design it.
data.lines[].discount appears on one line item out of two. The collapsed list is the union of fields across elements, so it does not tell you which are always present. Untick Collapse array indices and the literal paths show it plainly - data.lines[1].discount.code exists, and there is no data.lines[0].discount. Make those columns nullable.

That first point is also why the flattener lists container keys like data.refunds and data.customer alongside the leaves. A leaf-only flatten loses empty arrays and empty objects without a trace. For comparison, the usual jq one-liner does exactly that:

jq -r 'paths(scalars) | map(tostring) | join(".")' event.json

On this payload it prints 23 paths and data.refunds is not among them, because an empty array contains no scalars. It also writes array positions as data.lines.0.sku, which is ambiguous with an object key named "0". Fine for a quick look; not what you want to base a table design on.

From paths to column names

Dots are awkward in SQL identifiers and spreadsheet formulas, so most teams convert the path into a column name with a fixed rule. The one that stays readable and reversible is: drop the envelope prefix, then replace dots with underscores.

data.amount                        -> amount
data.customer.email                -> customer_email
data.payment_method.card.last4     -> payment_method_card_last4
data.metadata.order_ref            -> metadata_order_ref
data.lines[].discount.code         -> (payment_lines) discount_code

Keep the full path somewhere - a comment on the column, or a mapping sheet - because the day the provider adds data.customer.address.city, you will want to see at a glance where it slots in. Switch the format to Key paths with value types to get the column types in the same pass: created and data.amount are numbers (a Unix timestamp and an amount in cents - neither is obvious from the name), while card.last4 is a string, which is correct and matters, because "0042" as a number becomes 42.

Spotting changes between two versions

Flattened output diffs well. Flatten last month's sample and this month's with the same settings, save both, and compare them:

diff old-paths.txt new-paths.txt

Because the collapsed list contains no values and no array positions, the only lines that differ are fields that were added or removed - exactly the list you need to update a mapping.

Keys containing dots

If a key name itself contains a dot, dot notation becomes ambiguous: metadata.order.ref could be a nested field or a single key literally called "order.ref". Webhook metadata objects are a common place for this, because their keys are chosen by whoever integrated with the API. When it matters, switch the format to JSONPath, which writes such a key as $.data.metadata['order.ref'].

Other output formats

Convert JSON to a TypeScript interface - the same structure as a type, with optional and nullable fields marked
Generate JSONPath expressions - query paths with [*] wildcards
View JSON as an indented tree - the shape without the full paths
Format or minify the JSON - whitespace only, every value kept exactly

Flatten JSON to Dot Notation