stahura/dataset-query

@domoinc/query - Data Query Builder

CRITICAL: Use the Query API (via @domoinc/query) for all dataset queries in Domo apps. This is essential because:

1. Page Filter Integration - The Query API automatically respects page-level filters when your app is embedded in a Domo dashboard. This is key for apps that need to respond to dashboard filter changes.

2. Performance - The Query API allows you to query only the data you need at the aggregation level required, rather than fetching entire datasets. This is critical for performance, especially with large datasets.

3. Server-Side Processing - Aggregations and filtering happen on Domo's servers, reducing data transfer and client-side processing.

Non-Query data access: If your app bypasses @domoinc/query (e.g., Code Engine calls, raw SQL via SqlClient, or direct /data/v1/ fetches without query parameters), page filters are NOT applied automatically. You must register domo.onFiltersUpdated (see domo-js) and pass filter values as parameters to your data source manually. The same applies to App Studio variables — use domo.onVariablesUpdated to receive variable changes and incorporate them into your queries.

The Query library provides a chainable API for building complex data queries. It constructs URLs that work with domo.get() and the /data/v1/ endpoint.

npm/yarn:

yarn add @domoinc/query

import Query from '@domoinc/query';

CDN (Vanilla JavaScript):

<script src="https://cdn.jsdelivr.net/npm/@domoinc/[email protected]/dist/main.min.js"></script>

// Query is available globally after CDN script loads
const data = await new Query()
  .select(['region', 'sales'])
  .fetch('sales-dataset');

Basic Usage

// Simple query
const data = await new Query()
  .select(['region', 'sales', 'date'])
  .fetch('sales-dataset');

// With filtering
const filtered = await new Query()
  .select(['region', 'product', 'sales'])
  .where('sales').greaterThan(1000)
  .where('region').in(['North', 'South'])
  .fetch('sales-dataset');

// With grouping and aggregation
const summary = await new Query()
  .select(['region', 'sales', 'quantity'])
  .groupBy('region')
  .groupBy({ sales: 'sum', quantity: 'avg' })
  .orderBy('sales', 'descending')
  .limit(10)
  .fetch('sales-dataset');

Select

// Select specific columns
new Query().select(['col1', 'col2', 'col3'])

// Select all (omit select)
new Query().fetch('dataset')

Where Filters

All filter methods return the Query for chaining.

// Comparison filters
.where('amount').lessThan(100)           // .lt(100)
.where('amount').lessThanOrEqual(100)    // .lte(100)
.where('amount').greaterThan(100)        // .gt(100)
.where('amount').greaterThanOrEqual(100) // .gte(100)
.where('amount').equals(100)
.where('amount').notEquals(100)
.where('amount').between(100, 500)

// String filters
.where('name').contains('test')
.where('name').notContains('test')

// List filters
.where('category').in(['A', 'B', 'C'])
.where('status').notIn(['deleted', 'archived'])

// Multiple conditions (AND)
.where('amount').greaterThan(100)
.where('status').equals('active')
.where('region').in(['North', 'South'])

Group By

// Group by single column
.groupBy('region')

// Group by multiple columns
.groupBy('region')
.groupBy('product')

// Group by column with aggregations (second parameter)
.groupBy('region', { 
  sales: 'sum',
  quantity: 'avg',
  price: 'max',
  orders: 'count',
  skus: 'unique'
})

// Multiple groupBy calls
.groupBy('region')
.groupBy('product', { sales: 'sum', orders: 'count' })

Aggregation Functions:

• 'count' - Count rows
• 'sum' - Sum values
• 'avg' - Average values
• 'min' - Minimum value
• 'max' - Maximum value
• 'unique' - Count distinct values

CRITICAL - Aggregation Key Syntax

Aggregation keys MUST be the actual field names from your dataset, NOT custom aliases.

The key in the aggregation object determines the output property name. If you use an alias that doesn't match a field, you'll get [object Object] errors.

// ✅ CORRECT - Keys match actual field names
// If your dataset has fields: 'Sales_Amount', 'Order_Qty'
.groupBy('region', { 
  Sales_Amount: 'sum',   // Key matches dataset field
  Order_Qty: 'count'     // Key matches dataset field
})

// Results: [{ region: 'North', Sales_Amount: 50000, Order_Qty: 150 }]

// ❌ WRONG - Custom aliases as keys cause [object Object] errors
.groupBy('region', { 
  totalSales: 'sum',     // 'totalSales' is not a field name!
  orderCount: 'count'    // 'orderCount' is not a field name!
})

