Filtering Expressions

Overview

Devii's filtering expression syntax enables complex, expressive querying capabilities. This mini-language is deliberately designed to be similar to SQL's WHERE clause but introduces unique features that make it more versatile. Specifically, it supports:

Comparisons involving NULL: You can directly compare values to NULL using equality (=) or inequality (!=).

Example: regionid != null translates to regionid IS NOT NULL in SQL.

Aggregate Function Comparisons: Devii allows direct comparisons against the return values of aggregate functions like AVG, MIN MAX, SUM, or COUNT.
Subquery Evaluations: Devii can evaluate subqueries against related tables, allowing intricate cross-referencing.

This syntax was designed with developers in mind, taking inspiration from familiar programming languages such as C/C++, Java, JavaScript, and Python while integrating key SQL-style operators for consistency.

caution

Devii uses a custom filter expression language on its GraphQL query and Aggregates endpoints. This is NOT standard SQL and NOT JSON/GraphQL filter syntax. It is a Devii-specific DSL that parses into SQL WHERE clauses server-side.

Filter expressions are passed as string arguments to GraphQL fields on both the query and Aggregates endpoints:

Queries:

{
  people(filter: "name='Boba Fett'") {
    name
    birthYear
    films_collection {
      title
    }
  }
}

Aggregates:

{
  Aggregates{
    avg(subquery:"query{films{budget}}")
    
  }
}

caution

Mutations do not support filter arguments. Use id for targeted updates and deletes

mutation { update_opportunities(id: 42, input: { score: 0.8 }) { id } }

mutation { delete_opportunities(id: 42) { id } }

Syntax Overview

Literals and Operators:

