Array Functions (MOPS)

`$pivot()`

Signature: $pivot(data, keyFields, pivotField, measures)

Converts row-based data into columnar format by grouping, pivoting, and applying aggregations. It uses % to concatenate multiple keyFields. If in case, invalid input is provided then it will return an empty array.

Parameters

Parameter	Type	Description
`data`	object[]	Array of objects to pivot
`keyFields`	string\|string[]	Grouping column(s) to preserve as rows
`pivotField`	string	Column whose values become new headers
`measures`	[string, ‘SUM’\|‘AVERAGE’][]	Array of [field, aggregation] pairs

Examples

// JSONata
$pivot(
[
    {Region: "West", Product: "A", Sales: 100},
    {Region: "West", Product: "A", Sales: 150},
    {Region: "East", Product: "B", Sales: 200}
],
["Region", "Product"],
"Product",
[["Sales", "SUM"]]
)
// Result
[
    {Region: "West", Product: "A", A: 250},
    {Region: "East", Product: "B", B: 200}
]

`$fillMissingValues()`

Signature: $fillMissingValues(data, orderingColumn, applyToColumns, options)

Fills missing values (undefined or null) in selected columns by propagating known values across rows. Rows can be sorted by orderingColumn before fill is applied.

options.mode supports:

"forwardFill" (default): fills missing values from previous rows
"backwardFill": fills missing values from following rows

Parameters

Parameter	Type	Description
`data`	object[]	Array of row objects
`orderingColumn`	string\|null	Column used to sort rows before filling; pass `null` to keep current order
`applyToColumns`	string[]	Column names where missing values should be filled
`options`	object	Fill options, e.g. `{ "mode": "forwardFill" }`

Examples

// JSONata (forward fill)
$fillMissingValues(
[
    {timestamp: 1, frc001: 10, frc002: null},
    {timestamp: 2, frc001: null, frc002: 20},
    {timestamp: 3, frc001: null, frc002: null}
],
"timestamp",
["frc001", "frc002"],
{"mode":"forwardFill"}
)
// Result
[
    {timestamp: 1, frc001: 10, frc002: null},
    {timestamp: 2, frc001: 10, frc002: 20},
    {timestamp: 3, frc001: 10, frc002: 20}
]

// JSONata (backward fill)
$fillMissingValues(
[
    {timestamp: 1, frc001: null, frc002: null},
    {timestamp: 2, frc001: 11, frc002: null},
    {timestamp: 3, frc001: null, frc002: 22}
],
"timestamp",
["frc001", "frc002"],
{"mode":"backwardFill"}
)
// Result
[
    {timestamp: 1, frc001: 11, frc002: 22},
    {timestamp: 2, frc001: 11, frc002: 22},
    {timestamp: 3, frc001: null, frc002: 22}
]

`$stdev()`

Signature: $stdev(arr)

Calculates population standard deviation (σ) for an array of numbers using the “n” formula (divided by N).

Parameters

Parameter	Type	Description
`arr`	number[]	Array of numeric values to analyze

Examples

Basic calculation:

$stdev([1, 2, 3, 4, 5]) => 1.4142135623730951 (Population σ for 1-5: √2 ≈ 1.4142)

Empty array handling:
```
$stdev([]) => NaN
```

Single value array:

$stdev([42]) => 0 (No variation when only 1 value exists)