--- title: "Server-Side Implementation" framework: vue version: "2.1.2" --- # Server-Side Implementation Studio calls your engine's `execute()` method with one or more `AgExecuteRequest` objects. Each request carries an `AgStudioQuery` that your engine translates into your backend's native query language. Return one `AgExecuteResult` per request, in the same order. Implement the optional `executeCube()` method as well if your dashboard uses Pivot Grid, Treemap, Sunburst, or legend-grouped charts. See [Pivot and Hierarchy Queries](#pivot-and-hierarchy-queries). ## Query Anatomy Every field reference in an `AgStudioQuery` is an `AgStudioQueryField` object carrying `key`, `fieldId`, `sourceId`, and optional properties like `aggregation`. ### Core Fields | Field | Purpose | Shape | | --- | --- | --- | | `axes` | Group-by dimensions for aggregation | `[{ dimensions: [{ field: { key, fieldId, sourceId } }] }]` | | `measures` | Aggregated columns | `[{ field: { key, fieldId, sourceId, aggregation: 'sum' } }]` | | `projection` | Raw columns when not aggregating (mutually exclusive with `axes`/`measures`) | `[{ field: { key, fieldId, sourceId } }]` | | `filter` | WHERE clause tree (recursive groups with `combinator`, leaves with `operator`) | `{ combinator: 'and', conditions: [{ field, operator: 'equals', value }] }` | | `sort` | ORDER BY specification | `[{ field: { key, fieldId, sourceId }, direction: 'desc' }]` | | `limit` | LIMIT and OFFSET | `{ count: 100, offset: 20 }` | | `computedFields` | Computed columns with expression ASTs and evaluation phase | See [Computed Fields](#computed-fields) | ### Additional Fields These fields appear when the dashboard uses features that require them. If your engine does not support a given field, the corresponding UI feature will not work correctly. | Field | Purpose | | --- | --- | | `having` | Post-aggregation filter (SQL `HAVING`) | | `window` | Window functions (`rank`, `denseRank`, `rowNumber`) | | `joins` | Multi-source joins (see [Sources and Joins](#sources-and-joins)) | | `scope` | Dimension-member slice for slicer/page-filter semantics | | `distinct` | Row deduplication for projection-mode queries | | `from` | Derived-table composition (SQL `FROM (SELECT ...)`) | Properties available on the `AgStudioQuery` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `axes` | `AgAxisDefinition[]` | | Dimension axes for OLAP aggregation. Empty `axes: []` combined with measures yields a single grand-total group. Mutually exclusive with `projection`. | | `measures` | `AgMeasureDefinition[]` | | Measures aggregated at each cell. Required when `axes` is present. | | `projection` | `AgDimensionDefinition[]` | | Output columns for detail/raw-row queries. Mutually exclusive with `axes`/`measures`. | | `joins` | `AgJoinClause[]` | | Ordered join chain - the query's source topology. Omit for a single-source query. Engines must consume clauses in order and must not perform schema lookup to reconstruct the topology. | | `filter` | `AgStudioFilterDefinition` | | Row-level predicate applied BEFORE any aggregation (SQL `WHERE` semantics). Leaves reference source fields on the joined plan. Affects both the result set and the denominator used by measures with `totalsScope: 'filtered'`. | | `having` | `AgStudioFilterDefinition` | | Post-aggregation predicate applied to cells, AFTER measures are computed (SQL `HAVING` semantics). Leaves must reference measure aliases or dimension fields (via `AgExprFieldRef`); row-level source fields are rejected. Cells the predicate rejects are dropped; the measure denominators are NOT recomputed. | | `scope` | `AgScopeDefinition` | | Dimension-member slice (MDX-inspired). Restricts the visible cells to the chosen members but - unlike `filter` - does NOT remove the excluded rows from the denominator used by measures with `totalsScope: 'unrestricted'`. Use for slicer/page-filter semantics where "% of total" should still divide by the full dataset. | | `sort` | `AgStudioSortDefinition[]` | | Result ordering (SQL `ORDER BY`). Applied AFTER aggregation, computed fields, and windows. Multiple entries form a tie-break chain in array order. Sort fields must reference output columns (measure aliases, dimension fields, or computed-field outputs). | | `window` | `AgStudioWindowDefinition[]` | | Window function evaluations (SQL `OVER (...)`). Each entry produces an output column computed over a partition of rows defined by `partitionBy` and ordered by `orderBy`. Evaluated after aggregation; outputs may be referenced by post-aggregation computed fields and `having`. | | `limit` | `AgStudioLimitDefinition` | | Hard cap on returned rows, with optional offset (SQL `LIMIT` / `OFFSET`). Applied as the final step after `sort`; pagination is therefore stable only when `sort` produces a total ordering. | | `distinct` | `boolean` | | Projection-mode row deduplication (SQL `SELECT DISTINCT`). In aggregation mode, use distinct-within-measure aggregations (`countd` etc.) instead. | | `computedFields` | `AgStudioComputedFieldDefinition[]` | | Evaluation schedule for computed expression fields. Topologically sorted - engines evaluate in array order. Each entry carries the expression in `AgStudioExpression` AST form and an explicit evaluation phase. Synthetic entries decompose cross-source expressions into single-source intermediates. Absent when the query has no expression fields. | | `from` | `AgStudioQuery` | | Derived-table composition (SQL `FROM (SELECT ...)`). Engines execute the inner query, materialise its result, and run the outer query over those rows. Each materialised level costs memory and latency, so engines should consider imposing a depth limit and rejecting deeper nesting with an error rather than silently truncating. | ### Computed Fields `computedFields` is a topologically sorted evaluation schedule. Each entry carries an expression AST and an explicit evaluation phase: - **`phase: 'pre-agg'`**: evaluated on source rows before grouping (SQL column expression) - **`phase: 'post-agg'`**: evaluated on grouped output after measures are computed - **`synthetic: true`**: internally generated intermediates; exclude from user-facing result columns Engines evaluate entries in array order. Each entry's dependencies are satisfied by prior entries or source scans. Expression nodes are either operations (`{ operator, inputs, options? }`) or leaves: field references (`{ field: AgStudioQueryField }`), values (`{ type, value }`, e.g. `{ type: 'number', value: 2 }`), or alias references (`{ ref: string }`). ## Field Identifiers Every field reference in a query is an `AgStudioQueryField`: | Property | Where it comes from | What to use it for | | --- | --- | --- | | `fieldId` | Source-qualified column ID from `getDataSources()`: `"sales.revenue"`. For computed fields (`sourceId: ''`), a bare identifier like `"pct_of_total"`. | Map to a backend column. Strip the source prefix for source fields; use as-is for computed fields. | | `sourceId` | The `id` of the source the field belongs to: `"sales"`. | Pick which backend table or endpoint the query targets. | | `key` | An opaque string Studio builds per-field per-query. | The column name in your result rows. Your result `rows[i][field.key]` must round-trip cleanly. **Treat `key` as opaque; do not parse it.** | Measure fields also carry `aggregation` (e.g. `'sum'`, `'avg'`, `'count'`). Access it via `measure.field.aggregation`. Properties available on the `AgStudioQueryField` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `key` | `string` | | Composite identity key and output column alias. Treat as opaque. When the wrapping definition (window / measure / dimension / projection entry) carries an `as`, that alias - not this `key` - becomes the output column identity. This `key` then only carries the field's *source* identity for resolution. | | `sourceId` | `string` | | Owning source ID; `''` for computed/inline fields. | | `fieldId` | `string` | | The underlying field identifier within the source. | | `aggregation` | `AgAggregationFunction` | | Aggregation function for measure fields; omitted for raw dimensions. | | `determinant` | `string` | | Additional identity component for fields that would otherwise collide on `(sourceId, fieldId, aggregation)` - e.g. window outputs with different orderings. | | `sourceAlias` | `string` | | Alias for self-joins - disambiguates multiple instances of the same underlying source in one query. | | `expression` | `AgStudioExpression` | | Inline expression AST - present for computed / inline fields (where `sourceId === ''`) and also the mechanism for granularity transforms such as `dateTrunc` and numeric bucketing. | | `isMeasure` | `boolean` | | Inline-field intent disambiguator. For inline fields, `true` marks a measure (lives under `AgStudioQuery.measures`), `false` marks a dimension (lives under `axes[].dimensions`). Validator rejects mismatched slot/intent pairs. | | `dataType` | `AgDataType` | | Optional data-type hint. Studio-produced fields set this from the schema; hand-constructed queries may omit it. | ## Filter Structure The `filter` and `having` fields share the same recursive tree structure: **`AgStudioFilterGroup`**: a boolean combinator (`'and'`, `'or'`, or `'not'`) wrapping child nodes: ```ts { combinator: 'and', conditions: [/* AgStudioFilterCondition | AgStudioFilterGroup */], } ``` **`AgStudioFilterCondition`**: a leaf predicate on a single field: ```ts { field: { key, fieldId, sourceId }, operator: 'equals', value: 'EMEA', } ``` Walk the tree recursively: groups become parenthesised boolean expressions; conditions become backend predicates. See the [reference examples](#reference-examples) for complete filter translation. Properties available on the `AgStudioFilterCondition` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `field` | `AgStudioQueryField` | | The field tested by the predicate. | | `operator` | `"contains" \| "equals" \| "between" \| "endsWith" \| "startsWith" \| "notEqual" \| "greaterThan" \| "lessThan" \| "greaterThanOrEqual" \| "lessThanOrEqual" \| "inRelativeRange" \| "isNull" \| "isNotNull" \| "isUndefined" \| "isNotUndefined" \| "isBlank" \| "isNotBlank" \| "isNaN" \| "notContains" \| "isIn" \| "isTrue" \| "isFalse"` | | The comparison applied between `field` and `value`. `rank` is excluded rank-style filtering is expressed via `AgDimensionDefinition.topN`. | | `value` | `AgPrimitive \| AgPrimitive[] \| AgRelativeRangeFilterValue \| [AgPrimitive, AgPrimitive]` | | `undefined` for `isNull` / `isNotNull` / `isTrue` / `isFalse`; a 2-element array for `between` (check `operator` to distinguish from `isIn`); an array for `isIn`; a relative-range descriptor for `inRelativeRange`; single primitive otherwise. | | `options` | `{ crossFilter?: boolean; scopeDerived?: boolean; matchCase?: boolean; escapeChar?: string }` | | Routing metadata carried to the engine and explain output. | Properties available on the `AgStudioFilterGroup` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `combinator` | `"and" \| "or" \| "not"` | | How `conditions` are combined. `not` negates the conjunction of its children (`NOT (c1 AND c2 AND ...)`); to negate a single predicate, wrap it in a `not` group with one child. | | `conditions` | `AgStudioFilterDefinition[]` | | Child filters - leaf conditions or nested groups. | ## Result Format Return an `AgExecuteResult` per request, discriminated on `dataShape`. Return `'rows'` (default) or `'columns'` depending on `options.shape`. > **Note** > > The `dataShape` discriminator on each result must match the data you return. If you set `dataShape: 'columns'` but populate `rows` (or vice versa), widgets will render empty. Both shapes carry an `AgResultMetadata` object. `rowCount` is the number of rows in the returned result. When your engine applies a `limit`, set `totalRowCount` to the pre-limit row count. ### Rows Format (default) ```ts return { dataShape: 'rows', rows: [ { 'sales.region': 'EMEA', 'sales.revenue': 123456 }, { 'sales.region': 'APAC', 'sales.revenue': 234567 }, ], metadata: { rowCount: 2, totalRowCount: 5432 }, }; ``` ### Columns Format ```ts const columns = new Map>(); columns.set('sales.region', ['EMEA', 'APAC']); columns.set('sales.revenue', [123456, 234567]); return { dataShape: 'columns', columns, metadata: { rowCount: 2 }, }; ``` Properties available on the `AgExecuteRequest<TShape extends AgResultShape = AgResultShape>` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `query` | `AgStudioQuery` | | The query to execute against the data source. | | `options` | `AgRequestOptions` | | Per-request options controlling result shape, cancellation, and validation. | | `info` | `AgEngineCallInfo` | | Advisory metadata for logging, tracing, and batch coalescing. | Properties available on the `AgRowsResult` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `metadata` | `AgResultMetadata` | | Row count and optional pre-limit total. | | `dataShape` | `"rows"` | | Discriminator - always `'rows'` for this shape. | | `rows` | `Record[]` | | Result rows keyed by output field alias. | Properties available on the `AgColumnsResult` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `metadata` | `AgResultMetadata` | | Row count and optional pre-limit total. | | `dataShape` | `"columns"` | | Discriminator - always `'columns'` for this shape. | | `columns` | `AgColumnsMap` | | Column arrays keyed by output field alias. | ## Sources and Joins When a widget pulls fields from multiple related sources (declared via [Relationships](https://www.ag-grid.com/studio/vue/data#relationships)), Studio produces a single `AgStudioQuery` with `joins` populated. Your `execute()` receives one query that spans multiple sources. Each field's `sourceId` and `fieldId` identify which backend table it belongs to, so your translator can resolve every reference. If your backend does not support joins, throw a descriptive error when a query contains `joins`. ## Batching and Cancellation Studio calls `execute(...requests)` with every request in the current render cycle. Each request carries an `info` object: - **`batchId`:** All requests in the same `execute()` call share a `batchId`. Use it to coalesce backend round-trips (e.g. one combined request per batch). When absent, treat each request independently. - **`queryId`:** Distinguishes independent queries from the same widget (e.g. `'rows'` vs `'grandTotal'`). Studio uses `widgetId + queryId` as the cancellation key. Each request carries an optional `options.signal: AbortSignal`. Propagate it into your backend call to cancel superseded queries: ```ts const res = await fetch(url, { signal: request.options?.signal }); ``` ## Errors Throw from `execute()` when the backend fails. Any failed request in a batch will cause the whole batch to fail. This is to avoid partial data flowing into Studio. ```ts async execute(...requests: AgExecuteRequest[]): Promise { try { return await this.runQueries(requests); } catch (error) { throw { message: 'Backend query failed', cause: error }; } } ``` ## Pivot and Hierarchy Queries Some widgets need cube-aggregated data that `execute()` can't express: multiple independent grouping axes with dense cell coverage, and subtotal placement within a hierarchy. For these, Studio calls an optional second method, `executeCube(...requests: AgCubeResolvedExecuteRequest[])`, and expects one `AgCubeResult` per request - axis tuples and a sparse cell store, not flat rows. `executeCube` is required for: - **Pivot Grid** widgets, always. - **Treemap** and **Sunburst** widgets, always - hierarchy subtotal placement (`includeSubtotals`) has no flat equivalent. - Any chart configured with a **legend field** - legend grouping is a pivot query under the hood. A chart's own group-by fields, without a legend field, never need `executeCube`: they're a flat `execute()` query regardless of how many are configured. ### If Your Engine Doesn't Implement It `executeCube` is optional. If you omit it: - Pivot Grid, Treemap, and Sunburst widget types are omitted from the widget picker entirely, with a validation warning explaining why. - A chart configured with a legend field still renders, but without the legend grouping - one ungrouped series, plus a validation warning. Properties available on the `AgCubeResolvedExecuteRequest` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `query` | `AgCubeResolvedStudioQuery` | | AgCubeResolvedStudioQuery | | `options` | `AgCubeExecuteOptions` | | AgCubeExecuteOptions | | `info` | `AgEngineCallInfo` | | AgEngineCallInfo | Properties available on the `AgCubeResult<TValue = AgPrimitive>` interface. | Property | Type | Default | Description | | --- | --- | --- | --- | | `dataShape` | `"cube"` | | "cube" | | `axes` | `AgResultAxis[]` | | Axes in query order; length ≥ 2. | | `measures` | `AgResultMeasure[]` | | Ordered measure descriptors; each key matches a cell record key. | | `cells` | `AgCubeCells` | | Sparse N-D cell store keyed by axis-tuple index vector. | | `metadata` | `AgResultMetadata` | | Row count and optional pre-limit total. | ## Reference Examples > **Note** > > These are reference implementations for learning purposes. They are not production-ready integrations and will not cover every query feature. Use them as starting points for your own engine. ### ClickHouse over HTTP Translates `AgStudioQuery` to ClickHouse SQL, posts it over HTTP, and returns the response. The dashboard queries ClickHouse's [uk_price_paid](https://clickhouse.com/docs/getting-started/example-datasets/uk-price-paid) dataset (~28M rows) without downloading any of it. #### ClickHouse Server-Side ```ts import type { AgDataEngine, AgDataSourcesDefinition, AgExecuteRequest, AgExecuteResult, AgFieldDefinition, AgReportState, AgResultShape, AgStudioFilterDefinition, AgStudioMode, AgStudioQuery, AgStudioQueryField, } from "ag-studio"; import { AgStudio } from "ag-studio-vue3"; import { createApp, defineComponent, ref, shallowRef } from "vue"; // ─── Source metadata ──────────────────────────────────────────────────────── // // The example queries the `uk_price_paid` dataset on ClickHouse's public // playground (https://play.clickhouse.com). The schema is fixed, read-only, // and CORS-enabled. We expose the columns the dashboard cares about. const CLICKHOUSE_URL = "https://play.clickhouse.com/?user=play"; /** * The source ID AG Studio uses internally. Field IDs are prefixed with this * (e.g. `uk_price_paid.county`) so queries can be dispatched per source. * The SQL engine sees the bare column name after the dot. */ const SOURCE_ID = "uk_price_paid"; /** * The fully-qualified table name on the ClickHouse side. The engine keeps * the source ID and table name decoupled so the dashboard model isn't tied * to physical schema layout. */ const CLICKHOUSE_TABLE = "default.uk_price_paid"; const FIELDS: AgFieldDefinition[] = [ { id: "county", format: "textFormat" }, { id: "town", format: "textFormat" }, { id: "type", format: "textFormat" }, { id: "duration", format: "textFormat" }, { id: "is_new", format: "integerFormat" }, { id: "price", format: "currencyFormat" }, { id: "date", format: "dateFormat" }, ]; // ─── AgStudioQuery → ClickHouse SQL translation ───────────────────────────── function extractColumnName(fieldId: string): string { const dotIndex = fieldId.indexOf("."); return dotIndex >= 0 ? fieldId.slice(dotIndex + 1) : fieldId; } function quoteIdentifier(col: string): string { return `\`${col.replace(/`/g, "``")}\``; } function fieldToSql(ref: AgStudioQueryField): string { return quoteIdentifier(extractColumnName(ref.fieldId)); } function aggToSql(ref: AgStudioQueryField): string { const col = fieldToSql(ref); const { aggregation } = ref; if (!aggregation) return col; switch (aggregation) { case "sum": return `sum(${col})`; case "avg": return `avg(${col})`; case "count": return `count(${col})`; case "countd": return `uniqExact(${col})`; case "min": return `min(${col})`; case "max": return `max(${col})`; case "first": return `any(${col})`; case "last": return `anyLast(${col})`; default: return col; } } /** * Escape a SQL literal for direct interpolation. ClickHouse's HTTP * parameterised query syntax (`{name:Type}`) requires explicit types, which * adds friction for a small demo. The values we emit here are all typed by * the column they compare against, so inline quoting is safe. */ function sqlLiteral(value: unknown): string { if (value == null) return "NULL"; if (typeof value === "number") return String(value); if (value instanceof Date) return `'${value.toISOString().slice(0, 10)}'`; return `'${String(value).replace(/'/g, "''")}'`; } function filterToSql(filter: AgStudioFilterDefinition): string { if ("combinator" in filter) { const parts = filter.conditions.map(filterToSql); if (filter.combinator === "not") return `NOT (${parts.join(" AND ")})`; return `(${parts.join(` ${filter.combinator.toUpperCase()} `)})`; } const col = quoteIdentifier(extractColumnName(filter.field.fieldId)); const { operator, value } = filter; switch (operator) { case "equals": if (Array.isArray(value)) return `${col} IN (${value.map(sqlLiteral).join(", ")})`; return `${col} = ${sqlLiteral(value)}`; case "notEqual": if (Array.isArray(value)) return `${col} NOT IN (${value.map(sqlLiteral).join(", ")})`; return `${col} != ${sqlLiteral(value)}`; case "greaterThan": return `${col} > ${sqlLiteral(value)}`; case "greaterThanOrEqual": return `${col} >= ${sqlLiteral(value)}`; case "lessThan": return `${col} < ${sqlLiteral(value)}`; case "lessThanOrEqual": return `${col} <= ${sqlLiteral(value)}`; case "isNull": return `${col} IS NULL`; case "isNotNull": return `${col} IS NOT NULL`; case "between": { const [from, to] = value as [unknown, unknown]; return `${col} BETWEEN ${sqlLiteral(from)} AND ${sqlLiteral(to)}`; } case "isIn": return `${col} IN (${(value as unknown[]).map(sqlLiteral).join(", ")})`; default: return `${col} = ${sqlLiteral(value)}`; } } function agStudioQueryToSql(query: AgStudioQuery): string { const { axes, measures, projection, filter, sort, limit } = query; const selectCols: string[] = []; const groupByCols: string[] = []; const dimensions = axes?.[0]?.dimensions ?? []; for (const dim of dimensions) { selectCols.push(`${fieldToSql(dim.field)} AS \`${dim.field.key}\``); groupByCols.push(fieldToSql(dim.field)); } for (const m of measures ?? []) { selectCols.push(`${aggToSql(m.field)} AS \`${m.field.key}\``); } for (const dim of projection ?? []) { selectCols.push(`${fieldToSql(dim.field)} AS \`${dim.field.key}\``); } let sql = `SELECT ${selectCols.join(", ")} FROM ${CLICKHOUSE_TABLE}`; if (filter) sql += ` WHERE ${filterToSql(filter)}`; if (groupByCols.length > 0) sql += ` GROUP BY ${groupByCols.join(", ")}`; if (sort?.length) { const parts = sort.map( (s) => `\`${s.field.key}\` ${s.direction.toUpperCase()}`, ); sql += ` ORDER BY ${parts.join(", ")}`; } // Cap result size; the source table is ~28M rows. List-filters and // grids need a reasonable ceiling even without an explicit limit. const count = limit?.count ?? 5000; sql += ` LIMIT ${count}`; if (limit?.offset) sql += ` OFFSET ${limit.offset}`; return sql; } function applyRowId( query: AgStudioQuery, rows: Record[], ): void { const rowNumberField = query.window?.find( (w) => w.functionType === "rowNumber", ); if (rowNumberField?.as) { const key = rowNumberField.as; const offset = query.limit?.offset ?? 0; rows.forEach((row, i) => { row[key] = offset + i; }); return; } const dimensions = query.axes?.[0]?.dimensions ?? []; const rowIdField = query.computedFields?.[0]; if (!dimensions.length || !rowIdField) return; const dimensionKeys = dimensions.map((d) => d.field.key); for (const row of rows) { row[rowIdField.field.key] = dimensionKeys .map((key) => row[key] instanceof Date ? (row[key] as Date).toISOString() : String(row[key]), ) .join(""); } } // ─── ClickHouse HTTP execution ────────────────────────────────────────────── interface ClickHouseJsonResponse { data: Record[]; rows: number; } async function runClickHouse( sql: string, signal?: AbortSignal, ): Promise[]> { const body = `${sql} FORMAT JSON`; const res = await fetch(CLICKHOUSE_URL, { method: "POST", headers: { "Content-Type": "text/plain" }, body, signal, }); if (!res.ok) { const text = await res.text(); throw new Error( `ClickHouse query failed: ${res.status} ${text.slice(0, 500)}`, ); } const json = (await res.json()) as ClickHouseJsonResponse; return json.data; } // ─── ClickHouseDataEngine ─────────────────────────────────────────────────── class ClickHouseDataEngine implements AgDataEngine { /** Fully-qualified field IDs whose values need Date coercion. */ private readonly dateFieldKeys = new Set( FIELDS.filter((f) => f.format === "dateFormat").map( (f) => `${SOURCE_ID}.${f.id}`, ), ); /** Fully-qualified field IDs whose values ClickHouse returns as strings but AG Studio expects numeric. */ private readonly numericFieldKeys = new Set( FIELDS.filter( (f) => f.format === "currencyFormat" || f.format === "integerFormat", ).map((f) => `${SOURCE_ID}.${f.id}`), ); getDataSources(): AgDataSourcesDefinition { // ClickHouse's playground is read-only and pre-populated, so there's // no seeding step - we just declare the source and its metadata. return { sources: [{ id: SOURCE_ID, data: [], fields: FIELDS }], }; } async execute( ...requests: AgExecuteRequest[] ): Promise { return Promise.all(requests.map((r) => this.executeOne(r))); } private async executeOne( request: AgExecuteRequest, ): Promise { const { query, options, info } = request; const shape = options?.shape ?? "rows"; const sql = agStudioQueryToSql(query); console.log("[ClickHouse engine] execute:", { ...info, shape, sql }); const rawRows = await runClickHouse(sql, options?.signal); const rows = this.coerceRows(rawRows, query); applyRowId(query, rows); if (shape === "columns") { const columns = new Map(); for (const row of rows) { for (const [k, v] of Object.entries(row)) { let col = columns.get(k); if (!col) { col = []; columns.set(k, col); } col.push(v); } } return { dataShape: "columns", columns, metadata: { rowCount: rows.length }, }; } return { dataShape: "rows", rows, metadata: { rowCount: rows.length } }; } /** * ClickHouse's JSON format returns every cell as a string. Coerce values * back to the types AG Studio's formatters expect - dates to Date objects, * currency/integer columns to numbers. */ private coerceRows( rows: Record[], query: AgStudioQuery, ): Record[] { if (rows.length === 0) return rows; // Build { outputKey -> fullyQualifiedFieldId } mapping from the query. const keyToFieldId = new Map(); for (const dim of query.axes?.[0]?.dimensions ?? []) { keyToFieldId.set(dim.field.key, dim.field.fieldId); } for (const m of query.measures ?? []) { keyToFieldId.set(m.field.key, m.field.fieldId); } for (const dim of query.projection ?? []) { keyToFieldId.set(dim.field.key, dim.field.fieldId); } for (const row of rows) { for (const [key, fieldId] of keyToFieldId) { const val = row[key]; if (val == null) continue; if (this.dateFieldKeys.has(fieldId) && !(val instanceof Date)) { row[key] = new Date(val as string); } else if ( this.numericFieldKeys.has(fieldId) && typeof val === "string" ) { const n = Number(val); if (!Number.isNaN(n)) row[key] = n; } } } return rows; } } // ─── Studio component ─────────────────────────────────────────────────────── const VueExample = defineComponent({ template: `
`, components: { "ag-studio": AgStudio, }, setup() { const mode = ref("view"); const data = shallowRef(); const initialState = ref({ panels: { filters: { collapsed: true, }, }, pages: [ { id: "properties-dashboard", widgets: { "county-filter": { type: "list-filter", dataMapping: { value: [{ id: "uk_price_paid.county" }] }, format: { title: { enabled: true, text: "Filter by County" } }, }, "type-filter": { type: "list-filter", dataMapping: { value: [{ id: "uk_price_paid.type" }] }, format: { title: { enabled: true, text: "Filter by Property Type" }, }, }, "avg-by-county": { type: "bar-chart-grouped", dataMapping: { categoryKey: [{ id: "uk_price_paid.county" }], valueKey: [{ id: "uk_price_paid.price", aggregation: "avg" }], }, format: { title: { enabled: true, text: "Average Price by County", typography: { fontSize: 16, fontWeight: "bold" }, }, }, }, "avg-by-type": { type: "bar-chart-grouped", dataMapping: { categoryKey: [{ id: "uk_price_paid.type" }], valueKey: [{ id: "uk_price_paid.price", aggregation: "avg" }], }, format: { title: { enabled: true, text: "Average Price by Property Type", typography: { fontSize: 16, fontWeight: "bold" }, }, }, }, "summary-grid": { type: "grid", dataMapping: { cols: [ { id: "uk_price_paid.county" }, { id: "uk_price_paid.type" }, { id: "uk_price_paid.price", aggregation: "count" }, { id: "uk_price_paid.price", aggregation: "avg" }, { id: "uk_price_paid.price", aggregation: "min" }, { id: "uk_price_paid.price", aggregation: "max" }, ], }, format: { title: { enabled: true, text: "Price Summary", typography: { fontSize: 16, fontWeight: "bold" }, }, style: { grandTotalRow: { enabled: true }, theme: { rowHeight: 28 }, }, }, }, }, widgetLayout: { "county-filter": { xTrack: 0, yTrack: 0, xSpan: 6, ySpan: 12 }, "type-filter": { xTrack: 0, yTrack: 12, xSpan: 6, ySpan: 12 }, "avg-by-county": { xTrack: 6, yTrack: 0, xSpan: 9, ySpan: 24 }, "avg-by-type": { xTrack: 15, yTrack: 0, xSpan: 9, ySpan: 24 }, "summary-grid": { xTrack: 0, yTrack: 24, xSpan: 24, ySpan: 14 }, }, filter: { page: [] }, }, ], selectedPageId: "properties-dashboard", }); const toggleMode = () => { mode.value = mode.value === "edit" ? "view" : "edit"; }; // Lazily create the engine on demand - Studio then owns its lifecycle // (init → execute → dispose) once the `data` property is set. const loadData = () => { data.value = new ClickHouseDataEngine(); }; return { mode, data, initialState, toggleMode, loadData, }; }, }); const app = createApp(VueExample); app.mount("#app"); ``` [Live example: ClickHouse Server-Side](https://www.ag-grid.com/studio/examples/server-side-data-implementation/clickhouse-serverside/vue3/) Contains HM Land Registry data © Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0. ([Source](https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads), [Fields](https://www.gov.uk/guidance/about-the-price-paid-data)) ### ClickHouse Server-Side Pivot Adds `executeCube` (see [Pivot and Hierarchy Queries](#pivot-and-hierarchy-queries)) to the ClickHouse engine above, so it can also serve Pivot Grid, Treemap, and Sunburst widgets. The implementation groups by both axes' dimensions in a single query, then reshapes the flat result into the cube shape. #### ClickHouse Server-Side Pivot ```ts import type { AgAggregationFunction, AgCubeMeasureDefinition, AgCubeResolvedAxisDefinition, AgCubeResolvedExecuteRequest, AgCubeResult, AgDataEngine, AgDataSourcesDefinition, AgDataType, AgDimensionDefinition, AgExecuteRequest, AgExecuteResult, AgFieldDefinition, AgPublicColumnMetadata, AgReportState, AgResultAxis, AgResultShape, AgResultTuple, AgStudioFilterDefinition, AgStudioMode, AgStudioQuery, AgStudioQueryField, Primitive, } from "ag-studio"; import { AgStudio } from "ag-studio-vue3"; import { createApp, defineComponent, ref, shallowRef } from "vue"; // ─── Source metadata ──────────────────────────────────────────────────────── // // Same `uk_price_paid` dataset as the `clickhouse-serverside` example, on // ClickHouse's public playground (https://play.clickhouse.com). The schema is // fixed, read-only, and CORS-enabled. const CLICKHOUSE_URL = "https://play.clickhouse.com/?user=play"; /** * The source ID AG Studio uses internally. Field IDs are prefixed with this * (e.g. `uk_price_paid.county`) so queries can be dispatched per source. * The SQL engine sees the bare column name after the dot. */ const SOURCE_ID = "uk_price_paid"; /** * The fully-qualified table name on the ClickHouse side. The engine keeps * the source ID and table name decoupled so the dashboard model isn't tied * to physical schema layout. */ const CLICKHOUSE_TABLE = "default.uk_price_paid"; const FIELDS: AgFieldDefinition[] = [ { id: "county", format: "textFormat" }, { id: "town", format: "textFormat" }, { id: "type", format: "textFormat" }, { id: "duration", format: "textFormat" }, { id: "is_new", format: "integerFormat" }, { id: "price", format: "currencyFormat" }, { id: "date", format: "dateFormat" }, ]; // ─── AgStudioQuery → ClickHouse SQL translation ───────────────────────────── function extractColumnName(fieldId: string): string { const dotIndex = fieldId.indexOf("."); return dotIndex >= 0 ? fieldId.slice(dotIndex + 1) : fieldId; } function quoteIdentifier(col: string): string { return `\`${col.replace(/`/g, "``")}\``; } function fieldToSql(ref: AgStudioQueryField): string { return quoteIdentifier(extractColumnName(ref.fieldId)); } function aggToSql(ref: AgStudioQueryField): string { const col = fieldToSql(ref); const { aggregation } = ref; if (!aggregation) return col; switch (aggregation) { case "sum": return `sum(${col})`; case "avg": return `avg(${col})`; case "count": return `count(${col})`; case "countd": return `uniqExact(${col})`; case "min": return `min(${col})`; case "max": return `max(${col})`; case "first": return `any(${col})`; case "last": return `anyLast(${col})`; default: return col; } } function sqlLiteral(value: unknown): string { if (value == null) return "NULL"; if (typeof value === "number") return String(value); if (value instanceof Date) return `'${value.toISOString().slice(0, 10)}'`; return `'${String(value).replace(/'/g, "''")}'`; } function filterToSql(filter: AgStudioFilterDefinition): string { if ("combinator" in filter) { const parts = filter.conditions.map(filterToSql); if (filter.combinator === "not") return `NOT (${parts.join(" AND ")})`; return `(${parts.join(` ${filter.combinator.toUpperCase()} `)})`; } const col = quoteIdentifier(extractColumnName(filter.field.fieldId)); const { operator, value } = filter; switch (operator) { case "equals": if (Array.isArray(value)) return `${col} IN (${value.map(sqlLiteral).join(", ")})`; return `${col} = ${sqlLiteral(value)}`; case "notEqual": if (Array.isArray(value)) return `${col} NOT IN (${value.map(sqlLiteral).join(", ")})`; return `${col} != ${sqlLiteral(value)}`; case "greaterThan": return `${col} > ${sqlLiteral(value)}`; case "greaterThanOrEqual": return `${col} >= ${sqlLiteral(value)}`; case "lessThan": return `${col} < ${sqlLiteral(value)}`; case "lessThanOrEqual": return `${col} <= ${sqlLiteral(value)}`; case "isNull": return `${col} IS NULL`; case "isNotNull": return `${col} IS NOT NULL`; case "between": { const [from, to] = value as [unknown, unknown]; return `${col} BETWEEN ${sqlLiteral(from)} AND ${sqlLiteral(to)}`; } case "isIn": return `${col} IN (${(value as unknown[]).map(sqlLiteral).join(", ")})`; default: return `${col} = ${sqlLiteral(value)}`; } } function agStudioQueryToSql(query: AgStudioQuery): string { const { axes, measures, projection, filter, sort, limit } = query; const selectCols: string[] = []; const groupByCols: string[] = []; const dimensions = axes?.[0]?.dimensions ?? []; for (const dim of dimensions) { selectCols.push(`${fieldToSql(dim.field)} AS \`${dim.field.key}\``); groupByCols.push(fieldToSql(dim.field)); } for (const m of measures ?? []) { selectCols.push(`${aggToSql(m.field)} AS \`${m.field.key}\``); } for (const dim of projection ?? []) { selectCols.push(`${fieldToSql(dim.field)} AS \`${dim.field.key}\``); } let sql = `SELECT ${selectCols.join(", ")} FROM ${CLICKHOUSE_TABLE}`; if (filter) sql += ` WHERE ${filterToSql(filter)}`; if (groupByCols.length > 0) sql += ` GROUP BY ${groupByCols.join(", ")}`; if (sort?.length) { const parts = sort.map( (s) => `\`${s.field.key}\` ${s.direction.toUpperCase()}`, ); sql += ` ORDER BY ${parts.join(", ")}`; } const count = limit?.count ?? 5000; sql += ` LIMIT ${count}`; if (limit?.offset) sql += ` OFFSET ${limit.offset}`; return sql; } /** * Builds a single flat `SELECT ... GROUP BY` covering every cube axis at once. * Only `{ kind: 'fixed' }` axes are supported - the shapes this example needs. */ function cubeQueryToSql( axisLevels: AgDimensionDefinition[][], measures: AgCubeMeasureDefinition[], filter: AgStudioFilterDefinition | undefined, ): string { const selectCols: string[] = []; const groupByCols: string[] = []; for (const dim of axisLevels.flat()) { selectCols.push(`${fieldToSql(dim.field)} AS \`${dim.field.key}\``); groupByCols.push(fieldToSql(dim.field)); } for (const m of measures) { selectCols.push(`${aggToSql(m.field)} AS \`${m.field.key}\``); } let sql = `SELECT ${selectCols.join(", ")} FROM ${CLICKHOUSE_TABLE}`; if (filter) sql += ` WHERE ${filterToSql(filter)}`; sql += ` GROUP BY ${groupByCols.join(", ")}`; return sql; } // ─── ClickHouse HTTP execution ────────────────────────────────────────────── interface ClickHouseJsonResponse { data: Record[]; rows: number; } async function runClickHouse( sql: string, signal?: AbortSignal, ): Promise[]> { const body = `${sql} FORMAT JSON`; const res = await fetch(CLICKHOUSE_URL, { method: "POST", headers: { "Content-Type": "text/plain" }, body, signal, }); if (!res.ok) { const text = await res.text(); throw new Error( `ClickHouse query failed: ${res.status} ${text.slice(0, 500)}`, ); } const json = (await res.json()) as ClickHouseJsonResponse; return json.data; } // ─── Cube result assembly ──────────────────────────────────────────────────── /** Column format → `AgDataType`, used to populate `AgPublicColumnMetadata.dataType`. */ const FORMAT_TO_DATA_TYPE: Record = { textFormat: "string", integerFormat: "number", currencyFormat: "number", dateFormat: "date", }; function dataTypeForFieldId(fieldId: string): AgDataType { const format = FIELDS.find( (f) => f.id === extractColumnName(fieldId), )?.format; return (format && FORMAT_TO_DATA_TYPE[format]) ?? "string"; } /** Canonical tuple identity, matching the engine-wide `\x1F`-joined, `'∅'`-for-null scheme. */ function tupleId(values: readonly (Primitive | null)[]): string { return values.map((v) => (v == null ? "∅" : String(v))).join("\x1F"); } /** * Assigns a stable `tupleIndex` to each distinct dimension-value combination * in first-seen order, and returns the ordered `AgResultTuple[]` alongside a * lookup from tuple identity to index. */ function buildAxisTuples( levels: AgDimensionDefinition[], rows: Record[], ): { tuples: AgResultTuple[]; indexByRowIndex: number[] } { const tuples: AgResultTuple[] = []; const indexByKey = new Map(); const indexByRowIndex: number[] = []; for (const row of rows) { const values = levels.map((dim) => row[dim.field.key] as Primitive | null); const key = tupleId(values); let index = indexByKey.get(key); if (index === undefined) { index = tuples.length; indexByKey.set(key, index); tuples.push({ values, level: levels.length, isSubtotal: false, isGrandTotal: false, groupingBitmap: 0, id: key, }); } indexByRowIndex.push(index); } return { tuples, indexByRowIndex }; } function buildHierarchyAxisTuples( levels: AgDimensionDefinition[], rows: Record[], measures: AgCubeMeasureDefinition[], ): { tuples: AgResultTuple[]; cellByIndex: Map>; } { const tuples: AgResultTuple[] = []; const indexByKey = new Map(); const cellByIndex = new Map>(); for (const row of rows) { let parentIndex: number | undefined; for (let level = 1; level <= levels.length; ++level) { const values = levels.map((dim, i) => i < level ? (row[dim.field.key] as Primitive | null) : null, ); const key = tupleId(values.slice(0, level)); let index = indexByKey.get(key); if (index === undefined) { index = tuples.length; indexByKey.set(key, index); tuples.push({ values, level, parentIndex, isSubtotal: level < levels.length, isGrandTotal: false, groupingBitmap: 0, id: key, }); cellByIndex.set(index, {}); } const cell = cellByIndex.get(index)!; for (const m of measures) { const v = row[m.field.key]; const existing = cell[m.field.key]; cell[m.field.key] = typeof v === "number" && typeof existing === "number" ? existing + v : ((existing ?? (v as Primitive | null) ?? null) as Primitive | null); } parentIndex = index; } } return { tuples, cellByIndex }; } function toResultAxis( levels: AgDimensionDefinition[], tuples: AgResultTuple[], ): AgResultAxis { const dimensions: AgPublicColumnMetadata[] = levels.map((dim) => ({ fieldKey: dim.field.key, dataType: dataTypeForFieldId(dim.field.fieldId), })); return { dimensions, tuples }; } /** * Flattens ClickHouse's string-typed JSON cells into numbers for measure * columns, mirroring `ClickHouseDataEngine.coerceRows` in the row-shaped example. */ function coerceMeasureValues( rows: Record[], measures: AgCubeMeasureDefinition[], ): void { for (const row of rows) { for (const m of measures) { const val = row[m.field.key]; if (typeof val === "string") { const n = Number(val); if (!Number.isNaN(n)) row[m.field.key] = n; } } } } // ─── ClickHousePivotDataEngine ─────────────────────────────────────────────── class ClickHousePivotDataEngine implements AgDataEngine { private readonly dateFieldKeys = new Set( FIELDS.filter((f) => f.format === "dateFormat").map( (f) => `${SOURCE_ID}.${f.id}`, ), ); private readonly numericFieldKeys = new Set( FIELDS.filter( (f) => f.format === "currencyFormat" || f.format === "integerFormat", ).map((f) => `${SOURCE_ID}.${f.id}`), ); getDataSources(): AgDataSourcesDefinition { return { sources: [{ id: SOURCE_ID, data: [], fields: FIELDS }], }; } async execute( ...requests: AgExecuteRequest[] ): Promise { return Promise.all(requests.map((r) => this.executeOne(r))); } async executeCube( ...requests: AgCubeResolvedExecuteRequest[] ): Promise { return Promise.all(requests.map((r) => this.executeCubeOne(r))); } private async executeCubeOne( request: AgCubeResolvedExecuteRequest, ): Promise { const { query, options } = request; const axes = (query.axes ?? []) as AgCubeResolvedAxisDefinition[]; const measures = query.measures ?? []; const axisLevels = axes.map((axis) => { if (axis.dimensions.kind !== "fixed") { throw new Error( "ClickHousePivotDataEngine.executeCube only supports fixed-depth axes", ); } return axis.dimensions.levels; }); const sql = cubeQueryToSql(axisLevels, measures, query.filter); const flatRows = await runClickHouse(sql, options?.signal); coerceMeasureValues(flatRows, measures); const resultMeasures = measures.map((m) => ({ key: m.field.key, dataType: "number" as const, aggregation: m.field.aggregation as AgAggregationFunction | undefined, })); // Treemap/sunburst send a single hierarchy axis and need every level nested, not just leaves. if (axisLevels.length === 1) { const levels = axisLevels[0]; const { tuples, cellByIndex } = buildHierarchyAxisTuples( levels, flatRows, measures, ); const cells = Array.from(cellByIndex, ([idx, cell]) => ({ indices: [idx], cell, })); return { dataShape: "cube", axes: [toResultAxis(levels, tuples)], measures: resultMeasures, cells: { entries: cells }, }; } const axisResults = axisLevels.map((levels) => buildAxisTuples(levels, flatRows), ); const cells = flatRows.map((row, i) => { const cell: Record = {}; for (const m of measures) { cell[m.field.key] = row[m.field.key] as Primitive | null; } return { indices: axisResults.map((r) => r.indexByRowIndex[i]), cell }; }); return { dataShape: "cube", axes: axisLevels.map((levels, i) => toResultAxis(levels, axisResults[i].tuples), ), measures: resultMeasures, cells: { entries: cells }, }; } private async executeOne( request: AgExecuteRequest, ): Promise { const { query, options } = request; const shape = options?.shape ?? "rows"; const sql = agStudioQueryToSql(query); const rawRows = await runClickHouse(sql, options?.signal); const rows = this.coerceRows(rawRows, query); if (shape === "columns") { const columns = new Map(); for (const row of rows) { for (const [k, v] of Object.entries(row)) { let col = columns.get(k); if (!col) { col = []; columns.set(k, col); } col.push(v); } } return { dataShape: "columns", columns, metadata: { rowCount: rows.length }, }; } return { dataShape: "rows", rows, metadata: { rowCount: rows.length } }; } private coerceRows( rows: Record[], query: AgStudioQuery, ): Record[] { if (rows.length === 0) return rows; const keyToFieldId = new Map(); for (const dim of query.axes?.[0]?.dimensions ?? []) { keyToFieldId.set(dim.field.key, dim.field.fieldId); } for (const m of query.measures ?? []) { keyToFieldId.set(m.field.key, m.field.fieldId); } for (const dim of query.projection ?? []) { keyToFieldId.set(dim.field.key, dim.field.fieldId); } for (const row of rows) { for (const [key, fieldId] of keyToFieldId) { const val = row[key]; if (val == null) continue; if (this.dateFieldKeys.has(fieldId) && !(val instanceof Date)) { row[key] = new Date(val as string); } else if ( this.numericFieldKeys.has(fieldId) && typeof val === "string" ) { const n = Number(val); if (!Number.isNaN(n)) row[key] = n; } } } return rows; } } // ─── Studio component ─────────────────────────────────────────────────────── const VueExample = defineComponent({ template: `
`, components: { "ag-studio": AgStudio, }, setup() { const mode = ref("view"); const data = shallowRef(); const initialState = ref({ panels: { filters: { collapsed: true, }, }, pages: [ { id: "properties-pivot", widgets: { "price-pivot": { type: "pivot-grid", dataMapping: { rows: [{ id: "uk_price_paid.county" }], columns: [{ id: "uk_price_paid.type" }], values: [ { id: "uk_price_paid.price", aggregation: "avg" }, { id: "uk_price_paid.price", aggregation: "count" }, ], }, format: { title: { enabled: true, text: "Average Price and Sales Count by County and Property Type", typography: { fontSize: 16, fontWeight: "bold" }, }, style: { totalColumns: true }, }, }, "price-treemap": { type: "treemap-chart", dataMapping: { categoryKey: [ { id: "uk_price_paid.type" }, { id: "uk_price_paid.duration" }, ], valueKey: [{ id: "uk_price_paid.price", aggregation: "sum" }], }, format: { title: { enabled: true, text: "Total Price by Property Type and Tenure", typography: { fontSize: 16, fontWeight: "bold" }, }, }, }, "price-sunburst": { type: "sunburst-chart", dataMapping: { categoryKey: [ { id: "uk_price_paid.type" }, { id: "uk_price_paid.duration" }, ], valueKey: [{ id: "uk_price_paid.price", aggregation: "sum" }], }, format: { title: { enabled: true, text: "Total Price by Property Type and Tenure", typography: { fontSize: 16, fontWeight: "bold" }, }, }, }, }, widgetLayout: { "price-pivot": { xTrack: 0, yTrack: 0, xSpan: 24, ySpan: 18 }, "price-treemap": { xTrack: 0, yTrack: 18, xSpan: 12, ySpan: 18 }, "price-sunburst": { xTrack: 12, yTrack: 18, xSpan: 12, ySpan: 18 }, }, filter: { page: [] }, }, ], selectedPageId: "properties-pivot", }); const toggleMode = () => { mode.value = mode.value === "edit" ? "view" : "edit"; }; // Lazily create the engine on demand - Studio then owns its lifecycle // (init → execute → dispose) once the `data` property is set. const loadData = () => { data.value = new ClickHousePivotDataEngine(); }; return { mode, data, initialState, toggleMode, loadData, }; }, }); const app = createApp(VueExample); app.mount("#app"); ``` [Live example: ClickHouse Server-Side Pivot](https://www.ag-grid.com/studio/examples/server-side-data-implementation/clickhouse-serverside-pivot/vue3/) Contains HM Land Registry data © Crown copyright and database right 2021. This data is licensed under the Open Government Licence v3.0. ([Source](https://www.gov.uk/government/statistical-data-sets/price-paid-data-downloads), [Fields](https://www.gov.uk/guidance/about-the-price-paid-data)) ### REST API: World Bank Countries Queries the World Bank Open Data API. Supported predicates are sent as URL parameters; remaining filters, aggregation, and sorting are applied locally. #### World Bank Countries Server-Side ```ts import type { AgDataEngine, AgDataSourcesDefinition, AgExecuteRequest, AgExecuteResult, AgFieldDefinition, AgReportState, AgResultShape, AgStudioFilterDefinition, AgStudioMode, AgStudioQuery, AgStudioQueryField, } from "ag-studio"; import { AgStudio } from "ag-studio-vue3"; import { createApp, defineComponent, ref, shallowRef } from "vue"; /** * World Bank Open Data - Countries API. Public, no authentication, * CORS-enabled, and has been up reliably since the mid-2000s. Supports URL * filter pushdown via query parameters: `?region=EUU&incomeLevel=HIC` etc. * Response shape is a two-element tuple: `[metadata, countries[]]`. * * Docs: https://datahelpdesk.worldbank.org/knowledgebase/articles/898581 */ const WORLD_BANK_URL = "https://api.worldbank.org/v2"; const SOURCE_ID = "countries"; /** * The World Bank `/country` endpoint returns country metadata only - region, * income classification, lending type, capital, geolocation. Indicators * (population, GDP, area, etc.) live on a separate `/country/{id}/indicator` * endpoint and would need a second fetch; we keep this example to a single * REST call so the pushdown-vs-local split stays front and centre. */ const FIELDS: AgFieldDefinition[] = [ { id: "cca2", format: "textFormat", name: "Code" }, { id: "country", format: "textFormat", name: "Country" }, { id: "region", format: "textFormat", name: "Region" }, { id: "incomeLevel", format: "textFormat", name: "Income Level" }, { id: "lendingType", format: "textFormat", name: "Lending Type" }, { id: "capitalCity", format: "textFormat", name: "Capital" }, { id: "latitude", format: "decimalFormat", name: "Latitude" }, { id: "longitude", format: "decimalFormat", name: "Longitude" }, ]; interface CountryRow { cca2: string; country: string; region: string; incomeLevel: string; lendingType: string; capitalCity: string; latitude: number | null; longitude: number | null; } /** * Raw record shape from the World Bank API. Nested fields (`region`, * `incomeLevel`, `lendingType`) arrive as `{ id, iso2code, value }`; we * project the `id` for server-side pushdown mapping and the `value` for * the user-facing display column. */ interface RawCountry { id: string; iso2Code: string; name: string; region?: RawLookup; incomeLevel?: RawLookup; lendingType?: RawLookup; capitalCity?: string; longitude?: string; latitude?: string; } interface RawLookup { id: string; iso2code: string; value: string; } /** * Regional aggregates (e.g. "Africa Eastern and Southern") are flagged by * `region.id === 'NA'`. We drop them - the dashboard wants actual countries. */ function isAggregateRow(raw: RawCountry): boolean { return raw.region?.id === "NA"; } function flattenCountry(raw: RawCountry): CountryRow { const lat = parseCoord(raw.latitude); const lng = parseCoord(raw.longitude); return { cca2: raw.iso2Code ?? "", country: raw.name ?? "", region: (raw.region?.value ?? "Unknown").trim(), incomeLevel: (raw.incomeLevel?.value ?? "Unknown").trim(), lendingType: (raw.lendingType?.value ?? "Unknown").trim(), capitalCity: raw.capitalCity ?? "", latitude: lat, longitude: lng, }; } function parseCoord(s: string | undefined): number | null { if (!s) return null; const n = Number(s); return Number.isFinite(n) ? n : null; } // ─── World Bank countries engine ─────────────────────────────────────────── // // A query-style Data Engine backed by the World Bank Open Data API. The API // accepts equality predicates on `region`, `incomeLevel`, and `lendingType` // as URL query parameters - which means the engine can translate a subset of // `AgStudioQuery.filter` into a narrower fetch. Remaining predicates plus // all grouping/aggregation/sort/limit run locally on the returned rows. // Canonical REST integration pattern: push down what the URL can express, // compute the rest in JavaScript. type AggFn = | "sum" | "avg" | "count" | "countd" | "min" | "max" | "first" | "last"; // The World Bank API keys filters by internal IDs (e.g. `HIC` for // "High income"), but the user-facing display column and filter dropdowns // carry the human-readable `value`. We record the mapping on every fetch so // `chooseEndpoint` can translate display-value equalities into ID-keyed // query params. Small, bounded (~7 regions, ~4 income bands, ~5 lending // types) so the memory cost is negligible. type PushdownDimension = "region" | "incomeLevel" | "lendingType"; const PUSHDOWN_DIMENSIONS: readonly PushdownDimension[] = [ "region", "incomeLevel", "lendingType", ]; const displayToId: Record> = { region: new Map(), incomeLevel: new Map(), lendingType: new Map(), }; function recordDisplayMapping( dim: PushdownDimension, display: string, id: string, ): void { if (!display || !id || id === "NA") return; if (!displayToId[dim].has(display)) { displayToId[dim].set(display, id); } } // ─── Filter → REST URL ───────────────────────────────────────────────────── /** * Translate an `AgStudioQuery.filter` into a World Bank `/country` URL. * Every top-level AND equality on a pushdown dimension becomes a query * parameter; OR / NOT / non-equality predicates stay in the query and are * applied locally after the fetch. `per_page=500` overfetches the ~296 * rows available (including aggregate "regions") in one round-trip. */ function chooseEndpoint(filter?: AgStudioFilterDefinition): string { const params = new URLSearchParams({ format: "json", per_page: "500" }); const matches = collectEqualityFilters(filter); for (const dim of PUSHDOWN_DIMENSIONS) { const hit = matches.find((m) => m.field === dim); if (!hit || typeof hit.value !== "string") continue; const id = displayToId[dim].get(hit.value); if (id !== undefined) { params.set(dim, id); } } return `${WORLD_BANK_URL}/country?${params.toString()}`; } interface EqualityMatch { field: string; value: unknown; } function collectEqualityFilters( filter?: AgStudioFilterDefinition, ): EqualityMatch[] { if (!filter) return []; if ("combinator" in filter) { if (filter.combinator === "and") { return filter.conditions.flatMap((c) => collectEqualityFilters(c)); } return []; } if (filter.operator === "equals" && !Array.isArray(filter.value)) { return [ { field: extractColumnName(filter.field.fieldId), value: filter.value }, ]; } return []; } // ─── Local filter application ────────────────────────────────────────────── /** * Apply the full `AgStudioQuery.filter` to a row in memory. The REST endpoint * may have already narrowed by region/subregion; this pass handles everything * the URL couldn't express. */ function rowMatchesFilter( row: CountryRow, filter?: AgStudioFilterDefinition, ): boolean { if (!filter) return true; if ("combinator" in filter) { const { combinator, conditions } = filter; if (combinator === "and") return conditions.every((c) => rowMatchesFilter(row, c)); if (combinator === "or") return conditions.some((c) => rowMatchesFilter(row, c)); return !conditions.every((c) => rowMatchesFilter(row, c)); } const col = extractColumnName(filter.field.fieldId); const value = (row as unknown as Record)[col]; const { operator } = filter; switch (operator) { case "equals": return Array.isArray(filter.value) ? filter.value.includes(value as any) : value === filter.value; case "notEqual": return Array.isArray(filter.value) ? !filter.value.includes(value as any) : value !== filter.value; case "greaterThan": return value != null && (value as number) > (filter.value as number); case "greaterThanOrEqual": return value != null && (value as number) >= (filter.value as number); case "lessThan": return value != null && (value as number) < (filter.value as number); case "lessThanOrEqual": return value != null && (value as number) <= (filter.value as number); case "isNull": return value == null; case "isNotNull": return value != null; case "between": { const [from, to] = filter.value as [number, number]; return ( value != null && (value as number) >= from && (value as number) <= to ); } case "isIn": return Array.isArray(filter.value) && filter.value.includes(value as any); default: return true; } } // ─── Aggregation ─────────────────────────────────────────────────────────── function aggregate(values: unknown[], fn: AggFn | undefined): unknown { if (!fn) return values[0]; switch (fn) { case "sum": return sumNumbers(values); case "avg": { const nums = numericValues(values); return nums.length === 0 ? null : nums.reduce((a, b) => a + b, 0) / nums.length; } case "count": return values.filter((v) => v != null).length; case "countd": return new Set(values.filter((v) => v != null)).size; case "min": return minValue(values); case "max": return maxValue(values); case "first": return values[0]; case "last": return values[values.length - 1]; default: return values[0]; } } function numericValues(values: unknown[]): number[] { const out: number[] = []; for (const v of values) { if (typeof v === "number" && !Number.isNaN(v)) out.push(v); } return out; } function sumNumbers(values: unknown[]): number { let total = 0; for (const v of values) { if (typeof v === "number" && !Number.isNaN(v)) total += v; } return total; } function minValue(values: unknown[]): unknown { let min: unknown; for (const v of values) { if (v == null) continue; if (min == null || (v as any) < (min as any)) min = v; } return min; } function maxValue(values: unknown[]): unknown { let max: unknown; for (const v of values) { if (v == null) continue; if (max == null || (v as any) > (max as any)) max = v; } return max; } // ─── Utilities ───────────────────────────────────────────────────────────── function extractColumnName(fieldId: string): string { const i = fieldId.indexOf("."); return i >= 0 ? fieldId.slice(i + 1) : fieldId; } function getValue( row: Record, ref: AgStudioQueryField, ): unknown { return row[extractColumnName(ref.fieldId)]; } // ─── Query execution ─────────────────────────────────────────────────────── function groupAndAggregate( rows: CountryRow[], query: AgStudioQuery, ): Record[] { const { axes, measures, projection, sort, limit } = query; const dimensions = axes?.[0]?.dimensions ?? []; // Projection mode: no aggregation, just pick fields per row. if (projection?.length && !dimensions.length) { let out: Record[] = rows.map((row) => { const o: Record = {}; for (const dim of projection) { o[dim.field.key] = getValue( row as unknown as Record, dim.field, ); } return o; }); out = applySort(out, query, sort); if (limit) out = out.slice(limit.offset ?? 0, (limit.offset ?? 0) + limit.count); return out; } // Aggregation mode: group by dimensions, aggregate measures. const groups = new Map(); for (const row of rows) { const key = dimensions .map((dim) => String(getValue(row as unknown as Record, dim.field)), ) .join("\0"); const existing = groups.get(key); if (existing) existing.push(row); else groups.set(key, [row]); } let aggregated: Record[] = []; for (const bucket of groups.values()) { const first = bucket[0]!; const agg: Record = {}; for (const dim of dimensions) { agg[dim.field.key] = getValue( first as unknown as Record, dim.field, ); } for (const m of measures ?? []) { const values = bucket.map((r) => getValue(r as unknown as Record, m.field), ); agg[m.field.key] = aggregate( values, m.field.aggregation as AggFn | undefined, ); } aggregated.push(agg); } aggregated = applySort(aggregated, query, sort); if (limit) aggregated = aggregated.slice( limit.offset ?? 0, (limit.offset ?? 0) + limit.count, ); return aggregated; } function applySort( rows: Record[], _query: AgStudioQuery, sort: AgStudioQuery["sort"], ): Record[] { if (!sort?.length) return rows; const out = [...rows]; out.sort((a, b) => { for (const s of sort) { const key = s.field.key; const av = a[key]; const bv = b[key]; if (av === bv) continue; const dir = s.direction === "asc" ? 1 : -1; if (av == null) return dir; if (bv == null) return -dir; return (av as any) < (bv as any) ? -dir : dir; } return 0; }); return out; } function applyRowId( query: AgStudioQuery, rows: Record[], ): void { const rowNumberField = query.window?.find( (w) => w.functionType === "rowNumber", ); if (rowNumberField?.as) { const key = rowNumberField.as; const offset = query.limit?.offset ?? 0; rows.forEach((row, i) => { row[key] = offset + i; }); return; } const dimensions = query.axes?.[0]?.dimensions ?? []; const rowIdField = query.computedFields?.[0]; if (!dimensions.length || !rowIdField) return; const dimensionKeys = dimensions.map((d) => d.field.key); for (const row of rows) { row[rowIdField.field.key] = dimensionKeys .map((key) => row[key] instanceof Date ? (row[key] as Date).toISOString() : String(row[key]), ) .join(""); } } // ─── RestCountriesDataEngine ─────────────────────────────────────────────── class RestCountriesDataEngine implements AgDataEngine { /** Cache each distinct pushdown URL so repeated queries don't re-fetch. */ private cache = new Map(); getDataSources(): AgDataSourcesDefinition { return { sources: [{ id: SOURCE_ID, data: [], fields: FIELDS }] }; } async execute( ...requests: AgExecuteRequest[] ): Promise { // The pushdown URL depends on display→id mappings we only learn from // a real response. Prime the cache with an unfiltered fetch on the // first call so `chooseEndpoint` has a populated translation table // before any filter-bearing query tries to narrow the URL. await this.ensurePrimed(requests[0]?.options?.signal); return Promise.all(requests.map((r) => this.executeOne(r))); } private async ensurePrimed(signal?: AbortSignal): Promise { const baseUrl = `${WORLD_BANK_URL}/country?format=json&per_page=500`; if (this.cache.has(baseUrl)) return; await this.fetchCountries(baseUrl, signal); } private async executeOne( request: AgExecuteRequest, ): Promise { const { query, options, info } = request; const shape = options?.shape ?? "rows"; const url = chooseEndpoint(query.filter); console.log("[REST Countries engine] execute:", { batchId: info?.batchId, url, }); const fetched = await this.fetchCountries(url, options?.signal); const filtered = fetched.filter((r) => rowMatchesFilter(r, query.filter)); const rows = groupAndAggregate(filtered, query); applyRowId(query, rows); if (shape === "columns") { const columns = new Map(); for (const row of rows) { for (const [k, v] of Object.entries(row)) { let col = columns.get(k); if (!col) { col = []; columns.set(k, col); } col.push(v); } } return { dataShape: "columns", columns, metadata: { rowCount: rows.length }, }; } return { dataShape: "rows", rows, metadata: { rowCount: rows.length } }; } private async fetchCountries( url: string, signal?: AbortSignal, ): Promise { const cached = this.cache.get(url); if (cached) return cached; const res = await fetch(url, { signal }); if (!res.ok) throw new Error( `World Bank request failed: ${res.status} ${res.statusText}`, ); // Response is a two-element tuple: [metadata, countries[]]. The // metadata slot carries paging info we don't need here. const payload = (await res.json()) as [unknown, RawCountry[]]; const list = Array.isArray(payload) && Array.isArray(payload[1]) ? payload[1] : []; const rows: CountryRow[] = []; for (const raw of list) { if (isAggregateRow(raw)) continue; if (raw.region) recordDisplayMapping("region", raw.region.value.trim(), raw.region.id); if (raw.incomeLevel) recordDisplayMapping( "incomeLevel", raw.incomeLevel.value.trim(), raw.incomeLevel.id, ); if (raw.lendingType) recordDisplayMapping( "lendingType", raw.lendingType.value.trim(), raw.lendingType.id, ); rows.push(flattenCountry(raw)); } this.cache.set(url, rows); return rows; } reload(): void { this.cache.clear(); } } // ─── Studio component ─────────────────────────────────────────────────────── const VueExample = defineComponent({ template: `
`, components: { "ag-studio": AgStudio, }, setup() { const mode = ref("view"); const data = shallowRef(); const initialState = ref({ panels: { filters: { collapsed: true, }, }, pages: [ { id: "countries-dashboard", widgets: { "region-filter": { type: "list-filter", dataMapping: { value: [{ id: "countries.region" }] }, format: { title: { enabled: true, text: "Filter by Region" } }, }, "income-filter": { type: "list-filter", dataMapping: { value: [{ id: "countries.incomeLevel" }] }, format: { title: { enabled: true, text: "Filter by Income Level" }, }, }, "count-by-region": { type: "bar-chart-grouped", dataMapping: { categoryKey: [{ id: "countries.region" }], valueKey: [{ id: "countries.country", aggregation: "count" }], }, format: { title: { enabled: true, text: "Countries by Region", typography: { fontSize: 16, fontWeight: "bold" }, }, }, }, "count-by-income": { type: "bar-chart-grouped", dataMapping: { categoryKey: [{ id: "countries.incomeLevel" }], valueKey: [{ id: "countries.country", aggregation: "count" }], }, format: { title: { enabled: true, text: "Countries by Income Level", typography: { fontSize: 16, fontWeight: "bold" }, }, }, }, "countries-grid": { type: "grid", dataMapping: { cols: [ { id: "countries.region" }, { id: "countries.incomeLevel" }, { id: "countries.lendingType" }, { id: "countries.country", aggregation: "count" }, ], }, format: { title: { enabled: true, text: "Countries Summary", typography: { fontSize: 16, fontWeight: "bold" }, }, style: { grandTotalRow: { enabled: true }, theme: { rowHeight: 28 }, }, }, }, }, widgetLayout: { "region-filter": { xTrack: 0, yTrack: 0, xSpan: 6, ySpan: 12 }, "income-filter": { xTrack: 0, yTrack: 12, xSpan: 6, ySpan: 12 }, "count-by-region": { xTrack: 6, yTrack: 0, xSpan: 9, ySpan: 24 }, "count-by-income": { xTrack: 15, yTrack: 0, xSpan: 9, ySpan: 24 }, "countries-grid": { xTrack: 0, yTrack: 24, xSpan: 24, ySpan: 14 }, }, filter: { page: [] }, }, ], selectedPageId: "countries-dashboard", }); const toggleMode = () => { mode.value = mode.value === "edit" ? "view" : "edit"; }; // Lazily create the engine on demand - Studio then owns its lifecycle // (init → execute → dispose) once the `data` property is set. const loadData = () => { data.value = new RestCountriesDataEngine(); }; return { mode, data, initialState, toggleMode, loadData, }; }, }); const app = createApp(VueExample); app.mount("#app"); ``` [Live example: World Bank Countries Server-Side](https://www.ag-grid.com/studio/examples/server-side-data-implementation/restcountries-serverside/vue3/) Contains data from The World Bank: Countries API, licensed under [Creative Commons Attribution 4.0 (CC BY 4.0)](https://creativecommons.org/licenses/by/4.0/). ([Terms of Use](https://www.worldbank.org/en/about/legal/terms-of-use-for-datasets))