4.1 Querying Relational Data with a!queryEntity

Appian Certified Associate Developer ACD101

Track your progress as you read

Finished sections get a check mark.

1 Chapter 1: Platform Architecture & Environments

1.1 Appian Platform Architecture & Core Capabilities 1.2 Appian Designer, Applications & Object Containers 1.3 SDLC Environments, Deployment & Community Resources

2 Chapter 2: Security & Platform Governance

2.1 Group Hierarchies & Role-Based Access Control 2.2 Design Object Security & Dependency Tracking 2.3 Appian Design System & UX Governance

3 Chapter 3: Relational Data Modeling & CDTs

3.1 Custom Data Types (CDTs) & Schema Design 3.2 Data Stores, Entity Mapping & Table Publication

4 Chapter 4: Data Persistence Operations & Query Entity

4.1 Querying Relational Data with a!queryEntity 4.2 Data Modification & the Write to Data Store Entity Smart Service

5 Chapter 5: Records Architecture & Data Fabric

5.1 Record Types Architecture & Synced Data Sources 5.2 Record Relationships & Custom Record Fields

6 Chapter 6: Record Views, Actions & Record Queries

6.1 Record List Configuration, Headers & Views 6.2 Record Actions & Querying Records with a!queryRecordType

7 Chapter 7: Process Model Architecture & Task Design

7.1 Process Model Canvas, Lanes & Process Variables 7.2 User Input Tasks, Activity Chaining & Assignment 7.3 Script Tasks & System Smart Services

8 Chapter 8: Process Orchestration, Sub-Processes & Exception Handling

8.1 Workflow Gateways & Branching Logic 8.2 Sub-Process Design: Synchronous vs. Asynchronous 8.3 Process Escalations, Deadlines & Exception Events

9 Chapter 9: SAIL Interface Design & State Management

9.1 SAIL Framework & Component Hierarchy 9.2 Local Variables & Dynamic Refresh Behaviors 9.3 Rule Inputs, Component Parameters & Two-Way Binding

10 Chapter 10: Advanced Interface Components & Sites

10.1 Read-Only Grids and Editable Grid Layouts 10.2 Form Validations, Error Handling & Dynamic Display 10.3 Appian Sites, Navigation & Portal Configuration

11 Chapter 11: Expression Rules & Functional Data Manipulation

11.1 Expression Rule Architecture & Function Namespaces 11.2 List Processing & Looping with a!forEach 11.3 Conditional Logic & Decision Objects

12 Chapter 12: Query Optimization, Testing & Debugging

12.1 Expression Rule Testing, Assertions & Test Cases 12.2 Query Construction & Performance Best Practices 12.3 Error Handling, Type Coercion & Expression Debugging

4.1 Querying Relational Data with a!queryEntity

Key Takeaways Explain This Topic AI

a!queryEntity() executes type-safe SQL queries against relational database tables mapped through Data Store Entities, abstracting underlying JDBC connections and credentials.

Setting fetchTotalCount to false prevents an expensive secondary database COUNT(*) query, which is a critical performance optimization for high-volume enterprise tables.

Using a!querySelection() with a!queryColumn() projects only required attributes, minimizing database I/O, network transmission latency, and application server memory consumption.

a!queryFilter provides individual conditions, and a!queryLogicalExpression combines filters and nested logical expressions.

a!queryAggregation() delegates grouping and SQL aggregate calculations (COUNT, SUM, AVG, MIN, MAX) to the database engine rather than processing large datasets in Appian memory.

Last updated: October 2026

• 4.1 Querying Relational Data with a!queryEntity
• The Architecture and Anatomy of a!queryEntity

In the Appian platform, enterprise applications interact with relational database tables using the a!queryEntity() expression function. Rather than writing raw SQL statements inside application code, developers execute type-safe queries against Data Store Entities (DSEs) . This architectural abstraction ensures that database connection credentials, dialect idiosyncrasies, and connection pools remain securely managed at the infrastructure layer, while developers work with structured Appian data models.

• The function signature for a!queryEntity() comprises three core parameters: a!queryEntity(
• entity: cons!ORDER_ENTITY,
• query: a!query(
• selection: a!querySelection(
• columns: {
• a!queryColumn(field: "orderId"),
• a!queryColumn(field: "orderNumber"),
• a!queryColumn(field: "orderTotal")
• }
• ),
• filter: a!queryFilter(
• field: "orderStatus",
• operator: "=",
• value: "PENDING"
• ),
• pagingInfo: a!pagingInfo(
• startIndex: 1,
• batchSize: 25,
• sort: a!sortInfo(
• field: "orderDate",
• ascending: false
• )
• )
• ),
• fetchTotalCount: false
• )
• Core Parameters Explained

entity (DataStoreEntity, Required) : A pointer to the target Data Store Entity. In standard enterprise architecture, developers reference entities through dedicated constants (e.g., cons!CUSTOMER_DSE or cons!INVOICE_ENTITY ) rather than hardcoding entity references.

query (Query, Required) : An a!query() configuration object containing the structural definitions for column selection, logical filtering, aggregations, and pagination rules.

