JSON Extract

Turn a column of JSON text into real columns you can filter, sort and chart. Nested keys, and real dates and booleans.

What it does

App exports often keep extra fields in one text column. The cell looks like this:

{"plan": "pro", "seats": 12, "country": "IN"}

That is a dictionary. It holds named keys. The other shape is an array, a plain list:

["email", "sms", "push"]

JSON Extract reads such a column. It writes the values into new columns, or into new rows.

Set up the panel

1. Type json in the Search transforms box, in the Pipeline panel.
2. Select JSON Extract in the results.
3. Open Source column. Pick the column with the JSON text.
4. Set JSON Type to Dictionary or Array / List.
5. Set Extract as to Columns or Rows.
6. Fill in the fields for your choice. The next two sections explain them.
7. Click Apply.

The source column disappears from the grid. Tick Keep source column in the grid to hold on to it.

Dictionary: name the keys you want

The panel shows a Keys to be extracted box. It explains: Each key will be extracted as a column.

1. Type a key in the Type key name box. Spelling and case must match the JSON.
2. Set that key's type. Text, Number, True/false, Date, Date and time and Raw JSON are all offered.
3. Click Add Key for the next key.

Picking the right type here saves a Convert Type step afterwards. True/false and the two date types read the value straight into a real boolean or date column; a value that will not convert comes back empty, and the rest of the column converts as normal.

Keys inside keys

A key can be a path. Write the parts with dots between them and the step walks into the nested object:

{"plan": "pro", "billing": {"country": "IN", "currency": "INR"}}

Type billing.country and you get a column called country holding IN. The path is how to find the value; the last part of it is what the column is called. The formal $.billing.country form works too, and is named the same way.

Pick Raw JSON as the type to pull out a whole nested object or list without flattening it. The column then holds the fragment as it was written, ready for a second JSON Extract step.

With Extract as set to Columns, each key becomes a column with that name:

With Rows, you get two columns instead: json_key and json_value. One row appears per key.

Array: say how many slots you want

An array has no key names, so you work with positions.

• With Columns, set No. of new columns (max 20). New column's type takes the same list of types as a dictionary key.
• New columns take the source name plus a number, counting from zero: channels_0, channels_1.
• A slot with no value in that row stays empty.
• With Rows, every item becomes its own row. The new column is the source name plus _value.

Tips

• The column limit for an array is 20. Switch Extract as to Rows for longer lists.
• A key that is absent gives an empty cell. Check the spelling first when a whole column comes back empty.
• Number converts the value. Text that is not a number becomes empty.
• Extract to rows, then use Group & Aggregate to count values.
• Reach a nested value in one step by writing its path with dots, such as billing.country.
• Not sure what the keys are called? The panel can read the first rows and offer the keys it finds as chips, so you do not have to open a cell and read the JSON by eye.

For SQL users

Two dictionary keys, extracted as columns, run as:

SELECT "account_id", "payload"->>'plan' AS "plan", TRY_CAST("payload"->>'seats' AS DOUBLE) AS "seats" FROM "accounts"

A dotted key becomes a JSONPath, and Raw JSON uses -> rather than ->> so the fragment survives:

"payload"->>'$.billing.country' AS "country"
CAST("payload"->'$.billing' AS VARCHAR) AS "billing"

The column is read through a TRY_CAST(... AS JSON) first, so one truncated row comes back empty instead of failing the whole step.

Related Operations

• Join - Join with another file/table
• Unnest - Explode delimited column into rows
• Fill Missing - Fill null values