Deduplicate events

GraphJSON is append-oriented. If an order, subscription, or job emits multiple updates, keep the history and deduplicate at query time when you need its latest state.

Example#

An orders collection might contain:

A naive sum of paid events can overstate current revenue because ord_1 was later refunded.

Extract once#

Start with a CTE:

WITH order_updates AS (
  SELECT
    JSONExtractString(json, 'order_id') AS order_id,
    JSONExtractFloat(json, 'amount') AS amount,
    JSONExtractString(json, 'status') AS status,
    timestamp
  FROM orders
  WHERE JSONExtractString(json, 'event') = 'order_status_changed'
)
SELECT *
FROM order_updates
ORDER BY order_id, timestamp
LIMIT 100

Verify the extracted fields before aggregating.

Select the latest value#

ClickHouse’s argMax(value, order_by) returns the value associated with the greatest ordering value:

WITH order_updates AS (
  SELECT
    JSONExtractString(json, 'order_id') AS order_id,
    JSONExtractFloat(json, 'amount') AS amount,
    JSONExtractString(json, 'status') AS status,
    timestamp
  FROM orders
  WHERE JSONExtractString(json, 'event') = 'order_status_changed'
)
SELECT
  order_id,
  argMax(amount, timestamp) AS amount,
  argMax(status, timestamp) AS status,
  max(timestamp) AS latest_timestamp
FROM order_updates
GROUP BY order_id

See the official ClickHouse argMax reference.

Continue the analysis#

Put the latest-state query in another CTE:

WITH order_updates AS (
  SELECT
    JSONExtractString(json, 'order_id') AS order_id,
    JSONExtractFloat(json, 'amount') AS amount,
    JSONExtractString(json, 'status') AS status,
    timestamp
  FROM orders
  WHERE JSONExtractString(json, 'event') = 'order_status_changed'
),
latest_orders AS (
  SELECT
    order_id,
    argMax(amount, timestamp) AS amount,
    argMax(status, timestamp) AS status
  FROM order_updates
  GROUP BY order_id
)
SELECT
  countIf(status = 'paid') AS paid_orders,
  sumIf(amount, status = 'paid') AS paid_amount,
  countIf(status = 'refunded') AS refunded_orders
FROM latest_orders

Handle ties#

Unix event timestamps have one-second resolution. If the same object can receive multiple updates in one second, log a monotonically increasing version from the source system:

{
  "event": "order_status_changed",
  "order_id": "ord_1",
  "version": 4,
  "status": "refunded",
  "amount": 4900
}

Then order by a tuple:

argMax(status, tuple(timestamp, JSONExtractInt(json, 'version')))

This makes the winner deterministic.

Earliest state#

Use argMin when you need the first value, such as the original acquisition source:

argMin(JSONExtractString(json, 'source'), timestamp)

Do not deduplicate unrelated events#

Repeated page_viewed or product_used events may represent real repeated behavior. Deduplicate only when multiple rows describe versions of the same logical object or when your metric explicitly counts unique entities.