Filters and top-n

Filters are plain values against named fields. A filter on a dimension becomes a WHERE; a filter on a measure becomes a HAVING on the node that groups. Lists are one comma-separated string, strings compare lower-cased, dates accept fixed formats and relative offsets, and top_n keeps the best N members of a dimension by a measure through a ranking CTE. Reference: Filters.

Flat AND list

An array is an AND of leaves. Every entry names a field and a predicate.

"filters": [
  {"field": "category", "predicate": "in_list", "value": "men, children"},
  {"field": "date", "predicate": "greater_than_or_equal_to", "value": "2024-01-01"},
  {"field": "net_paid", "predicate": "greater_than", "value": "100"}
]
zsql sql --expr "category, net paid, category in (men, children), date >= 2024-01-01, net paid > 100"

Every filter in the shorthand is an item in the same comma list as the projections, and the shorthand only produces flat AND lists.

The and/or tree

An object with an and or or key holds nested nodes. This is JSON only.

"filters": {"or": [
  {"field": "category", "predicate": "equals", "value": "books"},
  {"and": [
    {"field": "category", "predicate": "equals", "value": "music"},
    {"field": "date", "predicate": "greater_than_or_equal_to", "value": "2024-01-01"}
  ]}
]}

An and/or object inside a flat array is rejected: Filters here are a flat AND list .... The error goes on to say that membership through any of several facts belongs in a segment’s measures list; see Cohorts and segments.

Lists and the quoting gotcha

in_list and exclude_list take one string. Items are split on commas, trimmed and lower-cased. A comma inside single or double quotes does not split, and the quotes stay part of the item.

{"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""}

renders, on every page of this cookbook, as

LOWER(T1."i_category") IN ('men', 'children', '"books,com"')

So "books,com" matches a category whose stored value includes the quotes. Quote an item only when the warehouse value really contains a comma and the quotes, otherwise list it bare. The shorthand strips quotes from list items before the planner sees them, so category in (Books, "Music, Live") becomes three values; use the JSON spec for list items that contain commas.

Dates: fixed and relative

Fixed formats include 2024-01-31, 2024/01/31, 31-01-2024, Jan 31 2024, 31 Jan 2024 and the same with a time (2024-01-31 09:30:00, 2024-01-31T09:30:00). Relative values are one to three digits and a unit: 28d, 3m, 1y, also h, w, q. They mean “that many units ago”; w, m, q and y snap to the start of the unit, or to its end when used as value_end.

"filters": [
  {"field": "date", "predicate": "greater_than_or_equal_to", "value": "28d"}
]
"filters": [
  {"field": "date", "predicate": "between", "value": "3m", "value_end": "1m"}
]

The second reads: from the first day of the month three months ago to the last day of last month. between needs both value and value_end. An unparseable date is a Planner::ResolutionError cannot parse date ....

String predicates

contains, starts_with, ends_with and their does_not_ forms render as LIKE on a lower-cased column.

{"spec": {"projections": [{"field": "ws_net_paid"}], "filters": [{"field": "category", "predicate": "starts_with", "value": "super"}]}}
zsql sql --expr "web net paid, category starts with super"
SELECT
	sum(T0."ws_net_paid") AS "Web Net Paid"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") LIKE 'super%'

contains gives LIKE '%super%', does_not_contain gives NOT LIKE, keyword with a, b gives (col LIKE '%a%' OR col LIKE '%b%'). equals on a string is LOWER(col) = 'books'.

A measure filter becomes HAVING

There is no separate syntax. {"field": "ws_net_paid", "predicate": "greater_than", "value": "100"} is a measure filter because ws_net_paid is a measure, and the planner places it in the HAVING of the node that groups:

HAVING
	sum(T0."ws_net_paid") > 100

The full statement, with the HAVING inside an aggregation CTE next to the WHERE for the category list, is on Period over period. Measures accept equals, does_not_equal, between and the four comparisons; top_n on a measure is refused with Predicate top n can only be applied to a dimension field.

Top-n

Web revenue for the five best categories by web revenue, among three candidates.

{"spec": {
  "projections": [{"field": "ws_net_paid"}, {"field": "category"}],
  "filters": [
    {"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""},
    {"field": "category", "predicate": "top_n", "value": "5", "top_n_measure": "ws_net_paid"}
  ]
}}
zsql sql --expr "web net paid, category, category in (men, children), category top 5 by web net paid"
WITH ag5c9bb8d550197b3a219ae50f0b427905 AS (
SELECT
	T1."i_category" AS "dim30d09b7",
	sum(T0."ws_net_paid") AS "msr0c0d023"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
WHERE
	LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	T1."i_category"
ORDER BY
	sum(T0."ws_net_paid") desc
LIMIT 5
)
SELECT
	sum(T0."ws_net_paid") AS "Web Net Paid",
	T1."i_category" AS "Category"
FROM
	web_sales T0
	JOIN item T1
		ON T0.ws_item_sk = T1.i_item_sk
	INNER JOIN ag5c9bb8d550197b3a219ae50f0b427905 AS A2
		ON T1."i_category" = A2.dim30d09b7
WHERE
	LOWER(T1."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	T1."i_category"
  • A ranking CTE picks the members. ag5c9b… aggregates the measure by the dimension, applies the same WHERE as the query, orders by the measure descending and takes LIMIT 5. This is the only LIMIT the planner emits; the spec’s limit key is carried, not rendered.
  • The query is INNER JOINed to it on the dimension. The main query then aggregates normally, so the projected measure is computed over the full rows of the winning categories.
  • The measure is optional. Omit top_n_measure to rank by row count. The measure can also be packed into the value as "5:ws_net_paid".

Variations

  • Bottom five. There is no bottom_n; project the measure with "order_by": "asc" and apply your own cut, or filter with less_than against a threshold.
  • Null handling. is_null and is_not_null take no value: {"field": "date", "predicate": "is_not_null"}, shorthand date is not null.
  • Exclude a list. {"field": "category", "predicate": "exclude_list", "value": "books, music"}, shorthand category not in (books, music), renders as NOT IN.

Next steps