Aggregate Query API

Aggregate Query API

In addition to the standard GraphQL query API behavior, Fuuz also has support for aggregating data as part of a GraphQL query, streamlining the process of producing reports or visualizations using Fuuz data. The aggregation query feature centers around an _aggregate field which is automatically added to data models upon deployment.

Example Package

Examples in this documentation use schema included in the 

Aggregate Query Testing@0.0.2.fuuz

 sample package. This schema is simple: a MasterUnit which links to multiple Inventory records, each of which links to one InventoryStatus. The sample package also includes a saved query containing aggregation query examples.

Schema used for examples in this documentation.

Aggregating Field Values Using _aggregate

Returning aggregated field values is as simple as selecting a field and one or more operators on the _aggregate query field. All fields with “primitive” data types will appear inside _aggregate, with appropriate operators for the field’s data type available under each field. The Fuuz API will apply any filter criteria applied through the query where argument, then group the matched documents and compute the aggregated values per group.

Example: Unique Inventory Statuses

Query: Unique Inventory Statuses
1
2
3
4
5
6
7
8
9
10
11
12
13
⌄
⌄
⌄
⌄
⌄
⌄
query Aggregate {
inventory {
edges {
node {
_aggregate {
inventoryStatusId {
uniqueValues
}
}
}
}
}
}
Results: Unique Inventory Statuses
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
{
"inventory": {
"edges": [
{
"node": {
"_aggregate": {
"inventoryStatusId": {
"uniqueValues": [
"good",
"suspect"
]
}
}
}
}
]
}
}

Aggregating Node Relation and Embedded Type Values

The aggregation query API also works with node relation and embedded type fields. To query aggregated field values from node relation or embedded models, simply select the field to aggregate from the _aggregate field on the node or embedded model.

Example: Total Good Inventory Quantity

Query: Total Good Inventory Quantity
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
⌄
⌄
⌄
⌄
⌄
⌄
⌄
query Aggregate {
inventory(where: { inventoryStatusId: { _eq: "good" } }) {
edges {
node {
inventoryStatusId
quantity {
_aggregate {
value {
sum
}
}
}
}
}
}
}
Results: Total Good Inventory Quantity
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
{
"inventory": {
"edges": [
{
"node": {
"inventoryStatusId": "good",
"quantity": {
"_aggregate": {
"value": {
"sum": 9057
}
}
}
}
}
]
}
}

Available Aggregation Operators

There are a wide range of aggregation operators available in aggregation queries, outlined below. A single aggregation query can select multiple fields or operators to aggregate.

Universal operators:

  • count returns the number of values in the group. This operator returns the same value for all fields in _aggregate.

  • uniqueCount returns the number of unique values in the group. This operator uses exact value comparison.

  • values returns a list of all grouped field values, ordered by the query orderBy argument.

  • uniqueValues returns a list of unique grouped field values. The order of the returned values is not guaranteed.

  • first returns a list of the first grouped field values as ordered by the query orderBy argument. The number of values to return can be specified using the values argument on the field, and defaults to 1.

  • last returns a list of the last grouped field values as ordered by the query orderBy argument. The number of values to return can be specified using the values argument on the field, and defaults to 1.

  • minimum returns a list of the smallest grouped field values as ordered by MongoDB’s value comparison order. The number of values to return can be specified using the values argument on the field, and defaults to 1.

  • maximum returns a list of the largest grouped field values as ordered by MongoDB’s value comparison order. The number of values to return can be specified using the values argument on the field, and defaults to 1.

Numeric operators (Int, Float, and JSON values):

  • average returns the average of the grouped field values. This operator is only available on numeric field types (Int, Float, and JSON).

  • sum returns the sum of the grouped field values. This operator is only available on numeric field types (Int, Float, and JSON).

  • standardDeviation returns the standard deviation of the grouped values. The calculation method (sample or population) can be specified using the method argument on the field, and defaults to sample. This operator is only available on numeric field types (Int, Float, and JSON).

  • median returns the median of the grouped values.

  • percentile allows users to specify a list of requested percentiles, and returns an array of values for each of the requested percentiles.

