[] (element access)

On this page

Reads an array element or an object field.

Syntax

<data>[<index>]
<data>[<key>]

Arguments

ArgumentTypeDescription
<data>array, object, or anyValue to read.
<index>intArray position, starting at 0. Negative indices count from the end; -1 selects the last element.
<key>stringObject key. An empty string is a valid key.

Returns

Returns any. Returns NULL when:

  • The array index is out of range or the object key is missing.
  • The input, index, or key is NULL.
  • An any input has the wrong contained type: an integer index needs an array, and a string key needs an object.

Examples

Read nested event fields

Extract typed fields from a flexible payload, including the final item of a nested array.

VALUES ({
    'order': {'id': 'o-17', 'total_cents': 7900},
    'steps': ['cart_viewed', 'checkout_started', 'payment_completed']
})
SELECT $0 AS payload
SELECT
    payload['order']['id']::string AS order_id,
    payload['order']['total_cents']::int AS total_cents,
    payload['steps'][-1]::string AS last_step
+----------+-------------+-------------------+
| order_id | total_cents | last_step         |
+----------+-------------+-------------------+
| o-17     | 7900        | payment_completed |
+----------+-------------+-------------------+

Cast extracted fields to the types needed by later expressions.

Handle an optional event attribute

A coupon field can be present on one order and absent on another.

VALUES
    ('o-17', {'coupon': 'WELCOME'}), ('o-42', {'channel': 'web'})
SELECT $0 AS order_id, $1 AS payload
SELECT order_id, payload['coupon']::string AS coupon
+----------+---------+
| order_id | coupon  |
+----------+---------+
| o-17     | WELCOME |
| o-42     | NULL    |
+----------+---------+