Basic Usage of SQL Expressions in Grafana

sql_expr_basic_usage_chatgpt.md

📘 Basic Usage of SQL Expressions in Grafana

SQL expressions in Grafana let you perform lightweight SQL-like transformations on the results of other queries — directly within the panel. This is great for reshaping, renaming, filtering, and aggregating data without modifying the original datasource query.

---

🔧 How SQL Expressions Work

SQL expressions operate on the output of another query in the same panel, referenced by its query letter (e.g., A, B). These expressions are parsed and executed by Grafana — not sent to an external database.

---

📥 Input Requirements

To work correctly, SQL expressions must use data from a backend datasource (e.g., Prometheus, JSON API, GitHub, etc.).

✅ Accepted Inputs

Input Type	SQL Mapping Behavior
Single table-like frame (no labels)	Used directly — each field becomes a column in the SQL table.
Full Long format (e.g., numeric_full_long)	Maps directly as a flat table — no conversion needed.
Metric frame with FrameMeta.Type (e.g., TimeSeriesWide, TimeSeriesMulti, NumericWide, NumericMulti)	Automatically converted to full_long format, then treated as a table.

❌ Rejected Inputs

• Queries that lack FrameMeta.Type and are not a single flat table with no labels.
• Queries from non-backend datasources (e.g., local variables or dashboard-only sources).
• Responses with ambiguous frame structures that cannot be interpreted as tables or convertible metrics.

---

📦 What’s in a full_long Format?

When Grafana auto-converts a metric frame into a full_long table, the result typically includes:

Field	Description
time	Timestamp column (if present)
__value__	The numeric value
__metric__	Metric name (e.g., cpu, up, etc.)
__display_name__	Resolved display name for visualization (e.g., legend)
<label columns>	All label keys (e.g., job, instance, pod, etc.)

These become standard SQL columns for use in expressions.

---

💡 SQL Expression Examples (With Inputs)

🟢 1. Basic projection of a time series

• Datasource A: Prometheus
• Input Query:

  sum(up{namespace=~".*(tempo|mimir).*-0."}) by (namespace)
  

• SQL Expression:

  SELECT time, __value__ AS up, __display_name__ AS namespace
  FROM A

---

🟢 2. Aggregate across time

• Datasource A: Prometheus
• Input Query:

  sum(up{namespace=~".*(tempo|mimir).*-0."}) by (namespace)
  

• SQL Expression:

  SELECT time, SUM(__value__) AS up
  FROM A
  GROUP BY time

---

🟢 3. Sort instant values (stat panel)

• Datasource A: Prometheus (instant query enabled)
• Input Query:

  sum(up{namespace=~".*(tempo|mimir).*-0."}) by (namespace)
  

• SQL Expression:

  SELECT __value__ AS up, __display_name__ AS namespace
  FROM A
  ORDER BY __value__ DESC

---

🟢 4. Merge GitHub issue results

• Datasource A: GitHub Datasource (grafana-github-datasource)
• Input Queries:
  • grafana → is:open
  • loki → is:open
• SQL Expression:

  SELECT title, author, repo FROM grafana
  UNION ALL
  SELECT title, author, repo FROM loki
  ORDER BY repo DESC

---

🟢 5. Work with mock/test table

• Datasource A: Grafana Mock Datasource (grafana-mock-datasource)
• Input Table (my_table):
  • username, height, role, isPremium
• SQL Expression:

  SELECT * FROM my_table

---

🛠 Tips and Best Practices

• Use __value__ and __display_name__ as generic hooks into metric values and labels.
• SQL expressions are ideal for:
  • Renaming columns for clarity
  • Reducing multiple series
  • Sorting top-N values
  • Merging data sources (via UNION)
• Prefer writing metric queries with valid FrameMeta.Type (e.g., TimeSeriesWide) to ensure compatibility with SQL expressions.