Example: Inventory Statistics

Query: Inventory Statistics
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
query Aggregate {
inventory {
edges {
node {
_aggregate {
id {
count
}
}
quantity {
_aggregate {
value {
average
standardDeviation(method: population)
minimum(values: 5)
maximum(values: 5)
}
}
}
}
}
}
}
Results: Inventory Statistics
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
{
"inventory": {
"edges": [
{
"node": {
"_aggregate": {
"id": {
"count": 220
}
},
"quantity": {
"_aggregate": {
"value": {
"average": 45.17272727272727,
"standardDeviation": 29.397080588171292,
"minimum": [
0,
0,
0,
0,
1
],
"maximum": [
99,
99,
98,
97,
96
]
}
}
}
}
}
]
}
}

Grouping by Field Values

Returning aggregated values for the full set of matched documents is great, but most use cases for reporting or visualizations involve aggregating values based on some kind of group criteria. Thankfully, grouping data in aggregation queries is simple: just select the fields you want to group the aggregated values by, and the API will automatically group by those field values and return them with the query. This supports a wide range of use cases: querying total sales by customer for a quarterly report, displaying inventory volumes by item and building, finding the last time a production strategy was used - the possibilities are endless!

Example: Master Unit Quantities

Query: Master Unit Quantities
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
query Aggregate {
inventory(orderBy: {field: masterUnitId, direction: asc}) {
edges {
node {
masterUnit {
number
}
quantity {
unit {
code
}
_aggregate {
value {
sum
}
}
}
}
}
}
}
Results: Master Unit Quantities
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
{
"inventory": {
"edges": [
{
"node": {
"masterUnit": {
"number": "400001"
},
"quantity": {
"unit": {
"code": "in"
},
"_aggregate": {
"value": {
"sum": 2104
}
}
}
}
},
{
"node": {
"masterUnit": {
"number": "400002"
},
"quantity": {
"unit": {
"code": "in"
},
"_aggregate": {
"value": {
"sum": 1097
}
}
}
}
},
{
"node": {
"masterUnit": {
"number": "400003"
},
"quantity": {
"unit": {
"code": "in"
},
"_aggregate": {
"value": {
"sum": 1252
}
}
}
}
},
{
"node": {
"masterUnit": {
"number": "100001"
},
"quantity": {
"unit": {
"code": "ft"
},
"_aggregate": {
"value": {
"sum": 1335
}
}
}
}
}
]
}
}

Example: Inventory Totals by Status and Unit

Query: Inventory Totals by Status and Unit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
query Aggregate {
inventory {
edges {
node {
inventoryStatus {
label
}
quantity {
unit {
code
}
_aggregate {
value {
sum
}
}
}
_aggregate {
id {
count
}
}
}
}
}
}
Results: Inventory Totals by Status and Unit
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
{
"inventory": {
"edges": [
{
"node": {
"inventoryStatus": {
"label": "Good"
},
"quantity": {
"unit": {
"code": "in"
},
"_aggregate": {
"value": {
"sum": 4453
}
}
},
"_aggregate": {
"id": {
"count": 100
}
}
}
},
{
"node": {
"inventoryStatus": {
"label": "Good"
},
"quantity": {
"unit": {
"code": "ft"
},
"_aggregate": {
"value": {
"sum": 4604
}
}
},
"_aggregate": {
"id": {
"count": 100
}
}
}
},
{
"node": {
"inventoryStatus": {
"label": "Suspect"
},
"quantity": {
"unit": {
"code": "ft"
},
"_aggregate": {
"value": {
"sum": 881
}
}
},
"_aggregate": {
"id": {
"count": 20
}
}
}
}
]
}
}

Querying the id field

One exception to this rule is the id field on a data model. Since each data model has a unique id, including that field in the group would result in data with one document per group - essentially, not aggregating at all! Since so many systems - Fuuz included - rely on a unique id record for each piece of data, it gets special treatment in aggregation queries. Instead of being included in the group set, the Fuuz API will instead return the ID from the first grouped record, sorted by the criteria specified in the query orderBy argument. This ensures for an aggregation query, the returned id is unique and stable between requests. However, it does mean that changing the sort criteria applied on a query may result in group IDs changing, so be careful!

