Search Inside Payload Arrays: payload.items[0].sku in the Events Query Bar

The Field You Need Is Usually in an Array

The events query bar has had a payload.<path>: filter for a while. payload.customer.email:"a@b.com" finds every event whose payload has that email at that path. It works well when the field you care about sits in a chain of nested objects.

Real webhook payloads are rarely shaped like that. An order webhook carries a list of line items. An invoice carries a list of lines. A batch notification carries a list of records. The question you are actually asking when something goes wrong is usually "which events contained SKU-C8102?", and that SKU lives inside an array.

Until now there was no way to ask that. The API rejected brackets in the field path with a 400, and a dotted path that crossed an array, such as data.order.items.sku, was accepted but could never match anything. The search built its match pattern out of nested objects only, so it was looking for an items object with a sku key, and a real payload has an items array.

What's New

Each segment of a payload field path can now end in an array index:

payload.data.order.items[0].sku:"SKU-C8102"

That finds every event whose data.order.items array contains an element with sku equal to SKU-C8102. It works anywhere a payload.<path>: filter works: on its own, combined with other filter keys, or combined with free-text terms.

payload.data.order.items[0].sku:"SKU-C8102" status:failed source:shopify

That query returns failed events from a source named or slugged shopify whose order contains that SKU.

The Index Matches Any Element

This is the one behavior that might surprise you: [0] doesn't mean "the first element." It means "some element of this array." items[0].sku:"SKU-C8102" and items[5].sku:"SKU-C8102" return exactly the same events.

Take this payload:

{
  "data": {
    "order": {
      "items": [
        { "sku": "SKU-A1001", "qty": 1 },
        { "sku": "SKU-C8102", "qty": 2 }
      ]
    }
  }
}

The matching SKU is at index 1, yet payload.data.order.items[0].sku:"SKU-C8102" still matches it.

For the question people actually ask ("did this order include that product?") this is usually the behavior you want. You rarely know, or care, which position a line item landed in. It was still a deliberate tradeoff, and the reason comes from how the search runs in PostgreSQL.

Why It Works This Way

Every event's payload is stored in a jsonb column, payload_jsonb, with a GIN index built using the jsonb_path_ops operator class. That index is designed for one operator, @> (containment): "does this document contain this smaller document?"

Payload field search turns your path and value into a small JSON document and asks whether the payload contains it. The function that builds that document, fn_build_jsonb_field_path, now wraps any segment that ends in [N] in a JSON array. So data.order.items[0].sku with value SKU-C8102 becomes:

{ "data": { "order": { "items": [ { "sku": "SKU-C8102" } ] } } }

and the query condition is:

payload_jsonb @> fn_build_jsonb_field_path(p_field_path, p_field_value)

Containment has a defined meaning for arrays in PostgreSQL. An array contains [x] if any of its elements contains x, and position plays no part. That is where "any element" comes from. It is not a shortcut we added on top; it is what @> means, and @> is what the GIN index accelerates.

The alternative was path extraction. With something like payload_jsonb #>> '{data,order,items,0,sku}' = 'SKU-C8102' the 0 would be honored literally. But the GIN index cannot serve that expression. The only ways to make it fast are an expression index for each specific path (not possible when the path is whatever you typed into a search bar) or a sequential scan of the events table. On the table that holds every webhook an organization has received, a sequential scan is not something we want behind a search box.

So the choice came down to an exact position with a slow scan, or any element on the index. We picked the index, and the docs call out the behavior directly.

Value Types

The value is typed the same way it was before arrays were supported. true, false and null match JSON booleans and null, and a value that looks like a number matches a JSON number. Everything else matches as a string. So these all work inside arrays too:

payload.data.lines[0].taxable:true payload.data.lines[0].quantity:3

One consequence: a value that looks like a number is matched as a number, so it won't match a field that stores the same digits as a JSON string.

Using It From the API

The query bar sends the path and value as the payloadField and payloadFieldValue parameters on the list events endpoint. You can call it directly:

curl -G "https://api.hookbase.app/api/events" \
  --data-urlencode "payloadField=data.order.items[0].sku" \
  --data-urlencode "payloadFieldValue=SKU-C8102" \
  -H "Authorization: Bearer whr_your_api_key"

Both parameters must be present for the filter to apply. The path is validated before it gets anywhere near SQL. Each dot-separated segment must be letters, digits or underscores, optionally followed by a single [N]. Anything else returns a 400 with a message showing the accepted format.

Limits

• One array index per segment. items[0].sku and orders[0].items[0].sku are both valid, but nested arrays written as matrix[0][1] are not.
• Five levels deep at most. A path with more than five dot-separated segments returns 400. A segment with an index, like items[0], counts as one level.
• One payload filter at a time. If you type more than one payload.<path>: term in the query bar, the last one wins and the bar shows a warning about the others.
• No positional matching. If you need "exactly the third element," this filter can't express it, for the index reasons above.
• Encrypted fields never match. Fields covered by Field Encryption are stored as ciphertext, so a search on them returns nothing. The rest of the payload is searchable as normal.

Try It

Open Inbound Events, type a payload. path with an array index into the search bar, and look at the results. The Search syntax link under the bar opens the full event search reference, which lists every filter key and now includes the array syntax and the any-element rule.