Building Queries

Query Builder uses the selected workflow fields to decide which filtering and aggregation options are available. This page explains how to choose fields, configure criteria, and preview saved results.

Selecting Data Fields

The Data Fields dropdown appears after you select a workflow. It includes fields from the workflow's main form and supplementary forms.

Select the fields that should be included in the API response. Each selected field appears in the Selection Criteria panel, where you can add a filter or aggregation.

Selection Criteria

Selection criteria depend on the field type.

For each selected field, use either:

• A filter to decide which submissions are returned
• An aggregation to calculate a value from matching submissions

Do not configure both on the same field. If both are configured, the aggregation takes priority and the filter is silently discarded when saved.

Numeric and Currency Fields

Numeric and currency fields support comparison filters and numeric aggregations.

Filter Operations

Operation	Details
Equals	Exact numeric match
Greater Than	Returns submissions where the value is strictly greater than the input
Less Than	Returns submissions where the value is strictly less than the input

Aggregation Operations

Aggregation	Details
Sum	Adds all matching numeric values
Average	Calculates the mean of matching numeric values
Minimum	Finds the smallest matching numeric value
Maximum	Finds the largest matching numeric value
Count	Counts entries with numeric values
Count Distinct	Counts unique numeric values

Aggregation only processes numeric values. Non-numeric values in the field are skipped.

Example:

Field: amount
Aggregation: Sum
API result: "aggregate": { "amount": 75000 }

Text Fields

Text fields include textfield, textarea, email, and phoneNumber.

Operation	Details
Equals	Case-insensitive exact match
Contains	Case-insensitive substring match
Starts With	Value begins with the input text
Ends With	Value ends with the input text

Example:

Field: department
Operation: Equals
Value: IT

Date and Datetime Fields

Date and datetime fields show a date range picker with From and To values. Both bounds are inclusive.

Internally, date range filters are saved as a from_to operation.

Example:

From: 2025-01-01
To: 2025-03-31

Other or Unknown Field Types

Other field types support general matching operations.

Operation	Details
Equals	Case-insensitive exact match
Contains	Substring match
Is Null	Field has no value
Is Not Null	Field has a value

note

Is Null and Is Not Null appear in the UI, but they are not reliably saved in the current version because the save logic requires a non-empty filter value. Use API URL parameters for null checks when needed.

Checkbox and Time Fields

Checkbox fields show Is True and Is False options in the UI. These options are not reliably saved in the current version, so avoid relying on saved checkbox filters.

Time fields use date-level boundaries internally, from start of day to end of day. Time-only filtering may not produce the expected result.

Query Preview

When editing an existing query, click Show Query Preview to run the saved query and display the JSON result.

Use preview to check:

• Which field keys appear in the response
• The shape of the returned JSON
• Whether saved filters are selecting the expected submissions

Preview has two important limitations:

• It uses saved filters and aggregates, not unsaved changes currently shown in the form.
• It limits results to 2 rows before calculating total_matches and aggregate.

Because of this, preview totals and aggregation values are not full-dataset totals. Save the query first, then call the full API endpoint to verify final counts and aggregate values.

Saving Rules

Before saving, OSPROV checks that a workflow and at least one data field are selected.

The query name becomes part of the API URL and cannot be changed after saving. Use a stable name that can safely be shared with dashboards, reports, or integration code.