Skip to content

Array Operators

Array operators in BizzStream allow you to perform operations on arrays, enabling you to analyze and manipulate array values efficiently. Let's explore some of the most commonly used array operators: SUM, MIN, MAX, AVG, and INDEX.

SUM

The SUM operator calculates the sum of all values in one or more arrays.

Syntax

SUM(array-expression1, array-expression2, ...)

  • array-expression1 - An expression or pointer to a line field.
  • array-expression2, ... - [ OPTIONAL ] - Expressions or pointers to line fields.

Examples

  • SUM([1, 2]) returns 3.
  • SUM([1, 2, F["age"]], [1]) returns 5 if the field "age" has a value of 1.
  • SUM(L["orderLines"][*].F["total"]) returns 10 if there are two order lines, one with a "total" value of 7 and another with a "total" value of 3.

SUMPRODUCT

The SUMPRODUCT operator calculates the sum of the products of corresponding elements in one or more arrays.

Syntax

SUMPRODUCT(array-expression1, array-expression2, ...)

  • array-expression1 - An expression or pointer to a line field.
  • array-expression2, ... - [ OPTIONAL ] - Expressions or pointers to line fields.

Examples

  • SUMPRODUCT([1, 2], [3, 4]) returns 11, because (1*3) + (2*4) = 3 + 8 = 11.
  • SUMPRODUCT([1, 2, F["quantity"]], [5, 6, 7]) returns 19 if the field "quantity" has a value of 1, because (1*5) + (2*6) + (1*7) = 5 + 12 + 7 = 24.
  • SUMPRODUCT(L["orderLines"][*].F["quantity"], L["orderLines"][*].F["price"]) returns 37 if there are two order lines, one with quantity 2 and price 7, and another with quantity 3 and price 7, because (2*7) + (3*7) = 14 + 21 = 35.

MIN

The MIN operator finds the minimum value among the values in one or more arrays.

Syntax

MIN(array-expression1, array-expression2, ...)

  • array-expression1 - An expression or pointer to a line field.
  • array-expression2, ... - [ OPTIONAL ] - Expressions or pointers to line fields.

Examples

  • MIN([1, 2]) returns 1.
  • MIN(["2024-01-01", "2024-12-31"]) returns 2024-01-01.
  • MIN(["12:30", "14:00"]) returns 12:30.
  • MIN([]) returns 0.
  • MIN(["apples", "pears"]) returns 0 because none of the values is a number, date or time.
  • MIN([1, 2, F["age"]], [1]) returns 1 if the field "age" has a value of 2.
  • MIN(L["orderLines"].F["total"]) returns 3 if there are two order lines, one with a "total" value of 7 and another with a "total" value of 3.

MAX

The MAX operator finds the maximum value among the values in one or more arrays.

Syntax

MAX(array-expression1, array-expression2, ...)

  • array-expression1 - An expression or pointer to a line field.
  • array-expression2, ... - [ OPTIONAL ] - Expressions or pointers to line fields.

Examples

  • MAX([1, 2]) returns 2.
  • MAX(["2024-01-01", "2024-12-31"]) returns 2024-12-31.
  • MAX(["12:30", "14:00"]) returns 14:00.
  • MAX([]) returns 0.
  • MAX(["apples", "pears"]) returns 0 because none of the values is a number, date or time.
  • MAX([1, 2, F["age"]], [1]) returns 2 if the field "age" has a value of 2.
  • MAX(L["orderLines"].F["total"]) returns 10 if there are two order lines, one with a "total" value of 7 and another with a "total" value of 3.

AVG

The AVG operator calculates the average value from one or more arrays.

Syntax

AVG(array-expression1, array-expression2, ...)

  • array-expression1 - An expression or pointer to a line field.
  • array-expression2, ... - [ OPTIONAL ] - Expressions or pointers to line fields.

Examples

  • AVG([1, 2]) returns 1.5.
  • AVG([1, 2, F["age"]], [1]) returns 1.5 if the field "age" has a value of 2.
  • AVG(L["orderLines"].F["total"]) returns 5 if there are two order lines, one with a "total" value of 7 and another with a "total" value of 3.

CONCATENATE

The CONCATENATE operator concatenates two or more arrays.

Syntax

CONCATENATE(array-expression1, array-expression2, ...)

  • array-expression1 - An expression or pointer to a line field.
  • array-expression2, ... - [ OPTIONAL ] - Expressions or pointers to line fields.

