Pikbase Docs
Open console
Esc

Type to search.

API reference

query() — Entity Service

Purpose : Searches and retrieves entities based on complex criteria. When to use : Filtering entities by property values Sorting and paginating results…

Contract source@gsb-core/mcp-docs:query

General Description

The query operation fetches entities based on specified query parameters, allowing for complex filtering, sorting, pagination, and analytical queries.

Detailed Description

This operation provides a powerful and flexible way to search and retrieve entities based on various criteria. It supports filtering by property values, sorting results, paginating through large result sets, and including related entities. The query system is designed to handle complex queries while maintaining performance. Additionally, it supports analytical queries with groupBy, aggregates, and modifiers for data analysis.

Input Parameters

Parameter Type Required Description
queryParams object Yes The query parameters object that defines the search criteria and result options.
token string No Authentication token for your request. If not provided, the system will use the default API key from environment variables.
tenantCode string No Tenant code to specify which tenant's data to access. If not provided, the system will extract it from the token or use the default tenant code from environment variables.

QueryParams Object Structure

Property Type Required Description
entDefName string Yes* Name of the entity definition to query. Required if entDefId is not provided. Getter/setter for entityDef.name.
entDefId string Yes* ID of the entity definition to query. Required if entDefName is not provided. Getter/setter for entityDef.id.
entityDef object No Entity definition object with id and/or name properties.
entity object No Optional entity instance for the query.
id string No ID of this query.
entityId string No Property for entity ID.
selectCols array No Array of SelectCol objects defining columns to select in the query. If not provided, all columns are selected.
includes array No Array of IncludeQuery objects for related entities to include in the results.
filters array No Array of Filter objects to filter the results.
startIndex number No Pagination start index (0-based).
count number No Number of records to return.
sortCols array No Array of SortCol objects for sorting specifications.
calcTotalCount boolean No Whether to calculate the total count of matching records.
searchText string No Search term for automatic searching across all searchable fields. Alias of the scalar filter.
filter string No Scalar shorthand for the cross-field auto-search over isSearchable=true fields. Same as searchText. For per-field predicates use filters[] instead.
queryType number No Type of query using QueryType enum (Single=0, List=1, Search=2, AutoComplete=3, Full=4, FullWithSingleRefs=5, FullNonPersonal=6).
disableTransaction boolean No Whether to disable database transaction for this query.
propertyName string No Name of the property being queried when using relationship queries.
propName string No Alias for propertyName.
mapColName string No Alias for propertyName used in mapping.
refColName string No Getter/setter for mapColName.

SelectCol Object Structure

SelectCol is the universal column descriptor. It appears as selectCols[] entries, as sortCols[].col, and as both sides of a filter (col and val). The same object therefore expresses "which database field" and "which value" depending on which fields are set.

The four resolution modes, and the symmetry between them:

Mode Field Resolves to
Field path name a database field, dotted across relations: order.partner.orgUnit.manager
Literal value a hardcoded value
Field script nameScript an expression that evaluates to a database address/expression
Value script valScript an expression that evaluates to values
Subquery valQuery a nested query whose result set supplies the values

Either side may use any mode. A filter's val can carry a name, which makes it a column-to-column comparison rather than a comparison against a literal.

Property Type Description
name string Field path to select or compare. Dot notation traverses relations.
value any Static value.
nameScript string Expression evaluating to a database address/expression. See _CALC token rules below.
valScript string Expression evaluating to values.
valQuery QueryDto Nested query supplying the value set. See Subqueries below.
aggregateFunction number AggregateFunction enum (None=0, Sum=1, Average=2, Count=3, Maximum=4, Minimum=5, Variance=6).
dateModifier number DateModifier enum (None=0, Year=1, Quarter=2, Month=3, DayOfYear=4, DayOfMonth=5, Week=6, Weekday=7, Hour=8, Minute=9, Second=10, Millisecond=11).
groupBy boolean Group by this column in an analytical query.
distinct boolean Deduplicate on this column. Also accepted at the root of queryParams.
dataType number DataType hint for the column.
hideFromResults boolean Compute/join the column but omit it from the response.
dynamicDefinition GsbEntityDef Entity definition supplied inline instead of by name.
skipProcess boolean Skip post-processing for this column.
searchHighlight string Search text to highlight within the returned column value.
selectAsTitle string Alias for the column in the result.
script string JavaScript expression to calculate the column value.
fullName string Full name including table prefixes.
title string Display title for UI purposes.

