T-SQL Filter Query Language Reference

Overview

The uProduce REST API supports server-side filtering using a subset of T-SQL (Transact-SQL) syntax. This allows you to efficiently filter, sort, and paginate large datasets directly on the server, reducing network traffic and improving performance.

There are two main contexts where T-SQL filtering is used:

• Filter - Used with GET list methods (e.g., /v1/jobs, /v1/campaigns)

• ExportRequest - Used with POST export methods for CSV exports

Security Note

All filter expressions are validated, sanitized, and parameterized before execution. The API uses a whitelist approach, allowing only specific T-SQL constructs to prevent SQL injection attacks.

Filter Parameters

Filter parameters are used with GET list methods to control which items are returned and in what order.

filter.Where

Type: string

Default: empty (no filter)

T-SQL WHERE clause for filtering results. Do not include the WHERE keyword.

filter.OrderBy

Type: string

Default: FriendlyId DESC

T-SQL ORDER BY clause for sorting. Do not include the ORDER BY keywords.

filter.Limit

Type: uint (max: 500)

Default: 100

Maximum number of items to return. Server enforces a maximum of 500.

filter.Offset

Type: uint

Default: 0

Number of items to skip (for pagination).

filter.IncludeDeleted

Type: bool

Default: false

Include soft-deleted items in results.

Example: Filter Jobs

Export Request Parameters

Export request parameters are used with POST export methods to generate CSV exports with custom field selection.

exportRequest.Select

Type: string

Default: *

T-SQL SELECT clause specifying fields to export. Use * for all fields.

exportRequest.Where

Type: string

Default: empty (no filter)

T-SQL WHERE clause for filtering exported data.

exportRequest.OrderBy

Type: string

Default: FriendlyId DESC

T-SQL ORDER BY clause for sorting exported data.

exportRequest.Limit

Type: uint

Default: 0 (no limit)

Maximum items to export. Use 0 for no limit.

exportRequest.Offset

Type: uint

Default: 0

Number of items to skip before exporting.

exportRequest.IncludeDeleted

Type: bool

Default: false

Include soft-deleted items. For Jobs, this means "only deleted".

Example: Export Jobs to CSV

Supported Operators

Comparison Operators

Operator	Description	Example
=	Equals	Status = JobStatus.Completed
!= or <>	Not Equal	Type <> DocumentType.Email
>	Greater Than	RecordsCount > 100
<	Less Than	FriendlyId < 1000
>=	Greater Than or Equal	ProcessedRecordsCount >= 50
<=	Less Than or Equal	Priority <= 5
!<	Not Less Than (same as >=)	Score !< 80
!>	Not Greater Than (same as <=)	Score !> 100

Logical Operators

Operator	Description	Example
AND	Logical AND	Status = JobStatus.Completed AND Type = JobType.Print
OR	Logical OR	Status = JobStatus.Failed OR Status = JobStatus.Aborted
NOT	Logical NOT	NOT Status = JobStatus.Waiting

Pattern Matching

Operator	Description	Example
LIKE	Pattern matching with wildcards (% for any chars, _ for single char)	Name LIKE 'Test%'
NOT LIKE	Negated pattern matching	Name NOT LIKE '%draft%'
REGEXP_LIKE()	Regular expression matching	REGEXP_LIKE(Name, '^Test[0-9]+')

Membership Operators

Operator	Description	Example
IN	Value is in a list (max 100 values)	Status IN (JobStatus.Completed, JobStatus.Failed)
NOT IN	Value is not in a list	FriendlyId NOT IN (1, 2, 3)

NULL Checking

Operator	Description	Example
IS NULL	Value is NULL	Description IS NULL
IS NOT NULL	Value is not NULL	ModificationDate IS NOT NULL

Range Operators

Operator	Description	Example
BETWEEN ... AND	Value is within range (inclusive)	SubmitDate BETWEEN '2024-01-01' AND '2024-12-31'
NOT BETWEEN ... AND	Value is outside range	FriendlyId NOT BETWEEN 100 AND 200

Arithmetic Operators

Operator	Description	Example
+	Addition	ProcessedRecordsCount + FailedRecordsCount > 100
-	Subtraction	TotalRecords - ProcessedRecords = 0
*	Multiplication	Price * Quantity > 1000
/	Division	ProcessedRecords / TotalRecords > 0.9
%	Modulo (remainder)	FriendlyId % 10 = 0

Bitwise Operators

Operator	Description	Example
&	Bitwise AND	Flags & 4 = 4
|	Bitwise OR	Flags | 2 > 0
^	Bitwise XOR	Flags ^ 1 = 0
~	Bitwise NOT	~Flags & 255 = 254

