<!-- Source: https://www.misata.studio/docs/guides/nested-columns -->
<!-- Docs index: https://www.misata.studio/llms.txt -->

# Nested JSON and array columns

API payloads, event properties, user preferences and document stores are
nested. Declare the structure and every field is generated by the same engine
as a top-level column, so an `email` field holds a real email, a `city` a real
city, and a `pattern` a code of that shape.

```yaml
tables:
  users:
    rows: 1000
    columns:
      user_id: {type: int, primary_key: true}
      profile:
        type: json
        fields:
          email: email
          age: {type: int, min: 18, max: 80}
          plan: {type: string, enum: [free, pro, enterprise]}
          address:
            type: json
            fields:
              city: text
              zip: {type: text, pattern: "#####"}
          tags: {type: array, items: {type: string, enum: [vip, beta, churn-risk]},
                 max_items: 3, unique_items: true}
        optional: [address]          # omitted on a share of rows
        optional_rate: 0.3
      logins:
        type: array
        items: {type: datetime, start: "2024-01-01", end: "2024-12-31"}
        min_items: 1
        max_items: 20
        sorted: true
```

| Key | Column | Meaning |
|---|---|---|
| `fields` | `json` | `{name: spec}`; a spec is a type name (`email`, `int`, `text`) or a column spec |
| `optional`, `optional_rate` | `json` | fields left out of that share of rows (default 0.3) |
| `items` | `array` | the spec of each item |
| `min_items`, `max_items` (or `length`) | `array` | length range per row |
| `unique_items`, `sorted` | `array` | no repeats within a row; items in order |

Specs nest freely, and the dict-schema spellings (`object`, `list`, `jsonb`,
`enum`) work too.

## What you get

In a DataFrame, CSV or database the value is canonical JSON text, so every
writer handles it: CSV cells parse with `json.loads`, and Postgres columns are
created as `JSONB` and query with `->>`. To work with the values as objects:

```python
misata.decode_json_columns(tables["users"])      # dicts and lists in pandas
misata.to_jsonl(tables, "./out/")                # nested objects, one per line
misata.to_polars(tables)["users"]                # Polars structs and lists
```

A `json` column with no `fields` is refused rather than filled with a
placeholder object.