Subqueries (verified live against dev1, 2026-08-20)

A subquery is a QueryDto on valQuery. The value set it returns becomes the right-hand side of the filter.

{
  "entDefName": "GsbUser",
  "filters": [{
    "col": { "name": "roles.id" },
    "val": {
      "valQuery": {
        "entDefName": "GsbRole",
        "selectCols": [{ "name": "id" }],
        "filters": [{ "col": { "name": "title" }, "val": { "value": "Admin" }, "function": 0 }]
      }
    },
    "function": 27
  }],
  "distinct": true
}
  • MatchArrays (27) treats the subquery result as a set — the row matches if the column is a member. This is the general-purpose form.
  • Equals (0) treats it as a scalar subquery. If the subquery returns more than one row the server fails with SQLSTATE 21000 ("more than one row returned by a subquery used as an expression").
  • The join behind a subquery over a multi-reference is not deduplicated. Set distinct: true or the same parent row repeats once per match.
  • calcTotalCount reports the pre-deduplication row count even when distinct is set. Do not page on totalCount for a deduplicated subquery result.

Aggregate subqueries

A subquery whose selectCols carry an aggregateFunction resolves to a scalar, which is how you compare a row against a value computed from the whole set:

{
  "col": { "name": "size" },
  "val": { "valQuery": {
    "entDefName": "GsbFile",
    "selectCols": [{ "name": "size", "aggregateFunction": 2 }]
  }},
  "function": 2
}

Verified against 349 GsbFile rows: size > AVG(size) returned 35, size < AVG(size) returned 221, and size = MAX(size) returned exactly the one largest row. Use this for "above average", "at the maximum", and any threshold derived from the data.

Correlated subqueries in tenant runtime

Tenant serverless queries can correlate an inner query with the row currently being evaluated by using __PARENT.<field>. The parent token belongs in the inner filter's val.name; val.name on the outer filter identifies the related collection that supplies the subquery rows.

This predicate keeps a cluster when its capacity is greater than the count of tenants assigned to that same cluster:

const hasCapacity: SingleQuery = {
  col: { name: "capacity" },
  val: {
    name: "tenants",
    valQuery: {
      selectCols: [{ name: "id", aggregateFunction: "Count" }],
      queries: [{
        col: { name: "cluster_id" },
        val: { name: "__PARENT.id" },
        function: "Equals",
      }],
    },
  },
  function: "Greater",
};

__PARENT refers to the immediately enclosing query row. It is a trusted query expression for tenant-runtime code, not a literal value to accept from browser input. Correlated aggregate subqueries can be expensive, so constrain the outer query and select only the fields the caller needs.

Column-to-column comparison

Because val is a SelectCol, giving it a name compares two fields:

{
  "col": { "name": "createDate" },
  "val": { "name": "lastUpdateDate" },
  "function": 4
}

Verified: on 349 GsbFile rows, Equals matched 321 and NotEqual matched 28 — a clean partition of the set.

Calculated columns and the _CALC token convention

nameScript uses the same allowlisted-token convention as _CALC in save operations: function and keyword tokens are written as bracketed underscore forms, and [propertyName] references a field.

Script Result
[size]/1024 works — arithmetic on a field
[_COALESCE]([size],0) works — returns the scalar
[_CASE] [_WHEN] [size] > 1000 [_THEN] 1 [_ELSE] 0 [_END] works — bucketing/tiering
COALESCE([size],0) wrong — an unbracketed name is not a function call; parsed as a row constructor returning {f1,f2}
CASE WHEN ... END fails with a SQL syntax error

Unbracketed SQL keywords are not executed, so nameScript is a restricted token allowlist rather than raw SQL. Still never interpolate untrusted text into it.

Filtered includes (verified live against dev1, 2026-08-20)