// ✅ If you need custom names, rename AFTER fetching:
const data = await new Query()
  .groupBy('region', { Sales_Amount: 'sum' })
  .fetch('sales');

const renamed = data.map(row => ({
  region: row.region,
  totalSales: row.Sales_Amount  // Rename here
}));

Order By

.orderBy('sales', 'descending')  // or 'desc'
.orderBy('date', 'ascending')    // or 'asc'

// Multiple sort columns
.orderBy('region', 'ascending')
.orderBy('sales', 'descending')

Limit and Offset

.limit(100)    // Max rows
.offset(50)     // Skip rows (for pagination)

// Pagination example
const page = 2;
const pageSize = 25;
new Query()
  .limit(pageSize)
  .offset((page - 1) * pageSize)
  .fetch('dataset');

Date Operations

dateGrain - Group by Date Period

// Group by month
.dateGrain('order_date', 'month')

// Group by month with aggregations
// NOTE: Aggregation keys must be actual field names!
.dateGrain('order_date', 'month', { Revenue: 'sum', Order_Count: 'count' })

// Available grains: 'day', 'week', 'month', 'quarter', 'year'
.dateGrain('date', 'day')
.dateGrain('date', 'week')
.dateGrain('date', 'month')
.dateGrain('date', 'quarter')
.dateGrain('date', 'year')

CRITICAL: The third parameter (aggregations) follows the same rules as groupBy - keys must match actual dataset field names:

// ✅ CORRECT - field names as keys
.dateGrain('order_date', 'month', { Sales_Amount: 'sum' })

// ❌ WRONG - custom aliases cause errors
.dateGrain('order_date', 'month', { totalSales: 'sum' })

periodToDate - YTD, MTD, QTD, etc.

// Year to date
.periodToDate('date', 'year')

// Month to date
.periodToDate('date', 'month')

// Quarter to date
.periodToDate('date', 'quarter')

// Full example
const ytdSales = await new Query()
  .select(['date', 'sales'])
  .periodToDate('date', 'year')
  .groupBy({ sales: 'sum' })
  .fetch('sales');

previousPeriod - Last Period Comparison

// Last year
.previousPeriod('date', 'year')

// Last month
.previousPeriod('date', 'month')

// Last quarter
.previousPeriod('date', 'quarter')

rollingPeriod - Rolling Windows

// Last 30 days
.rollingPeriod('date', 'days', 30)

// Last 12 weeks
.rollingPeriod('date', 'weeks', 12)

// Last 6 months
.rollingPeriod('date', 'months', 6)

// Last 4 quarters
.rollingPeriod('date', 'quarters', 4)

// Last 3 years
.rollingPeriod('date', 'years', 3)

Additional Options

// Use fiscal calendar
.useFiscalCalendar(true)

// Enable beast modes (calculated fields)
.useBeastModes()

// ⚠️ WARNING: .aggregate() does NOT work in practice
// It causes error: "DA0057: An alias list was provided but it could not be parsed"
// Use .groupBy() with a grouping column instead, or .select() + client-side aggregation
// ❌ .aggregate({ total: 'sum', average: 'avg' })  // DOES NOT WORK

Complete Examples

// Sales dashboard query
const salesByRegion = await new Query()
  .select(['region', 'product_category', 'sales', 'quantity', 'date'])
  .where('sales').greaterThan(0)
  .where('date').greaterThanOrEqual('2024-01-01')
  .dateGrain('date', 'month')
  .groupBy('region')
  .groupBy('product_category')
  .groupBy({ sales: 'sum', quantity: 'sum', orders: 'count' })
  .orderBy('sales', 'descending')
  .limit(100)
  .fetch('sales-dataset');

// YoY comparison
const thisYear = await new Query()
  .select(['month', 'revenue'])
  .periodToDate('date', 'year')
  .dateGrain('date', 'month', { revenue: 'sum' })
  .fetch('revenue');

const lastYear = await new Query()
  .select(['month', 'revenue'])
  .previousPeriod('date', 'year')
  .dateGrain('date', 'month', { revenue: 'sum' })
  .fetch('revenue');

// Trend analysis - last 90 days
const trend = await new Query()
  .select(['date', 'sales', 'orders'])
  .rollingPeriod('date', 'days', 90)
  .dateGrain('date', 'day', { sales: 'sum', orders: 'count' })
  .orderBy('date', 'ascending')
  .fetch('sales');