Sorting and limiting control the order and size of a semantic query’s result set. Add sort orders to arrange result rows by a field, an alias, or an aggregated measure, and add a limit to cap the number of rows returned. Both are set per query in the options block of structuredSemanticQuery.
Sorting is expressed as a list of sort orders (sort_orders), applied in the order you list them: the first sort order is the primary key, each subsequent one breaks ties. Two sort-order shapes are available — a simple_sort_order that orders by a single field or alias, and an aggregative_sort_order that orders a grouped dimension by an aggregated measure. You can mix both in one request; a common pattern is an aggregative sort followed by a simple tie-breaker.
Limiting caps the number of result rows and is applied after grouping. Because the limit applies to grouped output, it constrains the number of groups returned, not the number of underlying source records.
Sorting has an optional model-defined counterpart. A field’s default sort direction is set at authoring time through the SortOrder attribute (values Ascending, Descending, None) on a Semantic Data Object Field, a Semantic Calculated Dimension, or a Semantic Calculated Measurement. Set apply_model_sorting: true in the query to apply those model-defined sort directions to the result. Limiting has no model metadata — it is set per query.
For how sort direction is defined in the model, see Semantic Data Object Field, Semantic Calculated Dimension, and Semantic Calculated Measurement in the Authoring API.
Use simple_sort_order to order results by a single field or alias. Reference the sort key either by sort_by_field (a full field expression) or by sort_by_field_alias (an alias already assigned in the query’s fields). List multiple entries in sort_orders to sort by more than one key; they apply in order.
1{
2 "structured_semantic_query": {
3 "fields": [
4 {
5 "expression": {
6 "table_field": {
7 "name": "Account Name",
8 "table_name": "AccountSemanticLayer__dll"
9 }
10 },
11 "alias": "abc",
12 "row_grouping": true
13 },
14 {
15 "expression": {
16 "table_field": {
17 "name": "Annual Revenue",
18 "table_name": "AccountSemanticLayer__dll"
19 }
20 },
21 "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM",
22 "alias": "abc_sum"
23 }
24 // ...
25 ],
26 "options": {
27 "sort_orders": [
28 {
29 "simple_sort_order": {
30 "sort_by_field_alias": "abc"
31 }
32 },
33 {
34 "simple_sort_order": {
35 "sort_by_field_alias": "abc_sum"
36 }
37 },
38 {
39 "simple_sort_order": {
40 "sort_by_field": {
41 "expression": {
42 "table_field": {
43 "name": "Annual Revenue",
44 "table_name": "AccountSemanticLayer__dll"
45 }
46 },
47 "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_AVG"
48 },
49 "null_values_position": "NULLS_FIRST"
50 }
51 }
52 ]
53 }
54 }
55 // ...
56}Use aggregative_sort_order to order a grouped dimension by an aggregated measure — for example, sort each account group by its SUM(Annual Revenue). Identify the grouped column with grouped_field_alias (an alias) or grouped_expression (a field expression), and specify the measure to sort by with sort_by_field, sort_by_field_alias, or sort_by_row_count: true to sort by the group’s row count. Aggregative and simple sort orders combine in one sort_orders list.
1{
2 "structured_semantic_query": {
3 "fields": [
4 // ...
5 ],
6 "options": {
7 "sort_orders": [
8 {
9 "aggregative_sort_order": {
10 "grouped_field_alias": "abc",
11 "sort_by_field_alias": "abc_sum"
12 }
13 },
14 {
15 "aggregative_sort_order": {
16 "grouped_expression": {
17 "table_field": {
18 "name": "Mailing City",
19 "table_name": "ContactSemanticLayer__dll"
20 }
21 },
22 "sort_by_field": {
23 "expression": {
24 "table_field": {
25 "name": "Annual Revenue",
26 "table_name": "AccountSemanticLayer__dll"
27 }
28 },
29 "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
30 },
31 "null_values_position": "NULLS_FIRST"
32 }
33 },
34 {
35 "aggregative_sort_order": {
36 "grouped_expression": {
37 "table_field": {
38 "name": "Mailing Street",
39 "table_name": "ContactSemanticLayer__dll"
40 }
41 },
42 "sort_by_row_count": true,
43 "null_values_position": "NULLS_FIRST"
44 }
45 }
46 ],
47 "detailed_rows": true
48 }
49 }
50 // ...
51}Set sorting_order on a sort order to ASC (ascending) or DESC (descending). When omitted, results sort ascending. This applies to both simple_sort_order and aggregative_sort_order. You can sort by an alias assigned to a calculated field in the query.
1{
2 "structured_semantic_query": {
3 "fields": [
4 {
5 "expression": {
6 "calculated_field": {
7 "name": "simple_calc",
8 "expression": "123",
9 "calculated_measure_expression": {
10 "measure_output_type": "SEMANTIC_MEASUREMENT_TYPE_NUMBER"
11 }
12 }
13 },
14 "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM",
15 "alias": "Simple Calc"
16 }
17 // ...
18 ],
19 "options": {
20 "sort_orders": [
21 {
22 "simple_sort_order": {
23 "sort_by_field_alias": "Simple Calc",
24 "sorting_order": "DESC"
25 }
26 }
27 ]
28 }
29 }
30 // ...
31}Set null_values_position on a sort order to control where rows with null sort-key values land: NULLS_FIRST places them before non-null rows, NULLS_LAST places them after. This applies to both sort-order shapes. The example above under Simple sort order sets "null_values_position": "NULLS_FIRST", and the example below sets NULLS_LAST.
1{
2 "structuredMetricQuery": {
3 "sort_orders": [
4 {
5 "simple_sort_order": {
6 "sort_by_field": {
7 "expression": {
8 "table_field": {
9 "name": "Name",
10 "table_name": "SemanticAccount_SDO"
11 }
12 }
13 },
14 "sorting_order": "ASC",
15 "null_values_position": "NULLS_LAST"
16 }
17 }
18 ]
19 // ...
20 },
21 "semanticModelId": "2SMxx0000004CAeGAM"
22}When you sort by a field without overriding the direction, results order by the field’s data type: dimension columns sort alphanumerically, table fields sort by their underlying type (numeric fields sort numerically), and date and datetime fields sort chronologically. The following example sorts a date field descending.
1{
2 "structuredSemanticQuery": {
3 "options": {
4 "sort_orders": [
5 {
6 "simple_sort_order": {
7 "sort_by_field_alias": "CreatedDate",
8 "sorting_order": "DESC"
9 }
10 }
11 ]
12 },
13 "semantic_context": {
14 },
15 "fields": [
16 ]
17 },
18 "semanticModelId": "2SMxx0000004CAeGAM"
19}Set apply_model_sorting: true to apply the model-defined SortOrder of each queried field to the result. In the following example the queried fields carry a model sortOrder (Descending on the dimension, Ascending on the calculated measurement), and the query opts in to those directions.
1{
2 "structured_semantic_query": {
3 "fields": [
4 {
5 "expression": {
6 "table_field": {
7 "name": "semantic__Name__c",
8 "table_name": "SemanticAccount__dlm"
9 }
10 }
11 },
12 {
13 "expression": {
14 "semantic_field": {
15 "name": "formula_measurement_example"
16 }
17 }
18 }
19 // ...
20 ],
21 "options": {
22 "apply_model_sorting": true
23 }
24 },
25 "semantic_model": {
26 "apiName": "Sales",
27 "semanticDataObjects": [
28 {
29 "apiName": "SemanticAccount__dlm",
30 "dataObjectName": "ssot__Account__dlm",
31 "dataObjectType": "Dmo",
32 "semanticDimensions": [
33 {
34 "apiName": "semantic__Name__c",
35 "dataType": "Text",
36 "dataObjectFieldName": "ssot__Name__c",
37 "sortOrder": "Descending"
38 }
39 // ...
40 ]
41 }
42 ],
43 "semanticCalculatedMeasurements": [
44 {
45 "apiName": "formula_measurement_example",
46 "dataType": "Number",
47 "expression": "10 * inner_formula_measurement_example",
48 "sortOrder": "Ascending"
49 }
50 // ...
51 ]
52 }
53}Use limit_options.limit to cap the number of result rows. The limit is applied after grouping, so it constrains the number of groups returned rather than the number of underlying source records. For a result grouped by dimension, a limit of 30 returns at most 30 groups even when the source has more.
1{
2 "structuredSemanticQuery": {
3 "fields": [
4 // ...
5 ],
6 "options": {
7 "sort_orders": [
8 // ...
9 ],
10 "limit_options": {
11 "limit": 2000
12 }
13 }
14 }
15 // ...
16}Performance: A
limitcaps the number of result rows after grouping — it constrains the groups returned, not the rows scanned. Use filters to reduce the scan.
Performance: Result size is capped at 5,000 rows for interactive queries and 50,000 rows for non-interactive queries; a query that would exceed the cap fails. Narrow the result with filters or a
limit.
Set detailed_rows: true to return the individual detail rows behind an aggregated result alongside the aggregates, rather than only the grouped/aggregated output.
1{
2 "structured_semantic_query": {
3 "fields": [
4 {
5 "expression": {
6 "table_field": {
7 "name": "Account Name",
8 "table_name": "AccountSemanticLayer__dll"
9 }
10 },
11 "row_grouping": true
12 },
13 {
14 "expression": {
15 "table_field": {
16 "name": "Annual Revenue",
17 "table_name": "AccountSemanticLayer__dll"
18 }
19 },
20 "semantic_aggregation_method": "SEMANTIC_AGGREGATION_METHOD_SUM"
21 }
22 // ...
23 ],
24 "options": {
25 "detailed_rows": true,
26 "subtotals": false,
27 "grand_total": false,
28 "row_counts": false
29 }
30 }
31 // ...
32}Performance: When you request detailed (non-aggregated) rows, a
limitis pushed into the detail-rows step, reducing the rows scanned and returned.
Set row_counts: true to include row-count information in the query result. Combine it with sort_by_row_count (see Aggregative sort order) to sort groups by how many rows each contains.
1{
2 "structured_semantic_query": {
3 "fields": [
4 // ...
5 ],
6 "options": {
7 "sort_orders": [
8 // ...
9 ],
10 "subtotals": true,
11 "grand_total": true,
12 "row_counts": true
13 }
14 }
15 // ...
16}| Field (wire name) | Type | Required | Description |
|---|---|---|---|
sort_orders | SortOrder[] | N | Ordered list of sort orders; each is a simple_sort_order or aggregative_sort_order. Applied in list order. |
simple_sort_order | SimpleSortOrder | N | Sort by a single field or alias. |
aggregative_sort_order | AggregativeSortOrder | N | Sort a grouped dimension by an aggregated measure or row count. |
sort_by_field | SortField | N* | Field expression to sort by (optionally with semantic_aggregation_method). |
sort_by_field_alias | String | N* | Alias (assigned in fields) to sort by. |
sort_by_row_count | bool | N* | In an aggregative sort, sort by the group’s row count. |
grouped_field_alias | String | N* | Alias of the grouped dimension in an aggregative sort. |
grouped_expression | Expression | N* | Field expression of the grouped dimension in an aggregative sort. |
sorting_order | SortingOrder | N | Sort direction: ASC or DESC. Defaults to ascending. |
null_values_position | NullValuesPosition | N | NULLS_FIRST or NULLS_LAST. |
apply_model_sorting | bool | N | Apply each field’s model-defined SortOrder to the result. |
limit_options.limit | int | N | Maximum number of result rows; applied after grouping. |
detailed_rows | bool | N | Return individual detail rows in addition to aggregated results. |
row_counts | bool | N | Include row-count information in the result. |
For the full request schema, see Request Reference.
sort_by_field / sort_by_field_alias (and, for aggregative sorts, one of grouped_field_alias / grouped_expression); the alias must match a field defined in the query.limit is applied after grouping. It caps the number of grouped result rows, not the number of underlying source records, so it directly affects how a grouped visualization renders.sort_by_field and row counts/totals.
j