JSON
JSON turns JSON text stored in a column into something you can work with. Unnest object makes one column per key. Flatten array makes one row per item. Strings and Typed values list every value in the document with its path. Other columns repeat on the new rows.
Unpack customer addresses
Manual Input holds three customers with an address column containing JSON. JSON is set to Unnest object, so each key becomes its own column.
JSON interactive example
Loading interactive example…
The live example needs a desktop screen. The result it produces is shown here, and the explanation and settings below work on any screen.
| name | address.city | address.zip |
|---|---|---|
| Ada | Oslo | 0150 |
| Lin | Rome | 00100 |
| Sam | Lima |
- Look at the preview. The
addresscolumn is replaced byaddress.cityandaddress.zip. Sam has no zip, so that cell is NULL. - Check Keep original JSON. The
addresscolumn stays alongside the new ones. - Change Output mode to
Strings. Each value now gets its own row, with its path inJSON_Nameand its value inJSON_ValueString. The three customers become five rows.
When to use
Use JSON when a column holds JSON, as many connector columns do, such as an address from Stripe or line items from an invoice. Use Flatten array first for a list, then Unnest object on the result. Use Strings followed by Cross Tab to unpack a whole nested document.
Modes
| Mode | Result |
|---|---|
| Strings | One row per value, with its path (JSON_Name) and value (JSON_ValueString). |
| Typed values | One row per value, with its path in JSON_Name and the value in JSON_ValueString, JSON_ValueInt, JSON_ValueFloat or JSON_ValueBool. Only the column for the value's type is filled. |
| Unnest object | Each top-level key becomes a text column named <source>.<key>. Nested objects and arrays stay as JSON text. |
| Flatten array | One row per array item, with the item's JSON text in <source>_flatten and its position, counting from 1, in <source>_idx. |
Paths join keys with dots and count array items from 0: items.0.name, items.1.name. A single value at the top level has an empty path. Keys keep their characters as written, so a dot inside a key can make a path ambiguous.
Unnest collects keys from every row, in the order they first appear. A missing key gives NULL, and a key with values of mixed types stays text. A full run also finds keys the preview sample doesn't have.
Examples
A Stripe-style address column containing {"city":"London","country":"GB"} becomes address.city and address.country. In Script view:
customers = input(file_path="customers.csv")
addresses = json(customers, source_column="address", output_mode="unnest", keep_source_column=false)
An items column containing [{"name":"A","quantity":2},{"name":"B","quantity":3}] becomes two rows that keep the order ID. In Script view:
orders = input(file_path="orders.csv")
items = json(orders, source_column="items", output_mode="flatten", keep_source_column=false)
fields = json(items, source_column="items_flatten", output_mode="unnest", keep_source_column=false)
For {"address":{"city":"London","country":"GB"}}, Unnest the source column, then Unnest the column it creates to reach the city and country. That column is named <source>.address, where <source> is the name of the column you parsed.
For an entire nested document, use Strings, then Cross Tab: select JSON_Name as the column-name field and JSON_ValueString as the value field. Keep a column that identifies each source row (add Record ID first if needed) so different input rows aren't combined.
Values, empty inputs and errors
- Strings returns string values without their quotes and turns other values, such as numbers, into text. Typed values sorts each value into text, a signed 64-bit integer, a double-precision decimal or a boolean.
- An integer too large for signed 64 bits, or a non-finite number, causes an error in every mode. Text that looks like a number stays text.
- A NULL cell gives no rows in Strings, Typed values or Flatten. JSON
nullgives one Strings or Typed values row with NULL in the value columns. - An empty object or array holds no values, so Strings and Typed values give no rows for it. Flatten gives no rows for an empty array.
- Unnest keeps rows with an empty object, a NULL cell or JSON null, and fills their new columns with NULL.
- Flatten writes each item as valid JSON text, so strings keep their quotes and JSON null becomes the text
null. - Unnest needs an object or null, and Flatten needs an array or null. You get an error for the wrong structure, malformed JSON, a key repeated within one object or output column names that clash.
- Unnest creates up to 1,000 columns. If there are no keys and dropping the JSON column would leave nothing, check Keep original JSON or keep another input column.
- Trailing commas are accepted. Whitespace and exponent notation in numbers are normalized, and decimals are double-precision numbers.
To turn path rows back into columns, use Cross Tab.
To keep rows from different records apart, add Record ID.
Inputs and outputs
Inputs
1 port- InRequired
Accepts: Tables, Geospatial data.
Accepts one connection on this port.
Outputs
1 port- Out
Configure the block
- JSON column
The text column containing one JSON document per cell.
- Output mode
List every value with its path, unnest one object level or flatten an array.
Default:
StringsOptions: Strings, Typed values, Unnest object, Flatten array.
- Keep original JSON
Include the selected JSON column in the output.
Default:
false
JSON FAQs
- How do I turn a list of objects into a table?
- Use Flatten array first to get one row per item, then Unnest object on the result to get one column per key.
- What happens when a key is missing from some rows?
- The cell is NULL, like Sam's zip in the example.
- What type are the unnested columns?
- Text. Nested objects and arrays stay as JSON text, so you can run JSON on them again.
Prepare your data. Build it in Strata.
Strata opens in your browser, with no account.