Strings: Strings must be enclosed in single quotes (') inside the filter expression. See Quoting Rules below.
Parentheses: Parentheses (()) can group expressions and define precedence.
Operators:
- Standard comparison operators: =, !=, <, >, <=, >=
- Mathematical operators: +, -, *, /, %, ^
- Bitwise operations: &, |, #, <<, >>
String Matching: The LIKE (case-sensitive) and ILIKE (case-insensitive) operators are supported.
Set Membership: The IN operator uses square brackets: field IN [val1, val2].
Whitespace: Whitespace is insignificant, allowing flexible formatting.
Case Sensitivity: All word-based operators are case-insensitive to write (e.g. and, AND, And all work). Note that LIKE performs case-sensitive matching while ILIKE is case-insensitive.
Function Calls: Syntax: function(arg1, arg2, ...argN).

Quoting Rules

There are two distinct levels of quoting when writing filter expressions, and mixing them up is one of the most common sources of errors.

1. The filter argument itself must use double quotes (")

The filter argument passed to a GraphQL field is a string and must be wrapped in double quotes.

# ✅ Correct — filter argument in double quotes
{
  devii_users(filter: "email = NULL") {
    name
  }
}

# ❌ Wrong — filter argument in single quotes
{
  devii_users(filter: 'email = NULL') {
    name
  }
}

2. String values inside the filter must use single quotes (')

Any string value being compared to inside the filter expression must use single quotes.

# ✅ Correct — string value in single quotes
{
  devii_users(filter: "name = 'Joe'") {
    name
  }
}

# ❌ Wrong — string value with no quotes
{
  devii_users(filter: "name = Joe") {
    name
  }
}

# ❌ Wrong — quotes are reversed (filter in single, value in double)
{
  devii_users(filter: 'name = "Joe"') {
    name
  }
}

3. Integers can be written bare or in single quotes — both work

# ✅ Both of these are valid
{
  devii_users(filter: "userid = 2") {
    name
    userid
  }
}

{
  devii_users(filter: "userid = '2'") {
    name
    userid
  }
}

Summary

What	Quote type	Example
The `filter` argument	Double quotes `"`	`filter: "..."`
String values inside the filter	Single quotes `'`	`name = 'Joe'`
Integer values inside the filter	None (or single quotes)	`userid = 2` or `userid = '2'`
NULL, true, false	No quotes	`email = NULL`, `is_active = true`

Apostrophes in string values

Standard SQL '' double-apostrophe escaping does not work. Avoid filter values that contain apostrophes, or use ILIKE with wildcards as a workaround:

-- Does NOT work:  name = 'O''Brien'
-- Workaround:     name ILIKE '%Brien%'

Defined Classes of Expressions

Column References:

Allows direct reference to table columns for comparison or filtering.
- Syntax: column_name <operator> value
- Example: price > 100
  - Matches records where the price column is greater than 100.

Role Attributes

Enables access to attributes of the current role using the $_PRINCIPAL keyword, allowing queries to dynamically filter data based on the role's context.

Syntax: $_PRINCIPAL.attribute
Available attributes:
- roleid: Unique identifier of the role.
- capabilities: Permissions or abilities associated with the role.
- classes: Groups or categories assigned to the role.
- children / parentid: Role hierarchy.
- tenantid: Tenant-specific identifier.
  - Example: creatorid = $_PRINCIPAL.roleid
    - This matches records where the creatorid column equals the roleid of the current role.

Value Comparisons

Performs comparisons between two values (literals, columns, functions, or math expressions).

Operator	Syntax	Example	Supported
Equal	`=`	`agency = 'NSF'`	✅ Yes
Not equal	`!=`	`agency != 'NSF'`	✅ Yes
Greater than	`>`	`score > 0.5`	✅ Yes
Less than	`<`	`score < 0.5`	✅ Yes
Greater or equal	`>=`	`score >= 0.5`	✅ Yes
Less or equal	`<=`	`score <= 0.5`	✅ Yes
IN (set membership)	`IN [...]`	`id IN [1, 2, 3]`	✅ Yes — square brackets only
LIKE pattern	`LIKE`	`title LIKE '%NSF%'`	✅ Yes
ILIKE pattern	`ILIKE`	`title ILIKE '%nsf%'`	✅ Yes
SQL not equal	`<>`	`score <> 0.5`	❌ Use `!=`
NOT LIKE / NOT ILIKE	`NOT LIKE`	—	❌ Use `NOT ( x like/ilike "string%" )`
BETWEEN	`BETWEEN x AND y`	—	❌ Use `>= x AND <= y`

IN operator — square brackets required

Devii uses square brackets for IN lists. Using parentheses will fail.

id IN [1, 2, 3] ✅ id IN (1, 2, 3) ❌

String wildcards in LIKE and ILIKE: % for multi-character matches.
- % matches any sequence of characters.
- _ matches any single character.
- Example: name ILIKE 'foo%' — matches records whose name starts with 'foo'.

NULL Handling

Use = and != directly with NULL. Standard SQL IS NULL / IS NOT NULL syntax is not supported.

Syntax	Example	Supported
`= NULL`	`archived_at = NULL`	✅ Yes
`!= NULL`	`response_deadline != NULL`	✅ Yes
`IS NULL`	`archived_at IS NULL`	❌ Use `= NULL`
`IS NOT NULL`	`response_deadline IS NOT NULL`	❌ Use `!= NULL`

archived_at = NULL          -- record has not been archived
response_deadline != NULL   -- record has a deadline set

Boolean Values

Use = true and = false directly. IS TRUE / IS FALSE are not supported.

Syntax	Example	Supported
`= true`	`is_cross_college = true`	✅ Yes
`= false`	`is_external = false`	✅ Yes
`IS TRUE`	—	❌ Use `= true`
`IS FALSE`	—	❌ Use `= false`

Function Calls

Executes whitelisted functions, with arguments that may include literals, columns, or other expressions.

Syntax: function(arg1, arg2, ...)
- Core Operators: min, max, avg, count, sum, abs (absolute), round, length, lower, upper, contains
  - Examples: createdate = max(createdate)
    - Matched the record with the latest createdate
- Geospatial Operators: st_x, st_y, st_isempty, st_isvalid, st_srid, st_astext, st_buffer, st_boundary, st_envelope, st_centroid, st_equals, st_union, st_intersection, st_difference, st_symdifference, st_contains, st_covers, st_crosses, st_disjoint, st_distance, st_intersects, st_overlaps, st_touches, st_within, st_geomfromtext, geomunion, st_extent, st_z, st_m
  - Example: st_contains(geom, st_setsrid(st_makepoint(-96.8, 46.9), 4326))
    - Matches records whose geometry contains the latlong point 46.9 N, 96.8 W (in northern Fargo, ND), ensuring the coordinate is in the WGS-84 coordinate system.

Math Operations

Performs binary mathematical operations on columns, literals, or expressions directly in expressions.
Operators:
- binary: +, -, *, /, %, ^
- bitwise: & (bitwise AND), | (bitwise OR), # (bitwise XOR), <<(left shift), >> (right shift)

id % 3 = 0                      -- records where id is divisible by 3
p_win * p_go > 0.3              -- computed priority threshold
estimated_funding / 12 > 50000  -- average monthly value
score + 0.1 > 0.6               -- adjusted threshold

Unitary Math Operations

Applies mathematical operations to a single value using prefix or postfix operators.

Operators: @ (absolute), |/ (square root), ! (factorial), ~ (bitwise NOT — intended for bitmask columns; behavior may vary by database))
Example: id = round(|/ 25)
- Matches the record where the id equals the square root of 25, rounded to the nearest integer.

AND Operation

Combines two subexpressions; both must evaluate to True.

Syntax: expr1 AND expr2
Example: creatorid = $_PRINCIPAL.roleid AND createdate > '2020-01-01'
- Matches records created by the current role after January 1, 2020.

OR Operation

Combines two subexpressions; one must evaluate to True.

Syntax: expr1 OR expr2
Example: status = 'active' OR status = 'pending'
- Matches records where the status is either 'active' or 'pending'.

NOT Operation

Inverts the result of a subexpression.

Syntax: NOT expr
Example: NOT (status = 'inactive')
- Matches records where the status is anything but 'inactive'.

Where possible, prefer inverting the comparison operator directly, as it is more broadly compatible:

agency != 'NSF'     -- preferred over: NOT (agency = 'NSF')
score <= 0.5        -- preferred over: NOT (score > 0.5)

Logical Grouping and Precedence

Use parentheses to control evaluation order. AND binds more tightly than OR by default.

-- Without grouping: AND is evaluated first
agency = 'NSF' OR agency = 'DOD' AND score > 0.5

-- With grouping: OR is evaluated first
(agency = 'NSF' OR agency = 'DOD') AND score > 0.5

-- Complex grouping
((score > 0.5) AND (agency = 'NSF')) OR (estimated_funding > 1000000)

ANY Operation (Relationship Subquery)

The ANY operation allows advanced subquery checks across related tables, enabling comparisons that traverse relationships between tables. It is particularly useful for scenarios where a match is determined by the existence of related records satisfying specific conditions.

Syntax: $COLLECTION_ATTRIBUTE ANY(ValueComparison).attribute
- $COLLECTION_ATTRIBUTE: Refers to a collection attribute in the current table that maps to related records. This can represent a one-to-many or many-to-many relationship.
- ANY(ValueComparison): Specifies a condition applied to the related records within the collection. If any of the related records meet this condition, the expression evaluates to true.
- .attribute: Accesses a specific attribute of the related table after confirming the match.
Example: company.employees_collection ANY(roleid = $_PRINCIPAL.roleid).companyid
- Matches records where any employee has the same companyid (is in the same company) as the current role.
  - company — refers to the relationship between the current table and the company table.
  - employees_collection — accesses the related employees records.
  - ANY(roleid = $_PRINCIPAL.roleid) — filters to employee where the roleid matches the current user's role ID.
  - .companyid — accesses the companyid attribute of the matching employee.
  - This translates into an EXISTS SQL Query
    EXISTS ( SELECT 1 FROM employees e WHERE e.companyid = company.id AND e.roleid = :principal_roleid )

Data Types

Type	Syntax	Example
String	Single quotes `'...'`	`agency = 'NSF'`
Integer	Bare number	`id = 42`
Float	Decimal notation	`score > 0.655`
Boolean	`true` / `false`	`is_external = true`
NULL	`NULL`	`archived_at = NULL`
Date string	Single-quoted ISO date	`createdate > '2020-01-01'`

Features Not Available in Filter Expressions

The following standard SQL features are not available. Use the workarounds listed.

Feature	Workaround
`BETWEEN x AND y`	`field >= x AND field <= y`
`NOT IN [...]`	Multiple `!=` joined with `AND` or use NOT operation around an IN
`NOT LIKE` / `NOT ILIKE`	Use the NOT operation around an ILIKE
`IS NULL` / `IS NOT NULL`	`= NULL` / `!= NULL`
`IS TRUE` / `IS FALSE`	`= true` / `= false`
`<>` (SQL not equal)	`!=`
`CAST(... AS type)` / `::type`	Not available
`CASE WHEN...THEN...END`	Not available
`EXISTS (subquery)`	Use the `ANY` operation for relationship checks
JSONB operators (`->>`, `#>>`, `field.subfield`)	Use a generated column in PostgreSQL, or filter client-side
`SIMILAR TO` / regex (`~`, `~*`)	Not available
Inline subqueries	Use `Aggregates` for counts; use `ANY` for existence checks
Cross-table joins in filter	Resolve IDs first, then filter; or use `ANY`

Filter Expressions and Policy Rules

Filter Expressions are used by Policy Rules to define precise access controls for users.

How Policy Rules and Filter Expressions Interact

Policy Rules are applied in order of least to most restrictive:

Root Role: Bypasses all rules.
Global Rule: Applies universally unless overridden.
Role Class: Defines access for a general group of users.
Role Class with a Filter: Adds conditional access to a general group.
Specific Roleid: Assigns access to an individual user or role.
Specific Roleid with a Filter: Grants individual access with additional conditions.

note

Root Role has special global admin privileges for the tenancy and should not be assigned a role class, nor any policy applied. The Root Role is created when the Devii account is first created, your login/username will be same for both Devii Portal role or Devii Root role, the passwords for each should be different, instructions on how to change the password for the Root Role are contained HERE

See Applicability Precedence for additional details.

Example PBAC filter expressions:

-- Researcher can only see opportunities they lead
lead_personnel_id = $_PRINCIPAL.roleid

-- External partners only see non-internal records
is_external = true

-- Scoped to a specific department
department_id IN [5, 12, 18]

-- Only active, non-archived records
archived_at = NULL AND is_active = true

Creating a Policy

Follow these steps to design an effective access policy:

Identify Your Data: Review the tables and the data you manage.
Define Your Users: Determine who will access the data.
Group Your Users: Organize users into categories based on their needs.
Apply Filters (if necessary): Add filters to refine access for specific groups.
1. Example: Allow a group to view only the data they created.

Advanced Examples

Advanced Policy Example:

(company.employees_collection ANY(roleid = $_PRINCIPAL.roleid).status = 'active')
AND 
(
    (policy.startdate <= CURRENT_DATE AND policy.enddate >= CURRENT_DATE)
    OR 
    policy.type IN ['standard', 'premium']
)
AND 
$_PRINCIPAL.tenantid = company.tenantid

Explanation:

Filter Active Employees:

company.employees_collection ANY(roleid = $_PRINCIPAL.roleid).status = 'active'

Checks if the current role is associated with any active employees in the company.
Uses a relationship subquery (ANY) to ensure the roleid matches an active employee.

Policy Date Validity:

(policy.startdate <= CURRENT_DATE AND policy.enddate >= CURRENT_DATE)

Ensures the policy is currently valid by comparing the startdate and enddate against the current date.

Policy Type:

policy.type IN ['standard', 'premium']

Allows only specific types of policies (standard or premium) if the policy is not active based on dates.

Tenant Validation:

$_PRINCIPAL.tenantid = company.tenantid

Ensures the current role’s tenant ($_PRINCIPAL.tenantid) matches the company’s tenant. This enforces multi-tenant segregation.

Logical Grouping with AND/OR:

AND
(
    (policy.startdate <= CURRENT_DATE AND policy.enddate >= CURRENT_DATE)
    OR 
    policy.type IN ['standard', 'premium']
)

Combines both conditions (date and type) with an OR, to match either (or both) conditions.

Quick Reference

=== SUPPORTED ===
field = 'value'                  -- string equality (single quotes required)
field = 42                       -- numeric equality
field != 'value'                 -- not equal
field > 0.5                      -- greater than (also <, >=, <=)
field LIKE '%pattern%'           -- case-sensitive pattern match
field ILIKE '%pattern%'          -- case-insensitive pattern match
field IN ['a', 'b', 'c']        -- set membership — square brackets required
field = NULL                     -- is null
field != NULL                    -- is not null
field = true / field = false     -- boolean
expr1 AND expr2                  -- both must be true
expr1 OR expr2                   -- either must be true
(a OR b) AND c                   -- use parentheses to control precedence
NOT (expr)                       -- invert a subexpression
field1 * field2 > 0.3           -- arithmetic in filter
field % 2 = 0                    -- modulo
createdate = max(createdate)     -- aggregate function comparison
creatorid = $_PRINCIPAL.roleid   -- current role attribute
coll ANY(condition).attr         -- relationship subquery
field <= CURRENT_DATE            -- current date keyword

=== NOT SUPPORTED ===
field <> value                   -- use != instead
field BETWEEN x AND y            -- use >= x AND <= y
field NOT LIKE '%x%'             -- use NOT ( field LIKE '%x%' )
field NOT IN [...]               -- use NOT ( field IN [...] )
field IS NULL / IS NOT NULL      -- use = NULL / != NULL
field IS TRUE / IS FALSE         -- use = true / = false
field->>'key'                    -- no JSONB operators
CAST(field AS type)              -- no type casting
SIMILAR TO / regex (~ or ~*)     -- not available

Overview​

Syntax Overview​

Literals and Operators:​

Quoting Rules​

Defined Classes of Expressions​

Column References:​

Role Attributes​

Value Comparisons​

NULL Handling​

Boolean Values​

Function Calls​

Math Operations​

Unitary Math Operations​

AND Operation​

OR Operation​

NOT Operation​

Logical Grouping and Precedence​

ANY Operation (Relationship Subquery)​

Data Types​

Features Not Available in Filter Expressions​

Filter Expressions and Policy Rules​

How Policy Rules and Filter Expressions Interact​

Policy Rules are applied in order of least to most restrictive:​

Creating a Policy​

Advanced Examples​

Advanced Policy Example:​

Explanation:​

Filter Active Employees:​

Policy Date Validity:​

Policy Type:​

Tenant Validation:​

Logical Grouping with AND/OR:​

Quick Reference​