Supported Functions

Date and Time functions

Function	Description	Example
GETDATE()	Returns current date and time	SubmitDate > GETDATE()
DATEADD(unit, number, date)	Adds an interval to a date	SubmitDate > DATEADD(day, -7, GETDATE())
DATEPART(unit, date)	Returns specified part of a date as integer	DATEPART(year, SubmitDate) = 2024
DATENAME(unit, date)	Returns specified part of a date as string	DATENAME(month, SubmitDate) = 'January'
YEAR(date)	Extracts year from date	YEAR(CreationDate) = 2024
MONTH(date)	Extracts month from date (1-12)	MONTH(CreationDate) >= 6
DAY(date)	Extracts day from date (1-31)	DAY(CreationDate) = 1

Date Units for DATEADD/DATEPART/DATENAME

Supported units: year, month, day, hour, minute

String Functions

Function	Description	Example
LEN(string)	Returns length of string	LEN(Name) > 10
LEFT(string, count)	Returns first N characters	LEFT(Name, 4) = 'Test'
RIGHT(string, count)	Returns last N characters	RIGHT(Name, 3) = 'Job'

Conversion Functions

Function	Description	Example
CAST(expr AS type)	Converts value to specified type (fails on error)	CAST(FriendlyId AS VARCHAR)
TRY_CAST(expr AS type)	Converts value to specified type (returns NULL on error)	TRY_CAST(CustomField AS INT) IS NOT NULL
CONVERT(type, expr [, style])	Converts value with optional format style	CONVERT(VARCHAR, SubmitDate, 23)
TRY_CONVERT(type, expr [, style])	Converts value (returns NULL on error)	TRY_CONVERT(DATETIME, '2024-01-01')

Conditional Functions

Function	Description	Example
IIF(condition, true_val, false_val)	Inline if-then-else	IIF(Status = JobStatus.Completed, 1, 0) = 1
CASE WHEN ... THEN ... END	Searched CASE expression	See examples below
CASE expr WHEN ... THEN ... END	Simple CASE expression	See examples below

CASE Expression Examples

NULL Handling Functions

Function	Description	Example
ISNULL(expr, default)	Returns default if expr is NULL	ISNULL(Description, '') != ''
COALESCE(expr1, expr2, ...)	Returns first non-NULL value (max 10 expressions)	COALESCE(Name, Description, 'Unknown') != 'Unknown'
NULLIF(expr1, expr2)	Returns NULL if expressions are equal	NULLIF(Status, JobStatus.Waiting) IS NOT NULL

XML Functions

Method	Description	Example
.value(xpath, type)	Extracts a value from XML	XmlData.value('(/root/id)[1]', 'INT') = 5
.exist(xpath)	Checks if XML element exists	XmlData.exist('/root/flag') = 1
.query(xpath)	Queries XML and returns XML fragment	XmlData.query('/root/items')

Data Types

The following data types are valid for use with CAST, CONVERT, TRY_CAST, and TRY_CONVERT functions:

Integer Types

Type	Description	Range
BIGINT	8-byte integer	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
INT	4-byte integer	-2,147,483,648 to 2,147,483,647
SMALLINT	2-byte integer	-32,768 to 32,767
TINYINT	1-byte integer	0 to 255

Decimal Types

Type	Description
DECIMAL / NUMERIC	Fixed precision and scale numbers
MONEY	Monetary values with 4 decimal places
SMALLMONEY	Smaller monetary values

Floating Point Types

Type	Description
FLOAT	8-byte floating point
REAL	4-byte floating point

Date and Time Types

Type	Description
DATETIME	Date and time (3.33ms precision)
SMALLDATETIME	Date and time (1 minute precision)
DATE	Date only
TIME	Time only
DATETIME2	Date and time (100ns precision)
DATETIMEOFFSET	Date and time with timezone

String Types

Type	Description
CHAR	Fixed-length character string
VARCHAR	Variable-length character string
NCHAR	Fixed-length Unicode string
NVARCHAR	Variable-length Unicode string

Other Types

Type	Description
BIT	Boolean (0, 1, or NULL)
UNIQUEIDENTIFIER	GUID/UUID

Field Naming Conventions

Direct Properties

Reference simple entity properties directly by name. Field names are case-insensitive.

Name, FriendlyId, CreationDate, Status, Type, Description

Nested Properties

Reference nested properties by concatenating the property names (without dots). For example, to access the Name property of a related Campaign entity:

