---
title: "Parse JSON into columns or rows in Strata | Oplema"
description: "Parse JSON text into columns or rows with the JSON block. Choose Unnest object, Flatten array, Strings or Typed values and see how missing keys work."
url: "https://oplema.com/docs/strata/parse/json"
updated: "2026-11-04"
---

# 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.

**Learn this in context:** [Extract fields from JSON and reconcile payments](https://oplema.com/learn/many-sources/extract-json-and-reconcile) (Combine and reconcile data files)

## 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.

Result
| name | address.city | address.zip |
| --- | --- | --- |
| Ada | Oslo | 0150 |
| Lin | Rome | 00100 |
| Sam | Lima |  |

1. Look at the preview. The `address` column is replaced by `address.city` and `address.zip`. Sam has no zip, so that cell is NULL.
2. Check Keep original JSON. The `address` column stays alongside the new ones.
3. Change Output mode to `Strings`. Each value now gets its own row, with its path in `JSON_Name` and its value in `JSON_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 `null` gives 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](https://oplema.com/docs/strata/transform/cross-tab).

To keep rows from different records apart, add [Record ID](https://oplema.com/docs/strata/prep/record-id).

## Inputs and outputs

### Inputs

1 port

**In Required**

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: `Strings`

Options: 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.

[Open Strata](https://oplema.com/editor?path=%2F%3Fopen%3Dstrata) [About Strata](https://oplema.com/strata)
