GraphJSON SQL compatibility

GraphJSON provides a read-only ClickHouse SQL notebook over workspace collections. It is not an unrestricted ClickHouse administrative connection.

Use this reference when a query works in another ClickHouse environment but behaves differently in GraphJSON.

Supported query model#

Each collection is exposed as a logical table with:

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

GraphJSON rewrites collection references so the query remains scoped to the authenticated workspace.

all_events is a virtual table that scans every collection in the workspace:

SELECT
  JSONExtractString(json, 'event') AS event,
  count() AS events
FROM all_events
GROUP BY event
ORDER BY events DESC
LIMIT 100

Documented statement surface#

The portable GraphJSON subset is:

• one read-only SELECT query
• common table expressions with WITH
• subqueries
• joins between logical collections
• filters, grouping, ordering, and limits
• ClickHouse scalar and aggregate functions
• window functions
• array expansion where the stored value and function are verified

GraphJSON blocks mutation queries and wraps the result with an outer 2,000-row limit.

Do not use:

• INSERT
• ALTER
• UPDATE
• DELETE
• DROP
• TRUNCATE
• schema or user administration
• direct physical storage-table names
• multiple SQL statements in one notebook run

The notebook is for analytics. Use product retention and deletion controls for supported lifecycle operations.

JSON extraction compatibility#

The documented portable extraction functions are:

GraphJSON rewrites the JSONExtract family used in notebook queries to compatible simple extraction functions.

SELECT
  JSONExtractString(json, 'event') AS event,
  JSONExtractFloat(json, 'amount') AS amount,
  JSONExtractInt(json, 'attempt') AS attempt,
  JSONExtractBool(json, 'is_trial') AS is_trial
FROM product_events
LIMIT 100

Match the extraction function to the stored JSON type. A numeric-looking string is not the same as a JSON number.

Flattened nested fields#

GraphJSON flattens nested objects by default:

{
  "context": {
    "page": {
      "path": "/pricing"
    }
  }
}

becomes a stored key:

context.page.path

Query it literally:

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

Do not interpret dots as SQL object traversal in this case. They are characters in the flattened key.

Preserved nested JSON and arrays#

The logging endpoints accept no_flatten: true, but preserved nested objects and arrays are not part of the small portable extraction subset above.

Before depending on a broader ClickHouse JSON or array function:

1. ingest a representative event in a test collection
2. inspect the stored payload
3. run the exact function in GraphJSON
4. verify empty, null, missing, and mixed-type cases
5. keep a regression query

Prefer default flattening when fields must appear in the point-and-click visualizer.

Do not assume that every function in the latest upstream ClickHouse reference is available in GraphJSON’s deployed version or survives GraphJSON’s compatibility rewriting unchanged.

Collection names#

Use the exact lowercase collection name:

FROM product_events

Recommended collection names are short snake_case identifiers.

Avoid:

• spaces
• punctuation
• SQL keywords
• names that require quoted identifiers
• dynamically concatenated table names

Collection references are supported in ordinary FROM and JOIN positions. Highly dynamic SQL that treats a collection name as a function argument or string is outside the portable subset.

Aliases and query levels#

Use explicit aliases:

WITH events AS (
  SELECT
    toDateTime(timestamp, 'UTC') AS occurred_at,
    JSONExtractString(json, 'event') AS event_name,
    JSONExtractString(json, 'account_id') AS account_id
  FROM product_events
)
SELECT
  event_name,
  uniqExact(account_id) AS accounts
FROM events
GROUP BY event_name
ORDER BY accounts DESC

An alias is available only at query levels where ClickHouse makes it visible. When an alias causes an unknown-identifier error, place the extraction in a CTE and reference the named column from the outer query.

Joins#

Normalize key types before joining:

WITH product AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    count() AS actions
  FROM product_events
  GROUP BY account_id
),
billing AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    argMax(
      JSONExtractString(json, 'plan'),
      timestamp
    ) AS current_plan
  FROM billing_events
  GROUP BY account_id
)
SELECT
  billing.current_plan,
  sum(product.actions) AS actions
FROM product
INNER JOIN billing USING account_id
GROUP BY billing.current_plan

Aggregate before joining when possible. Joining raw high-volume collections can multiply rows and query cost.

Time compatibility#

timestamp is a Unix-second integer.

toDateTime(timestamp, 'UTC')

For a chartable time series, return Unix seconds:

SELECT
  toUnixTimestamp(
    toStartOfDay(toDateTime(timestamp, 'UTC'))
  ) AS day,
  count() AS events
FROM product_events
GROUP BY day
ORDER BY day

Use an IANA zone for calendar reporting. Do not group in server-default time and label the chart with another zone.

Result and runtime boundaries#

The 15-minute wait is a maximum workflow boundary, not a target. Interactive queries should normally finish much sooner.

An inner LIMIT 10000 does not bypass the outer 2,000-row guardrail.

Map SQL results to visualizations#

Return stable, unique column aliases:

SELECT
  JSONExtractString(json, 'plan') AS plan,
  count() AS accounts
FROM product_events
GROUP BY plan

Common compatibility errors#

Compatibility test query#

Keep one test collection with:

{
  "event": "sql_compatibility_checked",
  "event_id": "evt_test_1",
  "account_id": "acct_test",
  "amount": 42.5,
  "attempt": 2,
  "is_trial": true,
  "context": {
    "page": {
      "path": "/test"
    }
  }
}

Run:

SELECT
  JSONExtractString(json, 'event') AS event,
  JSONExtractString(json, 'event_id') AS event_id,
  JSONExtractFloat(json, 'amount') AS amount,
  JSONExtractInt(json, 'attempt') AS attempt,
  JSONExtractBool(json, 'is_trial') AS is_trial,
  JSONExtractString(json, 'context.page.path') AS path
FROM sql_compatibility_events
WHERE JSONExtractString(json, 'event_id') = 'evt_test_1'
LIMIT 1

Use this after a material query-platform change. Delete or expire the test collection according to the environment policy.

Portability checklist#

• Query is one read-only SELECT.
• Collections use simple lowercase names.
• JSON functions match stored types.
• Nested-field strategy is known.
• Result remains useful under 2,000 rows.
• Time zone is explicit.
• Joins normalize key types and grain.
• Visualization columns have stable aliases.
• Non-portable ClickHouse functions have regression tests.

Start with Run SQL queries, then use the SQL cookbook and SQL performance guide.