An IncludeQuery extends QueryParams, so it accepts its own filters. This is the way to narrow a related collection to the rows you actually want, instead of hydrating all of them:

{
  "entDefName": "GsbPrtOrder",
  "selectCols": [{ "name": "id" }],
  "includes": [{
    "name": "invoices",
    "selectCols": [{ "name": "id" }, { "name": "total" }],
    "filters": [{ "col": { "name": "issuer.orgUnit_id" }, "val": { "value": "<org-unit-id>" }, "function": 0 }]
  }]
}

The filter is applied per parent: verified on GsbUser.roles filtered by title Like '%Admin%', three parents returned 0, 0, and 1 related rows while the unfiltered include returned 2, 7, and 2.

Use a filtered include wherever you would reach for "the first related row" — "the open invoice", "the primary address", "the active contract". If the filter identifies exactly one row, you get exactly one row per parent.

Include count is not a limit. count, take, startIndex+count, queryType, and count+sortCols were all ignored on both a metadata relation and a real many-to-many. Narrow with filters, and select only the columns you need — a wide include on a large relation is an unbounded payload.

Nested filter logic (verified live against dev1, 2026-08-20)

Filters compose into an unlimited AND/OR tree. Two rules cover the whole system.

Rule 1 — relation describes how a filter joins to the filter before it. It is carried by the right-hand operand and defaults to "and". Putting it on the left operand does nothing.

[ A, { ...B, "relation": "or" } ]        // A OR B
[ { ...A, "relation": "or" }, B ]        // A AND B — the "or" is ignored

Rule 2 — children is a parenthesis. A filter carrying children is a group. Its own relation says how the group joins to its preceding sibling — not how its children combine. Children follow Rule 1 among themselves.

// (listingType = 2 AND size > 1000) OR listingType = 1
"filters": [
  { "children": [
      { "col": { "name": "listingType" }, "val": { "value": 2 },    "function": 0 },
      { "col": { "name": "size" },        "val": { "value": 1000 }, "function": 2 }
  ]},
  { "col": { "name": "listingType" }, "val": { "value": 1 }, "function": 0,
    "relation": "or" }
]

Verified on 349 GsbFile rows — folders (A) = 62, files (B) = 284, files over 1000 bytes = 92:

Expression Shape Total
A OR B [A, or(B)] 346 = 62 + 284
A AND B [A, B] 0
(B AND big) OR A [{children:[B,big]}, or(A)] 154 = 92 + 62
(A OR B) AND big [{children:[A,or(B)]}, big] 92
((B AND big) OR A) AND name IS NOT NULL three levels 154

Nesting depth is unbounded — a group may contain groups.

negate applies to a single filter, not to a group. On a leaf it inverted 62 rows to 287 as expected; on a children wrapper it was ignored (346 instead of 3). To negate a group, invert the operators inside it.

Most common mistake: putting relation: "or" on the wrapper and expecting the children to OR together. They stay ANDed, which usually returns zero rows.

SortCol Object Structure

Property Type Description
col SelectCol The column to sort by.
sortType string Sort direction: "asc" for ascending, "desc" for descending.

Filter Object Structure

Property Type Required Description
col SelectCol Yes The column to filter on.
val SelectCol Yes The value to compare against as a SelectCol object.
function number string No
relation string No How this filter joins to the preceding filter: "and" (default) or "or". See Nested filter logic.
children array No Nested filter conditions. A filter carrying children acts as a parenthesised group.
negate boolean No Invert this condition (default: false). Applies to a single filter only, not to a children group.
extra object No Extra operands for functions that need more than one value, e.g. { value1, value2 } for Between (19).
name string No Optional name for the filter.
relationLevel number No Level of relation nesting.

IncludeQuery Object Structure

IncludeQuery extends QueryParams and represents related entities to include:

Property Type Description
name string Name of the relationship property to include.
propertyName string Alias for name.
(all QueryParams properties) various All properties from QueryParams are available for nested queries.

Fluent TypeScript API

The TypeScript client uses QueryParams as the canonical fluent builder. Its methods serialize to the object structure above; method names are not sent over the wire.

