4.1 Querying Relational Data with a!queryEntity

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

1. 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.
2. query (Query, Required): An a!query() configuration object containing the structural definitions for column selection, logical filtering, aggregations, and pagination rules.
3. 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:

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.

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.

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:

1. The primary data retrieval query: SELECT ... FROM table WHERE ... ORDER BY ... LIMIT 25 OFFSET 0
2. 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.

---

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:

1. The query omitted a!querySelection(), loading all 48 columns of the CommercialLoan CDT, including large JSON audit strings and loan history remarks.
2. The query specified fetchTotalCount: true, forcing a full-table COUNT(*) across 650,000 unindexed rows on every tab switch.
3. 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.