fetchTotalCount (Boolean, Optional) : Determines whether Appian retrieves the total number of records matching the filter criteria across the entire database table. Defaults to false in modern versions of Appian.

The Return Structure: Understanding the DataSubset Object

When a!queryEntity() evaluates, it returns an Appian system object known as a DataSubset . Understanding the fields of this dictionary structure is essential for displaying data in read-only grids and extracting field values:

DataSubset Property Data Type Description & Usage

.data List of Variant / CDT The actual records retrieved from the database table. If no projection is configured, it returns an array of the underlying CDT. If a!querySelection() or a!queryAggregation() is used, it returns a list of dictionaries with field aliases.

.totalCount Integer The total number of rows matching the query filters in the database. When fetchTotalCount: true , this contains the exact row count. When fetchTotalCount: false , it returns -1 for a finite batch when the count is not requested. With batchSize -1, the full result size is already known.

.startIndex Integer The 1-based index of the first record returned in the current batch.

.batchSize Integer The number of rows requested in the a!pagingInfo() configuration.

.sort List of SortInfo The sort fields and direction instructions applied to the database query.

.identifiers List of Variant The primary key values corresponding to each returned row in .data .

To access queried values in an interface or expression rule, developers chain directly into the .data property, such as local!orderDataSubset.data.orderNumber .

Column Projection: a!querySelection() vs. Entire Entity Schemas

By default, if the selection parameter is omitted from a!query() , Appian executes a SELECT * query against the database table, mapping all relational columns back into the complete Custom Data Type (CDT) schema. In production systems with wide tables (e.g., 30 to 60 columns or fields containing large text and notes), retrieving entire schemas introduces severe architectural bottlenecks:

Database I/O Strain : The database engine must read broad disk blocks and transfer unneeded columns across the network.

Appian Memory Bloat : Retrieved values use application memory; values also saved into process state add to the process footprint. When multiple concurrent users view dashboards, loading full CDTs can lead to high memory consumption and garbage collection pauses.

Best Practice: Explicit Projections with a!querySelection()

Developers should explicitly project only the fields required for the immediate interface or business rule using a!querySelection() and a!queryColumn() : selection: a!querySelection(

• columns: {
• a!queryColumn(field: "customerId"),
• a!queryColumn(field: "companyName"),
• a!queryColumn(field: "accountManagerEmail")
• }
• )

When a projection is applied, Appian issues a targeted SQL query (e.g., SELECT customer_id, company_name, account_manager_email FROM ... ). The resulting .data property contains lightweight dictionary objects containing only the selected keys, reducing unneeded data transfer and memory use; measure the actual benefit for the selected fields.

Filtering Relational Data: a!queryFilter() and a!queryLogicalExpression()

Appian provides two distinct helper functions for filtering database records: a!queryFilter() for individual conditional statements, and a!queryLogicalExpression() for combining multiple conditions with boolean logic.

Anatomy of a!queryFilter()

An individual filter requires four primary parameters:

field (Text) : The exact CDT field name (case-sensitive) mapped to the relational column.

operator (Text) : The comparison operator, such as "=" , "<>" , "<" , ">" , "<=" , ">=" , "between" , "in" , "not in" , "starts with" , "ends with" , "includes" , "is null" , or "is not null" .

value (Any Type) : The target value to match. For multi-value operators like "in" or "between" , this must be an array.

applyWhen (Boolean) : A conditional toggle that determines whether the filter is executed. When applyWhen evaluates to false , Appian completely omits the filter from the generated SQL query.

Tip

Always use applyWhen: a!isNotNullOrEmpty(ri!searchTerm) on user-facing search fields. This eliminates the need for complex if() branching when configuring dynamic search queries and prevents invalid SQL queries when search inputs are blank.

Combining Filters with a!queryLogicalExpression()

When queries require multiple conditions, developers use a!queryLogicalExpression() to define boolean grouping: logicalExpression: a!queryLogicalExpression(

• operator: "AND",
• filters: a!queryFilter(
• field: "department",
• operator: "=",
• value: "Engineering"
• ),
• logicalExpressions: a!queryLogicalExpression(
• operator: "OR",
• filters: {
• a!queryFilter(field: "employmentStatus", operator: "=", value: "ACTIVE"),
• a!queryFilter(field: "onboardingStage", operator: "=", value: "PROBATION")
• }
• )
• )

This translates directly into the SQL WHERE clause: WHERE (department = 'Engineering' AND (employment_status = 'ACTIVE' OR onboarding_stage = 'PROBATION')) .

Pagination and Sorting: a!pagingInfo() and a!sortInfo()

Every call to a!queryEntity() requires an a!pagingInfo() configuration to instruct the database how many rows to retrieve, where to begin reading, and how to sort results.

Parameter Rules & Constraints Operational Behavior

startIndex Must be an integer ≥ 1. Appian pagination is 1-indexed . Specifying 0 or a negative number results in an immediate expression evaluation error.

batchSize Integer (e.g., 10 , 25 , 50 , -1 , 0 ). Defines the maximum number of rows returned. Setting batchSize: -1 requests all matching records. Setting batchSize: 0 returns zero data rows but returns totalCount if fetchTotalCount: true .

