JSON Extract

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

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 to Text or Number.
3. Click Add Key for the next key.

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). Set New column's type to Text or Number.
• 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.
• Nested JSON needs two steps. Extract the inner object first, then run JSON Extract on it.

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"

Related Operations

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