Filtering

where chooses which rows a read returns, or which rows a PATCH or DELETE touches. It uses the same filter syntax as row filters and payload checks, written in the query string with brackets:

where[<field>][<operator>]=<value>

where[status][_eq]=ASSIGNED
where[due_at][_lt]=$CURRENT_TIMESTAMP
where[site.customer.name][_ilike]=%mercado%

Your where is combined with your row filters using AND. It can narrow what you may see, never widen it.

Operators

Row filters › Operators explains each operator in detail, including how rows with no value behave.

A value is converted to the column's type: true or false for booleans, a number for numeric columns (400 INVALID_TYPE otherwise). Anything else is cast by PostgreSQL. Remember to URL-encode values: % is %25, a space is %20. curl -G --data-urlencode does it for you.

Several conditions

Conditions on different fields combine with AND:

where[status][_eq]=ASSIGNED&where[priority][_eq]=URGENT

So do two operators on the same field:

where[quantity][_gt]=0&where[quantity][_lte]=50

For OR and NOT, use _or, _and and _not. _or and _and take a list, written with indexes:

where[_or][0][priority][_eq]=URGENT&where[_or][1][due_at][_lt]=$CURRENT_TIMESTAMP

That reads "urgent, or overdue". _not takes one filter:

where[_not][status][_in]=COMPLETED,CANCELLED

Groups nest: where[_or][0][_and][0][...]. A query string holds at most 1000 values and 64 bracket levels per key; past them the request is refused (QUERY_PARAMETER_LIMIT_EXCEEDED, QUERY_DEPTH_LIMIT_EXCEEDED). Filters can nest up to api.max_filter_depth levels (10).

Relation paths

Any field can be a dotted path through foreign keys, up to three steps:

where[site.branch.code][_eq]=SAL
where[technician.user.email][_ilike]=%@acme-field.example

You need READ on each path you filter by, as for the fields you read.

Variables

Periods: today, yesterday, this_week, last_week, this_month, last_month, this_quarter, last_quarter, this_year, last_year. Weeks start on Monday. PostgreSQL calculates them in your time zone, so "today" starts at your midnight.

# orders reported this month
where[reported_at][_gte]=$PERIOD.this_month.from&where[reported_at][_lt]=$PERIOD.this_month.to

# changed in the last 24 hours
where[updated_at][_gte]=$INTERVAL.24h

# my open orders
where[technician.user][_eq]=$USER.id&where[status][_nin]=COMPLETED,CANCELLED

# parts at or below their reorder point
where[quantity][_lte]=$ROW.reorder_point

$INTERVAL.7d always means "seven days ago from now". It isn't an interval value you can add to a column. Inside _in and _nin only literal values and $USER.* are accepted.

Limits

To protect the database, a query's size is bounded. Each limit is a setting:

An administrator can turn off _regex, the containment operators, having and count per instance (query.allow_*), for large tables without the right indexes. A request using one then gets 400 QUERY_FEATURE_DISABLED.

The SQL it runs

This is the SQL TotalBase sent to PostgreSQL for the example above, as PostgreSQL's own log recorded it - ids shortened, long column lists cut with …. Every value travels as a parameter ($1), never inside the SQL text. "Urgent or overdue, in the Salta branch":

GET /api/public/table/work_orders?fields=number&where[_or][0][priority][_eq]=URGENT&where[_or][1][due_at][_lt]=$CURRENT_TIMESTAMP&where[site.branch.code][_eq]=SAL

SELECT t0."number" AS "number"
FROM "public"."work_orders" AS t0
LEFT JOIN "public"."sites" AS t1 ON t1."id" = t0."site"
LEFT JOIN "public"."branches" AS t2 ON t2."id" = t1."branch"  -- site.branch.code, two steps
WHERE ((t0."priority" = $1 OR t0."due_at" < CURRENT_TIMESTAMP) AND t2."code" = $2)
ORDER BY t0."id" ASC
LIMIT $3 OFFSET $4;                                           -- $1 = 'URGENT', $2 = 'SAL'

$CURRENT_TIMESTAMP becomes PostgreSQL's own CURRENT_TIMESTAMP, and $PERIOD.this_month.from becomes date_trunc('month', CURRENT_TIMESTAMP): PostgreSQL computes them, in the session's time zone, never the client.

Next steps

• Aggregating.
• Row filters: the same syntax in permissions.