Extracting Fields from JSON Columns into Regular Columns

You exported your products table and there it is: a column called metadata containing values like {"weight_g": 85, "color": "black", "warranty_months": 12}. All the information you need is in there, but it's trapped inside a JSON string. You can't filter by color, sort by weight, or build a chart of warranty periods. The data exists but it's not usable.

This happens constantly with data exported from APIs, e-commerce platforms, and any system that stores flexible attributes as JSON blobs. Here's how to break those fields out into proper, typed columns in ExploreMyData.

What JSON columns look like in practice

JSON columns show up in CSVs more often than you'd expect. Shopify product exports, Stripe payment metadata, survey tools that store custom fields, CRM systems with flexible attributes. The JSON is usually valid, but every row might have a different set of keys. One product has a warranty_months field, another doesn't. One has nested objects, another is flat.

A typical table might look like this:

You want weight_g and color as their own columns so you can sort products by weight or filter by color.

Using JSON Extract in dictionary mode

Click the green + in the Pipeline panel and select JSON Extract from the Data group. The panel is short, and every control on it matters:

1. Source column: pick metadata.
2. JSON Type: Dictionary for objects with named keys, Array / List for ["a", "b", "c"]. Leave it on Dictionary here.
3. Extract as: Columns (one new column per key) or Rows (a tall key/value pair per key). Columns is what you want.
4. Keep source column in the grid: unchecked by default, so metadata disappears once the new columns land. Tick it if you plan to pull more keys out later.
5. Keys to be extracted: one row per key. Each row is a text box reading "Type key name" and a Text / Number toggle. "Add Key" adds another row.

The thing to internalise is that last one. You type the key name. There is no picker, no schema sniffing, no list of keys found in your data. If you spell it wrong you get a column full of NULLs and no warning, so it is worth widening the JSON column and reading a couple of real values before you start.

The SQL under the hood

Each key becomes one ->> expression. That is DuckDB's "extract by key and give it back to me as text" operator. Keys set to Number get wrapped in a TRY_CAST to DOUBLE on top of that:

SELECT "product", "price",
       "metadata"->>'color' AS "color",
       TRY_CAST("metadata"->>'weight_g' AS DOUBLE) AS "weight_g",
       TRY_CAST("metadata"->>'warranty_months' AS DOUBLE) AS "warranty_months"
FROM products

Notice that metadata is absent from the SELECT list. That is the "Keep source column" checkbox doing its job. Tick it and the column rides along.

Both halves fail softly, which is the whole reason this is usable on real exports. A key that is not in a row's JSON makes ->> return NULL, and a value that will not parse as a number makes TRY_CAST return NULL. Neither one aborts the query. The USB-C Hub row has no warranty_months key, so that cell is simply empty.

The result

Three ordinary columns. Sort by weight_g, filter on "color" = 'black', average the warranty period with Group & Aggregate. Every downstream operation treats them exactly like columns that came out of the CSV.

Dealing with inconsistent keys across rows

Real JSON columns rarely carry the same keys in every row, and nothing here requires them to. Ask for a key that only two rows out of a thousand have and you get a column that is NULL 998 times, which is often exactly the signal you were after.

Because the panel will not tell you what keys exist, get that list yourself first. Widen the JSON column in the grid and read a handful of rows, ideally from different parts of the file, since exports often change shape partway through. Keys are case sensitive, and a stray space inside the quotes counts.

Arrays instead of objects

Flip JSON Type to Array / List and the key list is replaced by two fields: "No. of new columns (max 20)" and a Text / Number toggle. Ask for 3 columns from tags and you get tags_0, tags_1 and tags_2, zero indexed, NULL where the array was shorter.

Twenty is a hard ceiling, and it is the right ceiling: if your arrays are longer than that, switching Extract as to Rows is almost always what you actually wanted. That unnests the array so each element gets its own row with the rest of the record repeated alongside it.

Nested JSON takes two passes

There are no dotted paths. Typing dimensions.width_mm into the key box looks for a key literally called "dimensions.width_mm" and returns NULL. What you do instead is run JSON Extract twice.

Say the column holds {"color": "black", "dimensions": {"width_mm": 62, "height_mm": 38}}. First pass: source metadata, keys color and dimensions, both set to Text. Asking for a key whose value is an object gives you the object back as a string, which is precisely what you want here:

Second pass: add another JSON Extract step, this time with dimensions as the source column and width_mm and height_mm as the keys, both set to Number. The generated SQL is just TRY_CAST("dimensions"->>'width_mm' AS DOUBLE) AS "width_mm", and it works because ->> is happy to parse a text column that happens to contain JSON.

One extra step per level of nesting. Three levels deep means three JSON Extract steps, which is tedious but entirely predictable, and each intermediate column is visible in the grid so you can see where a key name went wrong instead of guessing at a silent NULL.

Extract JSON fields in ExploreMyData →