Run SQL queries

The SQL notebook gives you a read-only ClickHouse query surface over your GraphJSON collections. Use it for analysis that goes beyond the visualizer: joins, cohorts, deduplication, custom funnels, and window functions.

Open Queries and select New Query.

Understand the table shape#

Each collection is available by name and exposes:

all_events is a virtual table containing every collection in the workspace.

SELECT
  timestamp,
  json
FROM product_events
ORDER BY timestamp DESC
LIMIT 10

GraphJSON scopes the named table to your workspace before the query executes.

Extract JSON fields#

Use a typed extraction function that matches the field:

SELECT
  toDateTime(timestamp) AS occurred_at,
  JSONExtractString(json, 'event') AS event,
  JSONExtractString(json, 'user_id') AS user_id,
  JSONExtractFloat(json, 'amount') AS amount,
  JSONExtractBool(json, 'is_trial') AS is_trial
FROM product_events
ORDER BY timestamp DESC
LIMIT 100

Common functions include JSONExtractString, JSONExtractFloat, JSONExtractInt, and JSONExtractBool. GraphJSON rewrites these to compatible simple extraction functions before execution.

Because ingestion flattens nested objects by default, query a nested field with its dot-separated key:

JSONExtractString(json, 'context.page.path')

See the official ClickHouse JSON functions reference for the broader function family.

Filter by event and time#

SELECT
  timestamp,
  JSONExtractString(json, 'user_id') AS user_id,
  JSONExtractString(json, 'plan') AS plan
FROM product_events
WHERE
  JSONExtractString(json, 'event') = 'signup_completed'
  AND toDateTime(timestamp) >= subtractDays(now(), 30)
ORDER BY timestamp DESC
LIMIT 500

Pass a time zone when the analysis uses local calendar boundaries:

toDateTime(timestamp, 'America/Los_Angeles')

Group and aggregate#

Count completed checkouts by plan:

SELECT
  JSONExtractString(json, 'plan') AS plan,
  count() AS checkouts
FROM product_events
WHERE
  JSONExtractString(json, 'event') = 'checkout_completed'
  AND toDateTime(timestamp) >= subtractDays(now(), 30)
GROUP BY plan
ORDER BY checkouts DESC

Sum revenue stored in integer cents:

SELECT
  JSONExtractString(json, 'currency') AS currency,
  sum(JSONExtractFloat(json, 'amount')) AS revenue_minor_units
FROM billing_events
WHERE JSONExtractString(json, 'event') = 'payment_succeeded'
GROUP BY currency

Do not combine different currencies in one sum.

Create a time series#

The visualization tab expects a Unix timestamp column for a time-series x-axis:

SELECT
  toUnixTimestamp(
    toStartOfDay(
      toDateTime(timestamp, 'America/Los_Angeles')
    )
  ) AS day,
  uniqExact(JSONExtractString(json, 'user_id')) AS active_users
FROM product_events
WHERE
  JSONExtractString(json, 'event') = 'product_used'
  AND toDateTime(timestamp) >= subtractDays(now(), 30)
GROUP BY day
ORDER BY day

Run the query, open Visualization, choose Single Line, set day as the timestamp column, and set active_users as the value column.

Use common table expressions#

CTEs make multi-step queries easier to read:

WITH completed_checkouts AS (
  SELECT
    JSONExtractString(json, 'user_id') AS user_id,
    JSONExtractFloat(json, 'amount') AS amount
  FROM product_events
  WHERE JSONExtractString(json, 'event') = 'checkout_completed'
)
SELECT
  count() AS checkouts,
  uniqExact(user_id) AS customers,
  sum(amount) AS revenue
FROM completed_checkouts

Name each CTE after the data it represents, not the implementation step. completed_checkouts is easier to review than step_1.

Join collections#

Join a product action with an account property:

WITH exports AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    count() AS export_count
  FROM product_events
  WHERE JSONExtractString(json, 'event') = 'report_exported'
  GROUP BY account_id
),
accounts AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    argMax(JSONExtractString(json, 'plan'), timestamp) AS plan
  FROM billing_events
  WHERE JSONExtractString(json, 'event') = 'subscription_status_changed'
  GROUP BY account_id
)
SELECT
  accounts.plan,
  sum(exports.export_count) AS exports
FROM exports
INNER JOIN accounts USING account_id
GROUP BY accounts.plan
ORDER BY exports DESC

Normalize the join key to the same JSON type in both collections.

Query safeguards#

GraphJSON blocks mutation queries and caps the outer result at 2,000 rows. The notebook is for analytics, not for updating or deleting stored events.

Keep queries efficient:

• filter the time range early
• select only needed fields
• aggregate before joining when possible
• avoid splitting by unbounded IDs unless the query needs them
• use LIMIT while exploring

For the GraphJSON-supported query boundary, use GraphJSON SQL compatibility. For broader upstream syntax and functions, use the official ClickHouse SQL reference, then verify non-portable functions in a test collection.

Save and visualize#

Give saved queries names that describe the metric and grain, such as weekly_active_accounts_by_plan. Add a description that records the event definition, time zone, and any important exclusions.

Saved SQL can be visualized and exported with the same dashboard, iframe, and API workflows as collection visualizations.

Use Work with saved SQL queries for the notebook lifecycle and result-to-chart mappings. For slow, empty, oversized, or misleading results, use SQL performance and troubleshooting.