sort List of a!sortInfo() . Defines multi-column ordering. Each sort item specifies a field (Text) and ascending (Boolean). When ascending: true , data sorts from low-to-high or A-to-Z.

Bound the Result Set

Use a positive, bounded batch size for ordinary interface queries. QueryEntity can request all results using batchSize -1, but memory and timeout limits still apply. QueryRecordType has a different contract and does not accept -1. Do not transfer paging assumptions between the two APIs.

The fetchTotalCount Parameter: Performance Trade-offs

When a finite result batch is requested with fetchTotalCount: true , Appian may need an additional count query against the database engine:

The primary data retrieval query: SELECT ... FROM table WHERE ... ORDER BY ... LIMIT 25 OFFSET 0

An exhaustive counting query: SELECT COUNT(*) FROM table WHERE ...

On large relational tables containing hundreds of thousands or millions of records, SELECT COUNT(*) requires the database engine to scan table partitions or index trees, which often takes significantly longer than fetching the 25 specific rows.

Architectural Guidance

Set fetchTotalCount: false when populating dropdown components, card lists, feeding downstream batch processing, or when using modern "Load More" infinite-scrolling interface designs.

Set fetchTotalCount: true only when displaying a classic read-only grid where users must see total page numbers (e.g., "Showing page 1 of 42").

Aggregating Data: a!queryAggregation() and Grouping

When an interface requires summary statistics—such as total revenue by region or headcount by department—developers should never retrieve raw records into Appian memory to compute sums with sum() or count() . Instead, a!queryAggregation() instructs the database engine to perform SQL grouping and calculation natively.

Inside a!queryAggregation() , developers supply an array of a!queryAggregationColumn() objects configured with:

isGrouping: true : Specifies that the column forms part of the SQL GROUP BY clause.

aggregationFunction : Specifies the SQL aggregate operation: "COUNT" , "SUM" , "AVG" , "MIN" , or "MAX" .

• alias (Required) : Defines the property name in the returned dictionary for the computed value. a!queryEntity(
• entity: cons!EMPLOYEE_ENTITY,
• query: a!query(
• aggregation: a!queryAggregation(
• columns: {
• a!queryAggregationColumn(
• field: "department",
• isGrouping: true
• ),
• a!queryAggregationColumn(
• field: "employeeId",
• aggregationFunction: "COUNT",
• alias: "totalHeadcount"
• ),
• a!queryAggregationColumn(
• field: "salary",
• aggregationFunction: "AVG",
• alias: "averageSalary"
• )
• }
• ),
• pagingInfo: a!pagingInfo(startIndex: 1, batchSize: -1)
• ),
• fetchTotalCount: false
• )

This generates: SELECT department, COUNT(employee_id) AS totalHeadcount, AVG(salary) AS averageSalary FROM employee GROUP BY department . The returned .data property contains dictionaries with the keys department , totalHeadcount , and averageSalary .

Warning

In an a!query() definition, selection and aggregation are mutually exclusive . You cannot specify both selection: a!querySelection(...) and aggregation: a!queryAggregation(...) within the same query call.

Real-World Scenario: Financial Risk Dashboard Latency

A financial institution built a risk analysis dashboard to display high-risk commercial loans. During initial development with 100 sample records, the dashboard loaded instantly. However, after migrating to user acceptance testing (UAT) with 650,000 real-world loan records, loading the dashboard took over 9.5 seconds, creating unacceptable latency for the example application.

Upon inspecting the expression rule, the lead developer identified three critical anti-patterns:

The query omitted a!querySelection() , loading all 48 columns of the CommercialLoan CDT, including large JSON audit strings and loan history remarks.

The query specified fetchTotalCount: true , forcing a full-table COUNT(*) across 650,000 unindexed rows on every tab switch.

The paging configuration used batchSize: -1 , loading thousands of rows into Appian application memory.

The developer refactored the query to use a!querySelection() with only 5 required summary columns, set fetchTotalCount: false , and capped batchSize at 20 rows with a dedicated sort index. Compare before-and-after query timings and result correctness using the same representative dataset; this example does not establish a measured latency.

Exam Watchouts & Common Traps

1-Based Indexing : startIndex must be 1 or greater. Providing 0 is a classic exam trick and causes an evaluation failure.

Different Query Contracts : Use bounded QueryEntity batches, and remember that QueryRecordType does not accept batchSize -1.

Field Name Casing : The field parameter in a!queryColumn() , a!queryFilter() , and a!sortInfo() must match the CDT field name (e.g., "createdDateTime" ), NOT the physical database column name (e.g., "CREATED_DATE_TIME" ).

Aggregations Require Aliases : Any a!queryAggregationColumn() using an aggregationFunction must have a defined alias . Omitting the alias results in an evaluation error because Appian cannot name the returned dictionary key.

Mutual Exclusivity : An a!query() call cannot contain both a selection and an aggregation . Choose selection for row-level projection and aggregation for grouped metrics.

Source checked 2026-10-18: Appian queryEntity .