# Query events

[Read this page in HTML](https://www.scopedb.io/docs/guides/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](https://www.scopedb.io/docs/guides/quickstart.md#2-insert-sample-events). It includes payment failures, stock shortages, and an email timeout across three services. Run the examples in [Query in Console](https://www.scopedb.io/docs/guides/console-query.md), or save a query to a file and use `scope query -f <file>` after [connecting a client](https://www.scopedb.io/docs/guides/connect-to-scopedb.md).

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:

```scopeql
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:

| order\_id  | name               |
| ---------- | ------------------ |
| order-1012 | payment\_succeeded |
| order-1012 | checkout\_started  |
| order-1011 | payment\_succeeded |
| order-1011 | checkout\_started  |
| order-1010 | payment\_succeeded |

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:

```scopeql
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:

| order\_id  | service       | message                       |
| ---------- | ------------- | ----------------------------- |
| order-1004 | checkout      | Credit card gateway timed out |
| order-1007 | notifications | Email provider timed out      |
| order-1008 | checkout      | Credit card gateway timed out |

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`](https://www.scopedb.io/docs/reference/functions/string/search.md) 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`](https://www.scopedb.io/docs/reference/functions/string/regexp_like.md) for a regular-expression pattern.

## Extract fields from `var`

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

```scopeql
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
```

| name              | region | gateway      |
| ----------------- | ------ | ------------ |
| checkout\_started | apac   | NULL         |
| payment\_failed   | apac   | credit\_card |

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:

```scopeql
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
```

| region | gateway      | failed\_payments |
| ------ | ------------ | ---------------- |
| apac   | credit\_card | 2                |
| eu     | credit\_card | 1                |

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`](https://www.scopedb.io/docs/guides/scopedb-cli/catalog.md)). 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:

```json
{
  "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:

```scopeql
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:

```scopeql
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
```

| order\_id  | service       | name                 | team        |
| ---------- | ------------- | -------------------- | ----------- |
| order-1004 | checkout      | payment\_failed      | payments    |
| order-1006 | checkout      | payment\_failed      | payments    |
| order-1008 | checkout      | payment\_failed      | payments    |
| order-1010 | inventory     | reservation\_failed  | fulfillment |
| order-1012 | inventory     | reservation\_failed  | fulfillment |
| order-1007 | notifications | notification\_failed | messaging   |

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](https://www.scopedb.io/docs/guides/console-explore.md) to build filter and aggregation views visually, then select **Open in Query** to refine the generated ScopeQL. Continue with [adding indexes](https://www.scopedb.io/docs/guides/add-indexes.md) for recurring filters and searches, or the [query reference](https://www.scopedb.io/docs/reference/commands/stmt-query.md) for additional operators.

## Related contents

* [Quickstart](https://www.scopedb.io/docs/guides/quickstart.md)

* [Explore in Console](https://www.scopedb.io/docs/guides/console-explore.md)

* [Query in Console](https://www.scopedb.io/docs/guides/console-query.md)

* [Query syntax](https://www.scopedb.io/docs/reference/commands/stmt-query.md)

* [Aggregate functions](https://www.scopedb.io/docs/reference/functions/aggregate.md)