Filter - Advanced Topics

Value Types

Values are automatically typed based on content:

Input	Type	SQL Output
123	Number	123
-45.67	Number	-45.67
ACTIVE	String	'ACTIVE'
John Doe	String	'John Doe'

Type Casting

Type casting allows you to explicitly convert columns or values to different PostgreSQL types using the :: operator. This is useful when PostgreSQL cannot automatically compare values of different types.

Value Type Casting

You can cast values (the right side of comparisons) using the :: operator followed by a type name.

Supported Cast Types

Cast Operator	PostgreSQL Type	Description
::text	TEXT	String/text type
::int	INTEGER	32-bit integer
::bigint	BIGINT	64-bit integer
::numeric	NUMERIC	Arbitrary precision decimal
::boolean	BOOLEAN	Boolean (true/false)
::date	DATE	Date (without time)
::timestamp	TIMESTAMP	Timestamp (without timezone)
::timestamptz	TIMESTAMPTZ	Timestamp (with timezone)

Examples

Example 1 - Filter:

(code,1::text)

SQL:

"code" = '1'::text

Explanation: The column code is a string type, but 1 is automatically detected as a number. Casting 1::text allows PostgreSQL to compare the string column with a text value.

---

Example 2 - Filter:

(userId,42::bigint)

SQL:

"userId" = 42::bigint

---

Example 3 - Filter:

(price,100.50::numeric)

SQL:

"price" = 100.50::numeric

---

Example 4 - Filter:

(isActive,true::boolean)

SQL:

"isActive" = true::boolean

---

Example 5 - Filter:

(createdAt,2024-01-15::date)

SQL:

"createdAt" = '2024-01-15'::date

---

Example 6 - Filter:

(updatedAt,2024-01-15 10:30:00::timestamp)

SQL:

"updatedAt" = '2024-01-15 10:30:00'::timestamp

---

Example 7 - Filter:

(scheduledAt,2024-01-15 10:30:00-05::timestamptz)

SQL:

"scheduledAt" = '2024-01-15 10:30:00-05'::timestamptz

Type Casting with Operators

Type casting works with all comparison operators:

Example 8 - Filter:

(age,18::int)and(balance,gt,1000.00::numeric)

SQL:

"age" = 18::int AND "balance" > 1000.00::numeric

---

Example 9 - Filter:

(code,in,A1::text|B2::text|C3::text)

SQL:

"code" IN ('A1'::text, 'B2'::text, 'C3'::text)

Column Type Casting

You can also cast columns (the left side of comparisons) using the same ::type syntax. This is particularly useful for pattern matching on integer columns or comparing columns of different types.

Common use case: Casting integer columns to text enables pattern matching with has, sw, and ew operators.

Supported Cast Types

Column casting supports the same types as value casting:

Cast Operator	PostgreSQL Type	Common Use Case
::text	TEXT	Pattern matching on numeric columns
::int	INTEGER	Comparing text columns as integers
::bigint	BIGINT	Large integer comparisons
::numeric	NUMERIC	Precise decimal comparisons
::boolean	BOOLEAN	Boolean comparisons on text fields
::date	DATE	Date comparisons without time
::timestamp	TIMESTAMP	Timestamp comparisons without timezone
::timestamptz	TIMESTAMPTZ	Timestamp comparisons with timezone

Examples

Example 1 - Pattern matching on integer column:

(orderNumber::text,has,23)

SQL:

"orderNumber"::text ILIKE '%23%'

Explanation: Cast the integer orderNumber column to text to search for orders containing “23” anywhere in the number (e.g., 1230, 2300, 5231).

---

Example 2 - Comparing text column as integer:

(stringId::int,gt,100)

SQL:

"stringId"::int > 100

Explanation: Cast a text column containing numeric values to integer for proper numeric comparison.

---

Example 3 - JSON field casting:

(user.metadata.age::int,gte,18)

SQL:

("user"."metadata"->>'age')::int >= 18

Explanation: JSON fields are extracted as text, so cast to integer for numeric comparison. The JSON extraction is wrapped in parentheses because the :: cast operator has higher precedence than the ->> operator in PostgreSQL.

---

Example 4 - Combining column and value casting:

(code::int,100::int)

SQL:

"code"::int = 100::int

Explanation: Both sides can be cast when needed for type compatibility.

---

Example 5 - Column casting with IN operator:

(status::text,in,1|2|3)

SQL:

"status"::text IN (1, 2, 3)

Explanation: Cast an integer status column to text to match against text values.

---

Example 6 - Timestamp to date comparison:

(createdAt::date,2024-01-15)

SQL:

"createdAt"::date = '2024-01-15'

Explanation: Cast a timestamp column to date to compare only the date part, ignoring time.

---

Example 7 - Multiple casted columns with logical operators:

(orderNumber::text,has,23)and(total::numeric,gte,50.00)

SQL:

"orderNumber"::text ILIKE '%23%' AND "total"::numeric >= 50.00

Explanation: Combine multiple column casts in complex filter expressions.

SQL Injection Prevention

The filter parser is designed to prevent SQL injection:

1. Column names are always double-quoted as SQL identifiers
2. Values are escaped using pg-format library
3. Parentheses in values are rejected by the grammar (cannot inject sub-expressions)
4. SQL keywords in values become harmless literals

Examples of safely escaped input:

Example 1 - Filter:

(name,O'Brien)

SQL:

"name" = 'O''Brien'

---

Example 2 - Filter:

(password,"' OR '1'='1")

SQL:

"password" = ''' OR ''1''=''1'

Explanation: Quoted strings allow any characters including single quotes. The value is safely escaped by pg-format.

---

Example 3 - Filter:

(id,1; DROP TABLE users; --)

SQL:

"id" = '1; DROP TABLE users; --'

---

Example 4 - Filter:

(path,C:\\Windows\\System32)

SQL:

"path" = E'C:\\Windows\\System32'

Error Cases

The parser rejects malformed input with a SyntaxError:

Invalid Input	Reason
()	Empty expression
(field)	Missing value
(field,)	Empty value
(field,gt,)	Empty operator value
(,value)	Missing column
(a.b.c.d,value)	Column path exceeds 3 parts
(((a,1)or(b,2))and(c,3))	Nesting depth exceeds limit