import {
  IncludeQuery,
  QueryFunction,
  QueryParams,
  QuerySortType,
  QueryType,
} from "@gsb-core/core";

const queryParams = new QueryParams<Order>("Order")
  .type(QueryType.FullNonPersonal)
  .filter("status", "open", QueryFunction.Equals)
  .filter("total", 100, QueryFunction.GreaterOrEqual)
  .include(
    new IncludeQuery<Customer>("customer").select(["id", "name"]),
  )
  .self.sortBy("createDate", QuerySortType.Descending)
  .skip(20)
  .take(10)
  .select(["id", "status", "total", "createDate"])
  .returnCount();

const result = await entityService.query(queryParams);

The current fluent methods are:

Method Serialized property Purpose
type() queryType Set the query hydration mode.
filter() filters Add a filter using a property name, value, function, and relation.
search() searchText Apply configured full-text search.
select() selectCols Select only required properties.
include() includes Include related entities; use .inc for the latest include and .self to return to the root query.
sortBy() sortCols Sort ascending or descending.
skip() startIndex Set the zero-based offset.
take() / limit() count Limit the number of returned entities.
returnCount() calcTotalCount Request the total matching count.
pickEntity() entityId Target an entity for mapped or relationship operations.

CLI JSON

CLI and MCP calls use the serialized form of the same model:

gsb call query --input '{
  "queryParams": {
    "entDefName": "Order",
    "filters": [{
      "col": { "name": "status" },
      "val": { "value": "open" },
      "function": 0
    }],
    "selectCols": [{ "name": "id" }, { "name": "status" }],
    "sortCols": [{ "col": { "name": "createDate" }, "sortType": "desc" }],
    "startIndex": 0,
    "count": 25,
    "calcTotalCount": true
  }
}' --raw

The CLI also reads the legacy GSB code-library shape (query, propVal, and colName) and normalizes it to filters, col/val, and name. New code should always use the current form shown above.

Server caveats (verified live against dev1, 2026-08-20)

These are server behaviors, not client bugs — plan around them.

Predicate delivery

  • filters[] is honored on its own. Each entry may use either the col/val shape or the legacy propVal shape, and both filter identically — verified deterministic over 25 consecutive runs each, on a scalar column (349 rows narrowed to 62) and on a reference path (roles.id, 9 narrowed to 1).
  • If a request carries both filters and a legacy query array, the query array wins. That is safe when the two agree, but a hand-built mirror that drops fields — aggregateFunction, valQuery, extra — silently changes the meaning of the query. Send filters alone unless you have a specific reason not to.
  • toQueryParams() throws when a query has no entity target or a filter/sort entry is malformed — a silently dropped predicate would return a broader result set than intended.

Broken filter functions

  • function: 19 (Between) needs its bounds in extra, not val. Put extra: { value1, value2 } on the filter object; val stays empty. Passing an array in val.value, or putting extra on col/val, fails with HTTP 500. Verified: size between 0 and 1000 returned 164 rows, identical to GreaterOrEqual + SmallerOrEqual. Works on dates too.

    { "col": { "name": "size" }, "val": {}, "function": 19,
  "extra": { "value1": 0, "value2": 1000 } }

  • function: 20 (PhraseSearch) fails with HTTP 500 — 42883: function phraseto_tsquery(unknown, ...) does not exist server-side. Use FullTextSearch (11) or Like.

  • In (8) on a bare multi-reference property name fails with HTTP 500. Use the dotted id path (roles.id). On a scalar text column In works with a plain string array — verified: title In ["Validate Subscription SAAS", "Workspace Create"] returned exactly the 2 matching GsbWfFunction rows. The dotted .id path is required only for reference fields.