Examples

  • CONCATENATE([1, 2]) returns [1,2].
  • CONCATENATE([1, 2, F["age"]], [1]) returns `[1, 2, 5, 1] if the field "age" has a value of 5.

COUNT

The COUNT operator returns the number of elements in an array.

Syntax

COUNT(array-expression1)

  • array-expression1 - An expression or pointer to a line block.

Examples

  • COUNT([1, 2, 3]) returns 3.
  • COUNT(L["employees"][*]) returns the number of lines in the "employees" line block.

COUNTIF

The COUNTIF operator returns the count of the number of elements in an array that meet a specified condition.

Syntax

COUNTIF(array-expression1, condition)

  • array-expression1 - An expression or pointer to a line block.
  • condition - A condition which determines whether the element should be included in the count.

Examples

  • COUNTIF([1,2,3], F["name"] = "test") returns 3 if the value of the "name" field is "test". Otherwise it returns 0.
  • COUNTIF(L["employees"][*], SRL.F["age"] > 22) returns the number of lines in the "employees" line block for which the line field "age" is higher than 22.

FILTERARRAY

The FILTERARRAY operator returns the elements in an array that meet a specified condition.

Syntax

FILTERARRAY(array-expression1, condition)

  • array-expression1 - An expression or pointer to a line block.
  • condition - A condition which determines whether the element should be included in the array.

Examples

  • FILTERARRAY([1,2,3], F["name"] = "test") returns [1,2,3] if the value of the "name" field is "test". Otherwise it returns [].
  • FILTERARRAY(L["employees"][*].F["age"], SRL.F["job"] = "manager") returns the ages of the employees where the "job" field is equal to "manager".

SUMIF

The SUMIF operator returns the sum of the number of elements in an array that meet a specified condition.

Syntax

SUMIF(array-expression1, condition)

  • array-expression1 - An expression or pointer to a line block.
  • condition - A condition which determines whether the element should be included in the sum.

Examples

  • SUMIF([1,2,3], F["name"] = "test") returns 6 if the value of the "name" field is "test". Otherwise it returns 0.
  • SUMIF(L["employees"][*].F["age"], SRL.F["job"] = "manager") returns the total sum of the "age" field for all lines where the "job" field is equal to "manager".

INDEX

The INDEX operator retrieves the item at a specific index in an array. The index follows a zero-based numbering system, where the first element has an index of 0.

Syntax

INDEX(array-expression1, index)

  • array-expression1 - An expression or pointer to a line field.
  • index - A number or pointer indicating the position of the element.

Examples

  • INDEX([1, 2], 1) returns 2.
  • INDEX([1, 2, F["age"]], 2) returns 4 if the field "age" has a value of 4.
  • INDEX(L["orderLines"].F["total"], 1) returns 3 if there are two order lines, one with a "total" value of 7 and another with a "total" value of 3.

These array operators empower you to perform calculations and retrieve specific values within arrays effectively.

TEXTJOIN

The TEXTJOIN operator combines values from multiple arrays and includes a separator you specify between each text value that will be combined.

Syntax

TEXTJOIN(separator1, ignore-empty, array-expression1)

  • separator1 - A separator to be used when combining values a single array, or when combining values from multiple arrays.
  • ignore-empty - If true, values that are null or undefined are ignored.
  • array-expression1 - An expression or pointer to a line field.

Examples

  • TEXTJOIN(" - ", false, ["A", "B", "C"]) returns "A - B - C".

UNIQUE

The UNIQUE operator returns a duplicate-free version of an array. If multiply arrays are provided, those will first be concatenated. After that only unique values will be returned.

Syntax

UNIQUE(array-expression1, array-expression2, ...)

  • array-expression1 - An expression or pointer to a line field.
  • array-expression2, ... - [ OPTIONAL ] - Expressions or pointers to line fields.

Examples

  • UNIQUE([1, 2], [1, 3]) returns [1,2, 3].
  • UNIQUE([1, 2, F["age"]], [1]) returns `[1, 2, 5] if the field "age" has a value of 5.

ZIPTEXTJOIN

The ZIPTEXTJOIN operator creates an array of grouped elements, the first of which contains the first elements of the given arrays, the second of which contains the second elements of the given arrays, and so on. The operator then combines the values and includes a separator you specify between each text value that will be combined.

Syntax

ZIPTEXTJOIN(separator1, separator2, ignore-empty, array-expression1, array-expression2, ...)

  • separator1 - A separator to be used when combining values a single array, or when combining values from multiple arrays.
  • separator2 - A separator to be used between the combined values. This separator can only be provided when you provide at least two arrays.
  • ignore-empty - If true, values that are null or undefined are ignored.
  • array-expression1 - An expression or pointer to a line field.
  • array-expression2, ... - [ OPTIONAL ] - An expression or pointer to a line field.

Examples

  • ZIPTEXTJOIN(" - ", ", ", false, ["A", "B", "C"], ["X", "Y", "Z"]) returns "A - X, B - Y, C - Z".
  • ZIPTEXTJOIN(" - ", ", ", true, ["A", null, "B", "C"], [1, undefined, 2, 3] returns "A - 1, B - 2, C-3".