apache/superset · error · QueryObjectValidationError

Adhoc metric aggregate is invalid

Error message

Adhoc metric aggregate is invalid

What it means

QueryObjectValidationError raised in SqlaTable.adhoc_metric_to_sqla (models.py:1899) when an adhoc metric with expressionType 'SIMPLE' has an aggregate that is either not a string or not one of the allowed aggregations in self.sqla_aggregations (SUM, COUNT, COUNT_DISTINCT, AVG, MIN, MAX, and engine-specific additions). The aggregate name is used as a dict key to build the SQLAlchemy function, so unknown values are rejected.

Source

Thrown at superset/connectors/sqla/models.py:1899

        Turn an adhoc metric into a sqlalchemy column.

        :param dict metric: Adhoc metric definition
        :param dict columns_by_name: Columns for the current table
        :param template_processor: template_processor instance
        :param bool processed: Whether the sqlExpression has already been processed
        :returns: The metric defined as a sqlalchemy column
        :rtype: sqlalchemy.sql.column
        """
        expression_type = metric.get("expressionType")
        label = utils.get_metric_name(metric, self.verbose_map)

        if expression_type == utils.AdhocMetricExpressionType.SIMPLE:
            aggregate: Any = metric.get("aggregate")
            if (
                not isinstance(aggregate, str)
                or aggregate not in self.sqla_aggregations
            ):
                raise QueryObjectValidationError(_("Adhoc metric aggregate is invalid"))
            metric_column = metric.get("column") or {}
            column_name = cast(str, metric_column.get("column_name"))
            table_column: TableColumn | None = columns_by_name.get(column_name)
            if table_column:
                sqla_column = table_column.get_sqla_col(
                    template_processor=template_processor
                )
            else:
                sqla_column = column(column_name)
            sqla_metric = self.sqla_aggregations[aggregate](sqla_column)
        elif expression_type == utils.AdhocMetricExpressionType.SQL:
            expression: str | None = metric.get("sqlExpression")
            if not isinstance(expression, str) or not expression.strip():
                raise QueryObjectValidationError(
                    _("Adhoc metric SQL expression is invalid")
                )

            if not processed:

View on GitHub (pinned to f4587218dd)

Solutions

1. Use one of the aggregates supported by the datasource: check the datasource's aggregate options in Explore (they come from sqla_aggregations) — commonly SUM, AVG, COUNT, COUNT_DISTINCT, MIN, MAX.
2. If you need a custom aggregation (e.g. MEDIAN), switch the metric to expressionType 'SQL' and write the engine function directly: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x).
3. Ensure the adhoc metric dict includes a string aggregate field; re-pick the aggregate in the Explore UI to regenerate valid JSON.

Example fix

// before
{ expressionType: 'SIMPLE', column: { column_name: 'sales' }, aggregate: 'MEDIAN' }

// after
{ expressionType: 'SQL', sqlExpression: 'PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY sales)' }

ALLOWED_AGGREGATES = {"SUM", "AVG", "COUNT", "COUNT_DISTINCT", "MIN", "MAX"}

def validate_simple_metric(metric: dict) -> None:
    if metric.get("expressionType") == "SIMPLE":
        agg = metric.get("aggregate")
        if not isinstance(agg, str) or agg not in ALLOWED_AGGREGATES:
            raise ValueError(f"unsupported aggregate {agg!r}; use one of {sorted(ALLOWED_AGGREGATES)} or expressionType 'SQL'")

def is_valid_simple_metric(metric: dict) -> bool:
    return (
        metric.get("expressionType") == "SIMPLE"
        and isinstance(metric.get("aggregate"), str)
        and metric["aggregate"] in {"SUM", "AVG", "COUNT", "COUNT_DISTINCT", "MIN", "MAX"}
    )

from superset.exceptions import QueryObjectValidationError

try:
    col = table.adhoc_metric_to_sqla(metric, columns_by_name)
except QueryObjectValidationError as ex:
    if "aggregate is invalid" in str(ex):
        metric = {**metric, "expressionType": "SQL", "sqlExpression": f"{metric.get('aggregate')}({metric['column']['column_name']})"}
        metric.pop("aggregate", None)
        col = table.adhoc_metric_to_sqla(metric, columns_by_name)
    else:
        raise

When it happens

Trigger: Chart payload with an adhoc metric like {expressionType:'SIMPLE', column:{...}, aggregate:'MEDIAN'} on an engine/datasource whose sqla_aggregations lacks MEDIAN; or aggregate missing/None/numeric. Happens with hand-built FormData, stale chart JSON after an aggregation was removed, or engine specs not registering the aggregate.

Common situations: Pasting example payloads that use aggregates unavailable for the database backend; charts authored against one DB type (e.g. Postgres with PERCENTILE via custom) migrated to another; frontend regression dropping the aggregate field.

Related errors

• Adhoc metric SQL expression is invalid
• Adhoc metric expressionType is invalid
• Custom SQL fields cannot be parsed as a single SQL statement
• Custom SQL fields cannot contain set operations.
• Metric '%(metric)s' does not exist

AI-assisted analysis of apache/superset@f4587218dd (2026-08-14). Data as JSON: /api/errors/e1c0a35d83cec559. Report an issue: GitHub.