Semantics

  • Contains (12) is array/multi-reference membership, not substring. On text it returns 0 rows where Like matches — that is correct behavior, not a bug. Use Like (1) with % wildcards for text search.
  • Reference fields: Contains (12), In (8), and Equals (0) all match a given id-array against related ids. Contains targets the multi-ref property name (roles); In/Equals need the dotted id path (roles.id).
  • includes[] requires the include property to resolve on the target definition or the whole query fails with HTTP 400.
  • includes[].count is not a limit. A requested limit of 2 returned 9, 37, and 11 related rows on a metadata relation, and 2, 7, 2 on a real many-to-many. take, startIndex+count, queryType, and count+sortCols behave the same. Narrow related collections with includes[].filters instead — see Filtered includes.
  • Grouped aggregation emits a stray null-key row. A sum grouped by listingType returned 3 rows, one with no group key. Filter null-key rows before presenting aggregate results.
  • searchText is an alias of the scalar filter. Both run the cross-field auto-search over the entity's searchable fields (isSearchable=true) — no filters[] needed: { entDefName: "GsbEntityDef", filter: "workflow" } narrowed 189 rows to 5, matching on both name (GsbWfLog) and title (Workflow Log). Use FullTextSearch (11) on a specific field when you need full-text semantics (ranking/headline) rather than a LIKE sweep.
  • searchHighlight does not guarantee markup. On GsbWfFunction.code it returned a truncated raw-text prefix with no <b> tags (and undefined on non-matching rows), not the <b>Data</b> form shown under Full text search below. Treat highlight as best-effort snippet text; only a proper fullTextIndex + headline path emits markup.

Patterns for harder shapes

These need more than one call or a different formulation. Each has a working route.

  • Top-N per group. Parent correlation supports related filters and aggregates, but it does not provide a per-parent ranking/window operation, and include limits are ignored. Route: if the N rows are identifiable by a predicate, use a filtered include (above) — this covers most real cases. For a true ranked top-N, select the parents and issue one child query per parent, or select the relation and slice in the caller. Global top-N is a single query (sortCols + count), and the single extreme row is an aggregate subquery.
  • Heterogeneous union across unrelated definitions. unions, queries, an entityDef array, and a root dynamicDefinition are all rejected or ignored. GsbEntityDef.parent_id is organizational grouping, not subtype inheritance — querying a parent definition returns that definition's own rows, so it is not a union mechanism. Route: model the feed explicitly. A single definition holding the activity records, written by the workflows that create orders/invoices/payments, turns the feed into one ordinary query with correct paging and totalCount. Merging N queries in the caller works for small result sets but makes paging and totalCount unreliable.

Response

Success Response

{
    "success": true,
    "entities": [
        // Array of matching entities
        {
            "id": "string",
            "property1": "value1",
            "property2": "value2",
            // ...
        }
    ],
    "totalCount": 42,  // Present only if calcTotalCount is true
    "message": "string",  // Optional message
    "status": 200  // Optional status code
}

Query Functions (QueryFunction Enum)

The query system supports various functions for filtering entities:

