Query events

Find which orders failed, inspect their payment context, and identify the teams that own the affected services.

Use the 40-event, 12-order dataset from the quickstart. It includes payment failures, stock shortages, and an email timeout across three services. Run the examples in Query in Console, or save a query to a file and use scope query -f <file> after connecting a client.

The expected results assume one copy of the sample in events. If you used a different table name, replace events in the queries. The first four sections only read this table; the JOIN section prepares an additional services table before using it.

Inspect recent rows

Read the latest checkout events from the last hour:

FROM events
WHERE time >= NOW() - '1h'::interval
  AND service = 'checkout'
SELECT time, var['order_id']::string AS order_id, name
ORDER BY time DESC
LIMIT 5

The matching rows are shown below with their timestamps omitted:

Each order has more than one event. Your time column shows the sequence within an order; the order ID connects its records across services.

Search text fields

Find timeout messages across services, retaining the order IDs for follow-up:

FROM events
WHERE search(message, query => 'timed out')
SELECT var['order_id']::string AS order_id, service, message, time
ORDER BY time, order_id

The matching rows are shown below with their timestamps omitted:

Timeouts occur in both payments and email delivery. Add AND service = 'checkout' to the filter when investigating only payment timeouts. The declined card and stock shortages have different messages and are not returned by this search.

search matches tokens, not equivalent meanings: timeout does not match timed out. By default, all query tokens must occur, in any order. Use mode => 'phrase' when those tokens must be adjacent and in order, or regexp_like for a regular-expression pattern.

Extract fields from var

Inspect the checkout events for order-1004, one of the orders with a payment timeout:

FROM events
WHERE service = 'checkout'
SELECT time, name,
    var['order_id']::string AS order_id,
    var['region']::string AS region,
    var['gateway']::string AS gateway
WHERE order_id = 'order-1004'
ORDER BY time
SELECT name, region, gateway

The start event has no gateway field; that information arrives with the payment outcome. Missing fields return NULL. The casts give the extracted values the string type, and the second WHERE filters the order_id column created by SELECT. ScopeQL evaluates clauses in order, so the final projection can omit the timestamp after it has been used for sorting.

Aggregate by dimension

Compare failed payments by region and gateway to decide where to investigate first:

FROM events
WHERE service = 'checkout' AND name = 'payment_failed'
SELECT var['region']::string AS region,
    var['gateway']::string AS gateway
GROUP BY region, gateway
AGGREGATE count() AS failed_payments
ORDER BY failed_payments DESC, region, gateway

The two APAC failures are the timeouts found above; the EU failure is the declined card. No bank-transfer payment failed in this sample. These are counts, not failure rates: comparing rates would also require the successful attempts for each region and gateway. Secondary sort keys keep groups with equal counts in a predictable order.

Join with metadata

The events record what happened to each order, but not which team owns the service. A service directory supplies that separately, so ownership can change without rewriting historical events. Join the directory to the failed operations to see who owns each follow-up.

Prepare the service directory

For this walkthrough, create a services table alongside events in Console Data (or through scope catalog). If that name is already in use, choose a fresh table name and use it in all three steps below. In an application, this table could be maintained from your service catalog:

{
  "name": "services",
  "columns": [
    { "name": "service", "data_type": "string" },
    { "name": "team", "data_type": "string" }
  ]
}

Populate it once with the owners of the three services in the sample:

VALUES
    ('checkout', 'payments'),
    ('inventory', 'fulfillment'),
    ('notifications', 'messaging')
SELECT $0::string AS service, $1::string AS team
INSERT INTO services

Keep one row per service. Running this insert twice creates duplicate mappings, and each matching mapping would produce another copy of an event in the joined result.

Find the owner of each failed operation

Read events and their current owners from the two tables:

FROM events AS e
WHERE e.var['outcome']::string = 'failed'
LEFT JOIN services AS s ON e.service = s.service
SELECT e.var['order_id']::string AS order_id,
    e.service AS service, e.name AS name, s.team AS team
ORDER BY service, order_id

The payment team can review the three failed payments, fulfillment can follow up on the two stock shortages after payment, and messaging can investigate the confirmation failure. The event type and order ID preserve the context needed to inspect each case.

LEFT JOIN keeps a failed operation even if its service is missing from the directory, with NULL for team. Use JOIN instead when you only want operations with a known owner. This query uses the current service directory; determining who owned a service at the time of an event would require ownership history.

Next steps

Use Explore in Console to build filter and aggregation views visually, then select Open in Query to refine the generated ScopeQL. Continue with adding indexes for recurring filters and searches, or the query reference for additional operators.