Nested Property	Filter Field Name
Campaign.Name	CampaignName
Account.Name	AccountName
Creator.Name	CreatorName
Parent.Name	ParentName
Modifier.Name	ModifierName

Example: Filter by Nested Property

Important

Do NOT use dot notation in filter expressions. Use concatenated names instead:

• Correct: CampaignName = 'Test'

• Incorrect: Campaign.Name = 'Test'

Enum Values

Reference enum values using the syntax EnumType.EnumValue. The enum value name is case-insensitive.

Some Commonly Used Enums

JobStatus

Value	Description
JobStatus.Waiting	Job is waiting to be processed
JobStatus.InProgress	Job is currently being processed
JobStatus.Completed	Job completed successfully
JobStatus.Failed	Job failed with errors
JobStatus.Aborting	Job is being aborted
JobStatus.Aborted	Job was aborted
JobStatus.Deployed	Job is deployed (for on-demand)
JobStatus.Suspended	Job is suspended

JobType

Value	Description
JobType.Print	Print/PDF production job
JobType.Proof	Proof job (lower quality preview)
JobType.Email	Email delivery job
JobType.TestEmail	Test email job
JobType.CSVXData	CSV data export job

CampaignType

Value	Description
CampaignType.Unknown	Unknown campaign type
CampaignType.Regular	Regular batch campaign
CampaignType.Circle	Circle (real-time) campaign

DocumentType

Value	Description
DocumentType.InDesign	InDesign document
DocumentType.HTML	HTML document
DocumentType.Email	Email document
DocumentType.XML	XML document
DocumentType.Text	Text document

MessageSeverity

Value	Description
MessageSeverity.None	No messages
MessageSeverity.Info	Informational messages
MessageSeverity.Warning	Warning messages
MessageSeverity.Error	Error messages

Complete Enum List

The following enums are registered and available for use in filters:

AccessPermissionType	FontStyleFlag	ProductionServiceType
AuthenticationContextType	HotFolderFileStatus	RecipientFilterType
CampaignType	JobHierarchy	RecipientTableFieldMappingType
DataSourceConnectionEngine	JobStatus	SettingType
DataSourceConnectionType	JobType	SystemSettingType
DataSourceDataType	LicenseConnectedClientType	TableColumnDataType
DataSourceHostStrategy	MeasurementUnit	TableColumnsInfoType
DataSourceTableType	MessageContext	TrackingEventType
DeliveryProviderSenderStatusType	MessageSeverity	TrackingSegmentType
DocumentType	MessageType	UserLanguage
EmailBodyContentType	OnDemandRequestStatus	UserTokenType
EntityType	PlanPartsRequestStatus
FontFormat	PlanValidationObjectType

Example: Using Enum Values

Expression Limits

The following limits are enforced to ensure performance and security:

Limit	Maximum Value
WHERE clause length	2,000 characters
ORDER BY clause length	200 characters
SELECT clause length	500 characters
Function parameters	10 per function
IN clause values	100 values
CASE WHEN clauses	20 clauses
COALESCE expressions	10 expressions
String literal length	255 characters
Total SQL parameters	50 parameters
SELECT elements	100 elements
XML method parameters	3 parameters
Alias name length	128 characters
filter.Limit maximum	500 items

Complete Examples

Example 1: Filter Jobs by Status and Date Range

Get completed jobs from the last 7 days

Example 2: Paginated Results with Complex Filter

Get page 3 of campaigns (20 items per page) with specific criteria

Example 3: Export with Custom Fields and Formatting

Export jobs with formatted date and status

Example 4: Filter with Multiple Conditions

Get failed or aborted jobs with errors

Example 5: String Pattern Matching

Find campaigns with names matching a pattern

Example 6: NULL Handling

Find documents without descriptions

Example 7: Complex CASE Expression

Filter using conditional logic

Example 8: Date Extraction

Get jobs submitted in a specific month

Not Supported

The following T-SQL features are not supported in filter expressions for security and performance reasons:

Feature	Reason
JOINs	Complex queries impact performance
Subqueries	Security and performance concerns
IN (SELECT ...)	Subqueries not allowed; use value lists instead
Aggregate functions (SUM, COUNT, AVG, etc.)	Use dedicated report/export endpoints
GROUP BY / HAVING	Aggregation not supported in filters
EXECUTE / stored procedures	Security restriction
INSERT / UPDATE / DELETE	Filters are read-only
Dynamic SQL	Security restriction
Window functions (ROW_NUMBER, RANK, etc.)	Not implemented
UNION / EXCEPT / INTERSECT	Set operations not supported