Filters

Filters are plain values on fields. You name a field, a predicate and a value; the planner writes the WHERE (or HAVING, for a measure) with the right casing, quoting and date handling for the warehouse dialect. There is no SQL in a filter.

Shape

An array is a flat AND of leaves:

"filters": [
  {"field": "category", "predicate": "in_list", "value": "men, children"},
  {"field": "net_paid", "predicate": "greater_than", "value": "100"}
]

An object is a logic tree. A node with a field key is a leaf; otherwise its first key must be and or or holding an array of nodes, nestable to any depth:

"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 group inside an array is rejected:

Filters here are a flat AND list — each entry must name a field; and:/or: groups are not supported in this position. For membership through any of several facts (e.g. bought in store OR catalog), list those measures in the segment's `measures` instead.

Other shape errors: a filter node needs a field or an and/or key, got X and filters must be an array or a logic tree, got .... Filters keep their listed order. Segment filters use the same parser and the same shapes.

Leaf keys

Predicates

Numeric means integer, decimal or bigint. An unknown name fails with 'X' is not a predicate (filter on F). A predicate on a field type that does not allow it fails with class ActiveRecord::RecordInvalid:

Validation failed: Predicate Starts with predicate is not supported for Web Net Paid.

top_n on a measure: Predicate top n can only be applied to a dimension field.

Values

• value and value_end must be scalars; null means absent. Strings are trimmed and an empty string counts as absent. Arrays or objects fail with a filter value must be a scalar.
• Missing where required: Filter value can't be blank (<predicate> on <field>).
• On numeric fields the value must parse as a number: Filter value is not a number. For in_list/exclude_list every item must: Filter value values must be numeric. top_n needs a numeric N.

Strings

String comparisons lower-case both sides, so Books, books and BOOKS match the same rows:

{"spec": {"projections": [{"field": "ws_net_paid"}],
 "filters": [{"field": "category", "predicate": "starts_with", "value": "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%'

equals renders LOWER(T1."i_category") = 'books'.

Lists

A list is one string, split on commas. Each item is trimmed and lower-cased. Commas inside single or double quotes do not split, and a quoted item keeps its quotes in the SQL:

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

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

The gotcha: quoting keeps books,com together, but the quotes become part of the compared value, so this matches a category literally stored as "books,com". Values that contain commas and no quotes in the data cannot be expressed in a list; use equals leaves under an or tree instead.

Numbers

{"field": "net_paid", "predicate": "between", "value": "100", "value_end": "500"}
{"field": "employees", "predicate": "in_list", "value": "10, 20, 30"}

Numbers may be sent as JSON numbers or as strings.

Null checks

No value:

{"field": "date", "predicate": "is_not_null"}

Dates

Fixed dates in any of these formats: %Y-%m-%d %H:%M:%S, %Y-%m-%dT%H:%M:%S, %Y-%m-%d %H:%M, %Y/%m/%d %H:%M:%S, %Y-%m-%d, %Y/%m/%d, %d-%m-%Y, %b %d %Y, %B %d %Y, %d %b %Y.

Relative dates are one to three digits followed by a unit: h, d, w, m, q, y, meaning 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 of a between; h and d do not snap.

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

3m to 1m is from the first day of the month three months ago to the last day of last month. Anything the parser cannot read is a Planner::ResolutionError with cannot parse date ....

top_n

Keep the N dimension values that rank highest. value is N; the ranking measure goes in top_n_measure (uid or name) or is packed into the value as "N:measure". With no measure the ranking is by row count.

{"field": "category", "predicate": "top_n", "value": "5", "top_n_measure": "ws_net_paid"}
{"field": "category", "predicate": "top_n", "value": "5:ws_net_paid"}

Shorthand: category top 5 by web net paid. The planner ranks in a CTE and inner-joins the winners back:

{"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"}
 ]}}

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"

The other filters apply inside the ranking CTE too, so the top five are the top five among the listed categories. This LIMIT 5 is the only LIMIT the planner emits; the spec’s limit key is not written into the statement.

keyword

keyword is a multi-term contains: the value is comma-split and each term becomes a LIKE '%term%', joined with OR.

{"field": "product_name", "predicate": "keyword", "value": "super, ultra"}

Measure filters

There is no separate syntax. A filter whose field is a measure is a measure filter, and the planner places it per node: where a node groups, dimension filters go to WHERE and measure filters to HAVING. On a grouped merge node the measure filter is wrapped in max(...).

{"spec": {"projections": [
  {"field": "date", "order_by": "asc", "decorators": [{"type": "truncate", "grain": "month"}]},
  {"field": "ws_net_paid", "decorators": [{"type": "temporalize", "transform": "month_over_month"}]}
], "filters": [
  {"field": "category", "predicate": "in_list", "value": "men ,children, \"books,com\""},
  {"field": "ws_net_paid", "predicate": "greater_than", "value": "100"}
]}}

WITH ag47a9128558e109f9cf0b93c01992e0e7 AS (
SELECT
	(DATE_TRUNC('month', T1."d_date")::DATE + '1 month'::interval) AS "dim5b35e57",
	sum(T0."ws_net_paid") AS "__msrlm_51898eff8"
FROM
	web_sales T0
	JOIN date_dim T1
		ON T0.ws_sold_date_sk = T1.d_date_sk
	JOIN item T2
		ON T0.ws_item_sk = T2.i_item_sk
WHERE
	LOWER(T2."i_category") IN ('men', 'children', '"books,com"')
GROUP BY
	(DATE_TRUNC('month', T1."d_date")::DATE + '1 month'::interval)
HAVING
	sum(T0."ws_net_paid") > 100
)
SELECT
	A0.dim5b35e57 AS "Month(Date)",
	A0.__msrlm_51898eff8 AS "LM(Web Net Paid)"
FROM
	ag47a9128558e109f9cf0b93c01992e0e7 A0
ORDER BY
	A0.dim5b35e57 asc

The category filter is a WHERE on the rows; the measure filter is a HAVING on the month’s sum. To filter a population by a measure and then report other measures over it, use a segment instead; a measure filter inside a segment becomes a HAVING on the segment CTE.

Validation messages

Next steps

• Segments for population membership, including OR across facts
• Shorthand expressions for the one-line filter syntax
• Errors and corrections