# Semi-structured data types

[Read this page in HTML](https://www.scopedb.io/docs/reference/data-types/semi-structured)

Use `array`, `object`, and `any` for values whose nested structure or element types vary.

| Type     | Contents                                                                   | Example                                   |
| -------- | -------------------------------------------------------------------------- | ----------------------------------------- |
| `array`  | Ordered elements of any supported type, including NULL.                    | `['cart_viewed', 'payment_failed']`       |
| `object` | String keys and values of any supported type, including NULL.              | `{'channel': 'web', 'total_cents': 7900}` |
| `any`    | A value of any supported type. Different rows may contain different types. | `parse_json('{"channel":"web"}')`         |

## Inserting semi-structured data

Store stable event fields in typed columns and varying attributes in an `object` column. Create a table for checkout events:

```scopeql
CREATE TABLE reference_checkout_payloads (event_id string, payload object)
```

Insert two checkout events:

```scopeql
VALUES
    ('evt-17', parse_json('{"channel":"web","total_cents":7900}')),
    ('evt-42', {'channel': 'mobile', 'total_cents': 4900, 'coupon': 'WELCOME'}::any)
SELECT $0 AS event_id, $1::object AS payload
INSERT INTO reference_checkout_payloads
```

Read their shared and optional fields:

```scopeql
FROM reference_checkout_payloads
SELECT event_id, payload['channel']::string AS channel, payload['coupon']::string AS coupon
ORDER BY event_id
```

The final `SELECT` returns:

```text
+----------+---------+---------+
| event_id | channel | coupon  |
+----------+---------+---------+
| evt-17   | web     | NULL    |
| evt-42   | mobile  | WELCOME |
+----------+---------+---------+
```

The two payloads have different keys. Reading the missing `coupon` field returns NULL.

Array and object literals can contain timestamps, intervals, binary values, and nested arrays or objects as well as JSON-compatible values. Cast strings explicitly when they represent a timestamp or interval.

## Using semi-structured values

Element access with `[]` returns `any`. Array indices start at 0; negative indices count from the end. Object keys are strings. Missing elements and keys return NULL.

```scopeql
SELECT
    ['cart_viewed', 'checkout_started', 'payment_completed'][-1] AS last_step,
    {'order': {'id': 'o-17'}}['order']['id'] AS order_id,
    {'channel': 'web'}['coupon'] AS coupon
```

```text
+-------------------+----------+--------+
| last_step         | order_id | coupon |
+-------------------+----------+--------+
| payment_completed | o-17     | NULL   |
+-------------------+----------+--------+
```

Cast an `any` value to the type required by the next function. Use `TRY(...)` to return NULL for incompatible values. Here `length` needs an array, but one event contains an object:

```scopeql
VALUES
    ('evt-17', '["sku-17","sku-42"]'),
    ('evt-42', '{"sku":"sku-63"}'),
    ('evt-63', 'null')
SELECT $0 AS event_id, parse_json($1) AS items
SELECT event_id, typeof(items) AS received_type, TRY(length(items::array)) AS item_count
```

```text
+----------+---------------+------------+
| event_id | received_type | item_count |
+----------+---------------+------------+
| evt-17   | array         | 2          |
| evt-42   | object        | NULL       |
| evt-63   | null          | NULL       |
+----------+---------------+------------+
```

`typeof(<any>)` reports the contained type as a string. `typeof(<array>)` instead returns the distinct element types. Cast an array to `any` when you want its own type name. Here one item ID has an unexpected numeric type:

```scopeql
SELECT
    typeof(['sku-17', 'sku-42', 63]) AS element_types,
    typeof(['sku-17', 'sku-42', 63]::any) AS value_type
```

```text
+------------------+------------+
| element_types    | value_type |
+------------------+------------+
| ["int","string"] | array      |
+------------------+------------+
```

## NULL and JSON

JSON `null` and ScopeQL `NULL` both become NULL values. Arrays retain NULL elements, and objects retain keys with NULL values.

[`to_json`](https://www.scopedb.io/docs/reference/functions/semi-structured-data/to_json.md) serializes these values as JSON text. A NULL input produces the string `'null'`. Casting text to `any` does not parse it; use [`parse_json`](https://www.scopedb.io/docs/reference/functions/semi-structured-data/parse_json.md) instead.

## Comparison

Arrays and objects use [lexicographic comparison](https://www.scopedb.io/docs/reference/commands/query-operators/comparison.md). `any` values have a defined order that can be used in `ORDER BY`. Different contained types compare by the following order, from greatest to least:

```text
object > array > interval > timestamp > boolean > string > binary > float > uint > int
```

Types remain distinct: an `int` value of `1` and a `float` value of `1.0` are different array elements. This affects functions such as `contains`, `dedup`, and `exclude`.

Ordinary comparisons return NULL if either operand is NULL. Use `IS` for NULL-safe equality and `NULLS FIRST` or `NULLS LAST` to control NULL placement in `ORDER BY`.

## Related contents

* [Semi-structured data functions](https://www.scopedb.io/docs/reference/functions/semi-structured-data.md)