schema

Summarizes the top-level keys and value types observed in objects.

Syntax

schema(<object>)

Arguments

Returns

Returns an array of objects. Each object describes an observed field:

• key: the field name.
• type: its value type.
• elements: distinct element types, present only for array fields.

A key appears more than once when its value type or array element types differ across input objects.

Ignores NULL input objects. Returns [] when no keys are observed. Result order is unspecified.

Examples

Inspect changing event payloads

Inspect a sample of checkout payloads before writing typed queries. Here a producer starts sending amount as text and introduces a coupon field.

VALUES
    ({'amount': 49.0, 'channel': 'web'}),
    ({'amount': '79.00', 'channel': 'mobile', 'coupon': 'WELCOME'})
SELECT $0 AS payload
AGGREGATE schema(payload) AS fields

The fields value:

[
  {
    "key": "amount",
    "type": "float"
  },
  {
    "key": "amount",
    "type": "string"
  },
  {
    "key": "channel",
    "type": "string"
  },
  {
    "key": "coupon",
    "type": "string"
  }
]

amount appears as both float and string; cast it before calculating totals. Field order may vary.

Inspect array element types

The item list is still an array in both payloads, but one producer sends a numeric item ID. Inspect the element types as well as the outer type.

VALUES
    ({'item_ids': ['sku-17', 'sku-42']}),
    ({'item_ids': ['sku-63', 84]})
SELECT $0 AS payload
AGGREGATE schema(payload) AS fields

The fields value:

[
  {
    "elements": [
      "string"
    ],
    "key": "item_ids",
    "type": "array"
  },
  {
    "elements": [
      "int",
      "string"
    ],
    "key": "item_ids",
    "type": "array"
  }
]

The second entry includes both int and string elements. Entry and element-type order may vary.