Sorting Grouping and Aggregation

Sorting, Grouping and Aggregation

How to sort, group, and aggregate values in JSONata. The examples below use the sample JSON document from the Jsonata-Tutorial.

Fuuz Context

Where this applies in Fuuz: Sorting, grouping, and aggregation are core to building rollups in Fuuz Pipelines — e.g., summarizing production counts per SKU, batching work orders, and preparing totals before publishing to downstream systems.

• Order-by / $sort: Ensure deterministic batch order for connectors that expect ordered payloads (e.g., ERP imports).
• Grouping via object constructors: Use dynamic keys (e.g., SKU, WorkCenter) to group events; evaluate aggregations after grouping.
• Aggregation: Prefer $sum($.(Price*Quantity))-style patterns for accurate totals across grouped sequences.

Sorting

Arrays contain an ordered collection of values. If you need to re-order the values, then the array must be sorted. In JSONata, there are two ways of sorting an array:

1. Using the $sort() function (see Array-Functions-How-To).
2. Using the order-by operator (see Path-Operators).

The order-by operator is a convenient syntax that can be used directly in a path expression to sort the result sequences in ascending or descending order. The $sort() function requires more syntax to be written, but is more flexible and supports custom comparator functions.

Grouping

The JSONata object constructor syntax (see Construction) allows you to specify an expression for the key in any key/value pair (the value can obviously be an expression too). The key expression must evaluate to a string, since this is a restriction on JSON objects. The key and value expressions are evaluated for each item in the input context (see Processing-Model). The result of each key/value expression pair is inserted into the resulting JSON object.

If the evaluation of any key expression results in a key that is already in the result object, then the result of its associated value expression will be grouped with the value(s) already associated with that key. Note that the value expressions are not evaluated until all of the grouping has been performed. This allows for aggregation expressions to be evaluated over the collection of items for each group.

Examples

• Group all of the product sales by name, with the price of each item in each group:

  Account.Order.Product{`Product Name`: Price}
  

  {
    "Bowler Hat": [ 34.45, 34.45 ],
    "Trilby hat": 21.67,
    "Cloak": 107.99
  }

• Group all of the product sales by name, with the price and the quantity of each item in each group:

  Account.Order.Product {
    `Product Name`: {"Price": Price, "Qty": Quantity}
  }
  

  {
    "Bowler Hat": {
      "Price": [ 34.45, 34.45 ],
      "Qty": [ 2, 4 ]
    },
    "Trilby hat": { "Price": 21.67, "Qty": 1 },
    "Cloak": { "Price": 107.99, "Qty": 1 }
  }

  Note in the above example, the value expression grouped all of the prices together and all of the quantities together into separate arrays. This is because the context value is the sequence of all grouped Products, and the Price expression will select all prices from all products. If you want to collect the price and quantity into individual objects, then you need to evaluate the object constructor for each product in the context sequence. The following example shows this.

• Explicit use of $.{ ... } to create an object for each item in the group:

  Account.Order.Product {
    `Product Name`: $.{"Price": Price, "Qty": Quantity}
  }
  

  {
    "Bowler Hat": [
      { "Price": 34.45, "Qty": 2 },
      { "Price": 34.45, "Qty": 4 }
    ],
    "Trilby hat": { "Price": 21.67, "Qty": 1 },
    "Cloak": { "Price": 107.99, "Qty": 1 }
  }

• Multiply the Price by the Quantity for each product in each group:

  Account.Order.Product{`Product Name`: $.(Price*Quantity)}
  

  {
    "Bowler Hat": [ 68.9, 137.8 ],
    "Trilby hat": 21.67,
    "Cloak": 107.99
  }

• The total aggregated value in each group:

  Account.Order.Product{`Product Name`: $sum($.(Price*Quantity))}
  

  {
    "Bowler Hat": 206.7,
    "Trilby hat": 21.67,
    "Cloak": 107.99
  }

Aggregation

Often queries are just required to return aggregated results from a set of matching values. A number of aggregation functions are available which return a single aggregated value when applied to an array of values.

Examples

• Total price of each product in each order:

  $sum(Account.Order.Product.Price)
  

  198.56

• More likely, you want to add up the total of the price times quantity for each order:

  $sum(Account.Order.Product.(Price*Quantity))
  

  336.36

Other numeric aggregation functions are available (i.e. average, min, max — see Aggregation-Functions), as well as an aggregator for strings ($join() — see String-Functions). It is also possible to write complex custom aggregators using the $reduce() higher-order function (see Higher-Order-Functions).

See Also

• Jsonata-Tutorial
• Simple-Queries
• Aggregation-Functions
• Path-Operators
• Construction
• Higher-Order-Functions

---

Source: support.fuuz.com