Function Enum Value Description Example
Equals 0 Exact value match { col: { name: "status" }, val: { value: "active" }, function: 0 }
Like 1 Pattern matching { col: { name: "name" }, val: { value: "John%" }, function: 1 }
Greater 2 Greater than comparison { col: { name: "price" }, val: { value: 100 }, function: 2 }
Smaller 3 Smaller than comparison { col: { name: "quantity" }, val: { value: 50 }, function: 3 }
NotEqual 4 Not equal comparison { col: { name: "status" }, val: { value: "inactive" }, function: 4 }
BitwiseAnd 5 Bitwise AND operation { col: { name: "flags" }, val: { value: 8 }, function: 5 }
BitwiseOr 6 Bitwise OR operation { col: { name: "flags" }, val: { value: 4 }, function: 6 }
BitwiseXor 7 Bitwise XOR operation { col: { name: "flags" }, val: { value: 2 }, function: 7 }
In 8 Check if value is in a set. On a reference field use the dotted id path (ref.id); on the bare multi-ref property name it fails server-side. { col: { name: "roles.id" }, val: { value: ["role-id"] }, function: 8 }
Is 9 Type checking (null/not null) { col: { name: "createDate" }, val: { value: null }, function: 9 }
IsNot 10 Type checking negation { col: { name: "createDate" }, val: { value: null }, function: 10 }
FullTextSearch 11 Full text search { col: { name: "description" }, val: { value: "search terms" }, function: 11 }
Contains 12 Array / multi-reference membership: matches rows whose related-id set contains the given id(s). Target the multi-ref property name directly. NOT a substring operator — use Like for text. { col: { name: "roles" }, val: { value: ["role-id"] }, function: 12 }
GreaterOrEqual 13 Greater than or equal comparison { col: { name: "price" }, val: { value: 100 }, function: 13 }
SmallerOrEqual 14 Smaller than or equal comparison { col: { name: "quantity" }, val: { value: 50 }, function: 14 }
ILike 15 Case-insensitive pattern matching { col: { name: "name" }, val: { value: "john%" }, function: 15 }
RegexMatch 16 Regular expression matching { col: { name: "email" }, val: { value: ".*@domain\.com" }, function: 16 }
RegexMatchCaseInsensitive 17 Case-insensitive regex matching { col: { name: "email" }, val: { value: ".*@DOMAIN\.COM" }, function: 17 }
IsNull 18 Check if value is null { col: { name: "deletedAt" }, val: { value: null }, function: 18 }
Between 19 Range test. Bounds go in extra: { value1, value2 } on the filter, not in val. { col: { name: "price" }, val: {}, function: 19, extra: { value1: 100, value2: 500 } }
PhraseSearch 20 Phrase-based text search { col: { name: "content" }, val: { value: "exact phrase" }, function: 20 }
GeometryOverlaps 21 Geometry overlap check { col: { name: "area" }, val: { value: geometryObject }, function: 21 }
PointInGeometry 22 Point within geometry check { col: { name: "location" }, val: { value: pointObject }, function: 22 }
GPSDistance 23 GPS distance calculation { col: { name: "coordinates" }, val: { value: [lat, lng, distance] }, function: 23 }
GPSWithinRadius 24 GPS within radius check { col: { name: "coordinates" }, val: { value: [lat, lng, radius] }, function: 24 }
JsonContains 25 JSON containment check { col: { name: "metadata" }, val: { value: {"key": "value"} }, function: 25 }
JsonHasKey 26 JSON key existence check { col: { name: "metadata" }, val: { value: "keyName" }, function: 26 }
MatchArrays 27 Set membership against the value-set returned by a subquery on val.valQuery. Row matches if the column is in the set. For a literal array use Contains (12). Set distinct: true — the join is not deduplicated. { col: { name: "roles.id" }, val: { valQuery: { entDefName: "GsbRole", selectCols: [{ name: "id" }] } }, function: 27 }

Aggregate Functions (AggregateFunction Enum)

The query system supports the following aggregate functions for analytical queries:

Function Enum Value Description Example
None 0 No aggregation { name: "id", aggregateFunction: 0 }
Sum 1 Sum of values { name: "amount", aggregateFunction: 1, selectAsTitle: "total_amount" }
Average 2 Average of values { name: "price", aggregateFunction: 2, selectAsTitle: "average_price" }
Count 3 Count of records { name: "id", aggregateFunction: 3, selectAsTitle: "total_records" }
Maximum 4 Maximum value { name: "price", aggregateFunction: 4, selectAsTitle: "highest_price" }
Minimum 5 Minimum value { name: "price", aggregateFunction: 5, selectAsTitle: "lowest_price" }
Variance 6 Variance of values { name: "score", aggregateFunction: 6, selectAsTitle: "score_variance" }

Date Modifiers (DateModifier Enum)

For time-based grouping and analysis:

Modifier Enum Value Description
None 0 No date modification
Year 1 Group by year
Quarter 2 Group by quarter
Month 3 Group by month
DayOfYear 4 Group by day of year
DayOfMonth 5 Group by day of month
Week 6 Group by week
Weekday 7 Group by weekday
Hour 8 Group by hour
Minute 9 Group by minute
Second 10 Group by second
Millisecond 11 Group by millisecond

Query Types (QueryType Enum)

Type Enum Value Description
Single 0 Single entity query
List 1 List of entities
Search 2 Search query
AutoComplete 3 Autocomplete query
Full 4 Full entity data
FullWithSingleRefs 5 Full data with single references
FullNonPersonal 6 Full data excluding createDate,lastUpdateDate,createdBy,lastUpdatedBy

