Example Queries

Here are practical examples of JSONQL queries for common analytical use cases, from simple charts to complex multi-table analytics.

Bar Chart: Sales by Month

A basic time-series aggregate query that shows total sales grouped by month:

{
  "type": "aggregate",
  "measures": [{ "column": "Orders.totalSales", "type": "sum" }],
  "dimensions": [
    {
      "column": "Orders.createdAt",
      "type": "time",
      "granularity": "month",
      "date_range": ["2024-01-01", "2024-12-31"]
    }
  ]
}

This query:

• Sums up all sales amounts by month
• Filters to 2024 data only
• Returns data suitable for a time-series bar chart

Segmented Chart: Sales by Month by Product Category

A multi-dimensional aggregate query that breaks down sales by both time and category:

{
  "type": "aggregate",
  "measures": [{ "column": "Orders.totalSales", "type": "sum" }],
  "dimensions": [
    {
      "column": "Products.category",
      "type": "categorical"
    },
    {
      "column": "Orders.createdAt",
      "type": "time",
      "granularity": "month"
    }
  ],
  "joins": [
    {
      "from": "Orders",
      "to": "Products",
      "from_column": "Orders.product_id",
      "to_column": "Products.id"
    }
  ]
}

This query:

• Requires a join between Orders and Products tables
• Groups by both product category and month
• Returns data suitable for a segmented/stacked chart

Raw Table: Recent Orders

A table query that retrieves individual order records for display in a data table:

{
  "type": "table",
  "columns": [
    { "name": "Orders.id" },
    { "name": "Orders.customerName" },
    { "name": "Orders.amount" },
    { "name": "Orders.status" }
  ],
  "filters": [
    {
      "column": "Orders.createdAt",
      "operator": "gte",
      "values": ["2024-08-01"]
    }
  ],
  "order_by": [{ "column": "Orders.createdAt", "direction": "desc" }],
  "limit": 100
}

This query:

• Returns raw order records (no aggregation)
• Filters to orders from August 2024 onwards
• Orders by creation date (newest first)
• Limits results to 100 records

Complex Analytics: Revenue by Region and Product, Filtered

A sophisticated multi-table aggregate query with multiple measures, dimensions, joins, and filters:

{
  "type": "aggregate",
  "measures": [
    { "column": "Orders.totalRevenue", "type": "sum" },
    { "column": "Orders.id", "type": "count" }
  ],
  "dimensions": [
    {
      "column": "Users.region",
      "type": "categorical"
    },
    {
      "column": "Products.category",
      "type": "categorical"
    },
    {
      "column": "Orders.createdAt",
      "type": "time",
      "granularity": "quarter",
      "date_range": ["2024-01-01", "2024-12-31"]
    }
  ],
  "joins": [
    {
      "from": "Orders",
      "to": "Users",
      "from_column": "Orders.user_id",
      "to_column": "Users.id"
    },
    {
      "from": "Orders",
      "to": "Products",
      "from_column": "Orders.product_id",
      "to_column": "Products.id"
    }
  ],
  "filters": [
    { "column": "Orders.status", "operator": "equals", "values": ["completed"] },
    { "column": "Orders.totalAmount", "operator": "gte", "values": ["50"] }
  ]
}

This query demonstrates:

• Multiple measures: Both revenue sum and order count
• Multiple dimensions: Region, product category, and quarterly time grouping
• Complex joins: Connecting Orders to both Users and Products
• Filtering: Only completed orders with amount ≥ $50
• Time constraints: Limited to 2024 data with quarterly granularity

Each example can be adapted by changing fields, operators, date ranges, and aggregation types to fit your specific data model and analytical needs.