Grouping by Rounded Field Values (Bucketing)

The following section describes a feature available as of the 2024.4 release.

Many common use cases for aggregate queries involve grouping by Date, DateTime, or Time fields - createdAt, updatedAt, occurredAt, or any other custom field - to create a report or chart with “buckets” of time in one dimension. For these field types, including the field in the query as described above will group by the precise date or time, down to the smallest increment of precision (the millisecond for DateTime and Time fields and the day for Date fields). Most of the time, this isn’t what you’re looking for, as you’re likely to end up with just one record per group!

For this use case, the Fuuz API supports a round argument that can be applied to date (Date, DateTime, and Time) and numeric (Float and Int) fields. This argument allows you to specify the unit and multiple to round to (e.g. “every 8 hours”), as well as a direction to round (in the case of numeric fields). This allows for complex time-bucketed queries that drive timeseries visualizations, monthly reports, and other date/time analytics. It also allows for the creation of aggregate queries that group by a numeric value, allowing the API to directly drive visualizations like histograms.

Example: Inventory Totals by Age (Month)

Query: Inventory Totals by Age (Month)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
query Aggregate {
inventory {
edges {
node {
createdAt(round: { unit: month })
quantity {
_aggregate {
value {
sum
}
}
}
_aggregate {
id {
count
}
}
}
}
}
}
Results: Inventory Totals by Age (Month)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
⌄
{
"inventory": {
"edges": [
{
"node": {
"createdAt": "2024-02-01T00:00:00.000Z",
"quantity": {
"_aggregate": {
"value": {
"sum": 10337
}
}
},
"_aggregate": {
"id": {
"count": 225
}
}
}
},
{
"node": {
"createdAt": "2024-03-01T00:00:00.000Z",
"quantity": {
"_aggregate": {
"value": {
"sum": 2960
}
}
},
"_aggregate": {
"id": {
"count": 60
}
}
}
}
]
}
}

Planned Enhancements

There are a number of enhancements to this system we plan to implement over the coming months - if you have any feedback, feel free to send it our way via the Fuuz Community!

Filtering by Aggregated Values

Many use cases would benefit from the ability to filter by aggregated values - for example, to find only those master units with more than 100 total units of inventory.

Sorting by Aggregated Values

It would also be helpful for some use cases to be able to sort the returned data by the aggregated values - for example, to return the master units with the most or least inventory.

Handling of Connection Relations

Aggregation queries currently return connection edges from the first matched group when a connection relation is queried, which isn’t the behavior we’d like to see. We’re going to continue working to improve that behavior.

Additional Operators

There are several additional operators we plan to add over the coming months, including percentile and median operators.

 

    • Related Articles

    • Chart

      This screen element, found under the Display group, is aimed at streamlining the creation of visualizations, charts, and widgets on Fuuz screens. The element is completely self-contained - no need to worry about forms - and all configuration is done ...

    • GraphQL API

      GraphQL | A query language for your API graphql.org GraphQL is the preferred API query langauge for modern systems, like Fuuz. You can leverage GraphQL within the Fuuz Platform by navigating to the “API Explorer” Overview of GraphQL: Background, ...

    • Create a Document With Fuuz Data

      This article provides information resources, prerequisites, and instructions for creating documents with MFGx. Prerequisites Pulling Data Report Transform Evaluated Transform Prerequisites Become familiar with the Fuuz API. Become familiar with the ...

    • Combobox

      Comboboxes require that you provide an id and a label property. The values of these can be the same but they must both be present. The label is what will show in the Combobox when you are selecting and the id by default will be the value placed in ...

    • Saved Queries

      Open the Fuuz app. Select Fuuz → System → Orchestration → Saved Queries dropdown menu. Or Type Save into the Search Bar: From here, it is possible to perform the following task or tasks. Search for a query. Create a query. Update a query. Edit a ...