Example Usage

Basic Query

const result = await query({
  queryParams: {
    entDefName: "Customer",
    filters: [
      {
        col: { name: "status" },
        val: { value: "active" },
        function: 0 // QueryFunction.Equals
      }
    ]
  }
});

if (result.success) {
  const customers = result.entities;
  console.log(`Found ${customers.length} active customers`);
}

Query with Pagination and Sorting

const result = await query({
  queryParams: {
    entDefName: "Order",
    startIndex: 0,
    count: 10,
    sortCols: [
      {
        col: {
          name: "orderDate"
        },
        sortType: "desc"
      }
    ],
    calcTotalCount: true
  }
});

if (result.success) {
  const orders = result.entities;
  const totalOrders = result.totalCount;
  console.log(`Showing ${orders.length} of ${totalOrders} total orders`);
}

Complex Query with Multiple Conditions

relation goes on the second operand — see Nested filter logic.

// (price > 500 OR inStock = true) AND category = "tools"
const result = await query({
  queryParams: {
    entDefName: "Product",
    filters: [
      {
        children: [
          {
            col: { name: "price" },
            val: { value: 500 },
            function: 2 // QueryFunction.Greater
          },
          {
            col: { name: "inStock" },
            val: { value: true },
            function: 0, // QueryFunction.Equals
            relation: "or" // joins to the filter above it
          }
        ]
      },
      {
        col: { name: "category" },
        val: { value: "tools" },
        function: 0
      }
    ],
    sortCols: [
      {
        col: { name: "price" },
        sortType: "asc"
      }
    ]
  }
});

Putting relation: "or" on the children wrapper instead would AND the two conditions together and usually return nothing.

Using SelectCol with Scripts

const result = await query({
  queryParams: {
    entDefName: "Order",
    selectCols: [
      {
        name: "totalPrice",
        selectAsTitle: "total",
        nameScript: "([totalPrice]+[shipping])*[vatRate] - [discount]"
      }
    ],
    filters: [
      {
        col: { name: "orderDate" },
        val: { value: "2023-01-01" },
        function: 2 // QueryFunction.Greater
      }
    ]
  }
});

if (result.success) {
  console.log(`Found ${result.entities.length} orders`);
}
const result = await query({
  queryParams: {
    entDefName: "Product",
    searchText: "smartphone", // Will search across all searchable fields
    startIndex: 0,
    count: 20
  }
});

if (result.success) {
  const products = result.entities;
  console.log(`Found ${products.length} products matching 'smartphone'`);
}
const result = await query({
  queryParams: {
    entDefName: "Order",
    includes: [
      {
        propertyName: "customer"
      },
      {
        propertyName: "items",
        includes: [
          {
            propertyName: "product"
          }
        ]
      }
    ]
  }
});

Additional Information

  • For complex queries, the filter conditions can be nested using the children property.
  • relation ("and" / "or") on a filter describes how it joins to the preceding filter, and defaults to "and". A children group's own relation joins the group to its preceding sibling; it does not combine the children. See "Nested filter logic".
  • The negate property inverts a single condition (NOT). It does not invert a children group.
  • When using includes, you can nest includes to fetch deeply related entities using IncludeQuery objects.
  • For better performance with large result sets, use pagination with startIndex and count.
  • The calcTotalCount option adds overhead to the query, so only use it when needed.
  • For direct access to a single entity by ID, use the getById operation instead.
  • The searchText parameter provides a simple way to search across all searchable fields.
  • For more complex search requirements, use explicit filter conditions.
  • The response contains entities directly in the entities field, not in a data field.

SelectCol Usage

  • The SelectCol object provides extensive configuration options for column selection.
  • Use aggregateFunction with AggregateFunction enum values for analytical queries.
  • Use dateModifier with DateModifier enum values for time-based grouping.
  • The selectAsTitle property allows you to alias column names in results.
  • Scripts (nameScript, valScript) can contain JavaScript expressions for dynamic calculations.
  • The groupBy property is essential for analytical queries with aggregations.

Filter Usage

  • The Filter object uses SelectCol for both col and val properties.
  • val.value holds a literal; val.name makes it a column-to-column comparison, and val.valQuery makes it a subquery.
  • Use QueryFunction enum values for the function property.
  • relation ("and" / "or") joins a filter to the one before it; children groups filters like parentheses. See "Nested filter logic".
  • Functions needing two operands, such as Between, take them in extra: { value1, value2 } on the filter.

Full text search

  • to be able to use full text search, the property should be marked as fullTextIndex=true
  • you can use FullTextSearch function (QueryFunction.FullTextSearch = 11) in filters to search for a string in the property, or simply set searchText in the main query.
  • example queryParams:
     const queryParams = {
 entDefName: "HelpPage",
 searchText: "data t",

//if any help page has a title like "data table" or the en_us column of its associated content has "data table" will be selected. selectCols: [ { //will apply logical search on title property: title like '%data t%' "name": "title" }, { //will apply full text search on related content.en_us property (it's already marked as fullTextIndex=true) "selectAsTitle": "highlight", // alias name for the result column "searchHighlight": "data t", // search text to be highlighted, if not provided will return all content instead of highlight. "name": "content.en_us" // property name to be searched, we can use dot notation to search in nested properties. } ] }


response, data is highlighted with html tags. en_us column name is set to highlight because we set the selectAsTitle to highlight.

```json
{
    "entities": [
        {
            "title": "Data Table",
            "content": {
                "highlight": "<b>Data</b> Table  

 The <b>data</b> table may display varying features across different sections or devices. It is designed"
            }
        }
    ]
}

Analytical Queries

  • Use groupBy property in SelectCol objects to aggregate data by specific fields.
  • Aggregates define calculations to perform on grouped data using AggregateFunction enum:
    • Count (3): Count the number of records in each group
    • Sum (1): Calculate the total of a numeric field
    • Average (2): Calculate the average of a numeric field
    • Minimum (5): Find the minimum value
    • Maximum (4): Find the maximum value
    • Variance (6): Calculate variance of values
  • The selectAsTitle property in SelectCol defines the field name in the result.
  • If it's an analytical query, set groupBy or aggregateFunction to all selectCols, filters, includes and sorts.
  • A filter whose col carries an aggregateFunction becomes a HAVING clause; a filter whose col carries only groupBy stays a row-level WHERE. See "Filtering grouped results" above.
  • Analytical queries return aggregated data instead of complete entities.
  • For time-series analysis you can use dateModifier with DateModifier enum values.
  • Please refer to documentation for advanced usage. You can use sub queries, calculated fields, scripts, etc.

example, get users who have a role with id 699b313c-cf1c-40c1-b86e-ab6e9a53f4f2, and group by their group and createDate based on addedYear. *note: all columns in the selectCols and sorts are either group by or aggregate function.

{
  "entityDef": {
    "name": "GsbUser"
  },
  "filters": [
    {
      "children": [
        {
          "col": {
            "name": "roles"
          },
          "val": {
            "value": [
              {
                "id": "699b313c-cf1c-40c1-b86e-ab6e9a53f4f2"
              }
            ]
          },
          "function": 12 // QueryFunction.Contains for many-to-many relation
        }
      ]
    }
  ],
  "selectCols": [
    //group by groups, system automatically understands it's a many-to-many relation and does all the work.
    {
      "name": "groups",
      "groupBy": true
    },
    //count the number of groups, we can use title or any field just to count
    {
      "name": "title",
      "aggregateFunction": 3, // AggregateFunction.Count
      "selectAsTitle": "count"
    },
    //group by createDate based on addedYear
    {
      "name": "createDate",
      "dateModifier": 1, // DateModifier.Year
      "groupBy": true,
      "selectAsTitle": "addedYear"
    }
  ],
  "sortCols": [
    //sort by createDate based on addedYear
    {
      "col": {
        "name": "createDate",
        "groupBy": true,
        "dateModifier": 1 // DateModifier.Year
      },
      "sortType": "desc"
    }
  ]
}