Omnistudio List and Array Functions

You are here:

Omnistudio List and Array Functions

Functions that operate on lists and arrays.

Omnistudio AVG Function
Returns the average of a list or a JSON array of numeric values.
Omnistudio FILTER Function
Filters a list of JSON objects by a specified condition and returns a JSON list of matching objects.
Omnistudio LIST Function
Converts a list or a named JSON array to an anonymous JSON array.
Omnistudio LISTMERGE Function
Merges nodes from two or more JSON lists of objects into a single JSON list based on the values of one or more merge keys.
Omnistudio LISTMERGEPRIMARY Function
Augments the nodes of a primary JSON list of objects with data from one or more other JSON lists based on the values of one or more merge keys.
Omnistudio LISTSIZE Function
Returns the number of items in a list or a JSON array.
Omnistudio MAPTOLIST Function
Converts a JSON object or a hierarchy of objects to a JSON list.
Omnistudio MAX Function
Returns the maximum value from a list or a JSON array of numeric values.
Omnistudio MIN function
Returns the minimum value from a list or a JSON array of numeric values.
Omnistudio SORTBY Function
Sorts a JSON list of objects by specified keys and returns a sorted JSON list. (Data Mappers only)
Omnistudio SUM function
Returns the sum of a list or a JSON array of numeric values.

Omnistudio AVG Function

Returns the average of a list or a JSON array of numeric values.

Signature

AVG(list)

Return Value

Number

Parameters

Parameter	Data Type	Necessity	Description
`list`	List of numbers	Required	A comma-separated list of numeric values or a list of numeric values from a JSON array. Null values in the list count toward the denominator, which lowers the average.

List Example

Formula: AVG(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Return value: 5.5

JSON Array Example

Sample data:

"List": [ 
  {
    "Item": 3.5
  },
  {
    "Item": 4.25
  },
  {
    "Item": 5.75
  }
]

Formula: AVG(List:Item)

Formula: AVG(%List:Item%)

Return value: 4.5

Omnistudio FILTER Function

Filters a list of JSON objects by a specified condition and returns a JSON list of matching objects.

The function performs case-insensitive matching of strings.

Signature

FILTER(LIST(list), condition)

Return Value

JSON list

Parameters

Parameter	Data Type	Necessity	Description
`LIST(list)`	JSON list	Required	An anonymous JSON array of objects to be filtered. Use the `LIST` function to pass a JSON array.
`condition`	Expression	Required	An equivalence expression that identifies the node to filter on and the value the node must have to qualify as a match. Specify the value as a string or a variable. You must enclose the entire expression in quotes and also quote a string value within the expression.

Filter by String Example

Sample data:

"NameList": [ 
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  }
]

Formula: FILTER(LIST(NameList), 'LastName == "Xavier"')

Formula: FILTER(LIST(NameList), "LastName == 'Xavier'")

Return value:

[ 
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  }
]

Filter by Variable Example

Sample data:

"NameList": [ 
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  }
],
"FindName": "Xavier"

Formula: FILTER(LIST(NameList), 'LastName == "' + FindName + '"')

Formula: FILTER(LIST(NameList), "LastName == '" + FindName + "'")

Return value:

[ 
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  }
]

Omnistudio LIST Function

Converts a list or a named JSON array to an anonymous JSON array.

Use the LIST function with other functions that require an anonymous JSON array (a JSON list) as input: FILTER, LISTMERGE, LISTMERGEPRIMARY, SERIALIZE, SORTBY, and a custom FUNCTION that passes a JSON list as an argument.

Signature

LIST(expression)

Return Value

JSON list

Parameters

Parameter	Data Type	Necessity	Description
`expression`	List or named JSON array	Required	An expression that resolves to a list of values or JSON objects or to a named JSON array of one or more values or objects. Each object can contain one or more nodes or key-value pairs. If you pass it an anonymous JSON array, the function returns an array with a single `VLOCITY-FORMULA-LIST` object that contains that anonymous array.

Parameter

Data Type

Necessity

Description

expression

List or named JSON array

Required

An expression that resolves to a list of values or JSON objects or to a named JSON array of one or more values or objects. Each object can contain one or more nodes or key-value pairs.

If you pass it an anonymous JSON array, the function returns an array with a single VLOCITY-FORMULA-LIST object that contains that anonymous array.

List of Alphabetic Values Example

Formula: LIST("a", "b", "c")

Return value: [ "a", "b", "c" ]

List of Numeric Values Example

Formula: LIST(1, 2, 2, 3, 3, 4)

Return value: [ 1, 2, 2, 3, 3, 4 ]

JSON Object Example

Sample data:

"Contact": {
  "FirstName": "Mike",
  "LastName": "Smith"
}

Formula: LIST(Contact)

Return value:

[
  {
    "LastName": "Smith",
    "FirstName": "Mike"
  }
]

Named JSON Array Example

Sample data:

"NameList": [
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  }
]

Formula: LIST(NameList)

Return value:

[
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  }
]

Named JSON Array Example

Sample data:

"Account": [
  {
    "Cases": [
      {
        "Case1": {
          "CreatedDate": "2/1/2004T16:35:30 GMT -0500 (EDT)",
          "LastUpdate": "2/8/2024T09:15:00 GMT -0500 (EDT)"
        }
      },
      {
        "Case2": {
          "CreatedDate": "2/2/2004T11:05:05 GMT -0500 (EDT)",
          "LastUpdate": "2/3/2024T15:50:57 GMT -0500 (EDT)"
        }
      }
    ]
  }
]

Formula: LIST(Account:Cases)

Return value:

[
  {
    "Case1": {
      "CreatedDate": "2/1/2024T16:35:30 GMT -0500 (EDT)",
      "LastUpdate": "2/8/2024T09:15:00 GMT -0500 (EDT)"
    }
  },
  {
    "Case2": {
      "CreatedDate": "2/2/2025T11:05:05 GMT -0500 (EDT)",
      "LastUpdate": "2/3/2025T15:50:57 GMT -0500 (EDT)"
    }
  }
]

Omnistudio LISTMERGE Function

Merges nodes from two or more JSON lists of objects into a single JSON list based on the values of one or more merge keys.

The function combines nodes from the specified anonymous JSON arrays when the values of the named merge keys match. The resulting list contains all of the nodes and keys from all of the merged lists. If lists contain nodes with identical merge keys but different values, values from later nodes overwrite values from earlier nodes. The function performs case-insensitive matching of merge keys.

Signature

LISTMERGE(mergeKey..., LIST(list)...)

Return Value

JSON list

Parameters

Parameter	Data Type	Necessity	Description
`mergeKey...`	String	Required	A comma-separated list of one or more keys. The function combines nodes from the lists when the values of their merge keys match.
`LIST(list)...`	JSON list	Required	Two or more anonymous JSON arrays to be merged. Use the `LIST` function to pass each JSON array. If you pass only a single array, the function returns that array.

Merge Lists Example

Sample data:

"ContactNames": [
  {
    "id": "0036ab",
    "lastName": "Jones",
    "firstName": "Cathy"
  },
  {
    "id": "2787kq",
    "lastName": "Smith",  
    "firstName": "Albert"
  },
  {
    "id": "3610xr",
    "lastName": "Smith",
    "firstName": "Ben"
  }
],
"ContactAddresses": [
  {
    "id": "0036ab",
    "city": "Phoenix",
    "state": "AZ"
  },
  {
    "id": "2787kq",
    "city": "San Francisco",
    "state": "CA"
  },
  {
    "id": "3610xr",
    "city": "Portland",
    "state": "OR"
  }
],
"ContactBirthdates": [
  {
    "id": "0036ab",
    "birthdate": "1976-10-04"
  },
  {
    "id": "2787kq",
    "birthdate": "1973-10-01"
  },
  {
    "id": "3610xr",
    "birthdate": "1974-10-02"
  }
]

Formula: LISTMERGE("id", LIST(ContactNames), LIST(ContactAddresses), LIST(ContactBirthdates))

Return value:

[
  {
    "id": "0036ab",
    "lastName": "Jones",
    "firstName": "Cathy",
    "city": "Phoenix",
    "state": "AZ",
    "birthdate": "1976-10-04"
  },
  {
    "id": "2787kq",
    "lastName": "Smith",  
    "firstName": "Albert",
    "city": "San Francisco",
    "state": "CA",
    "birthdate": "1973-10-01"
  },
  {
    "id": "3610fw",
    "lastName": "Smith",
    "firstName": "Ben",
    "city": "Portland",
    "state": "OR",
    "birthdate": "1974-10-02"
  }
]

Omnistudio LISTMERGEPRIMARY Function

Augments the nodes of a primary JSON list of objects with data from one or more other JSON lists based on the values of one or more merge keys.

The function augments the nodes from the first (primary) specified anonymous JSON array with data from all other specified anonymous JSON arrays when the values of the named merge keys match. The resulting list contains all of the nodes and keys from the first list along with keys from the other matching lists. If nodes from later lists with identical merge keys have different values from the first list, values from later nodes overwrite values from earlier nodes. The function performs case-insensitive matching of merge keys.

For example, use the function when you have a list of qualified products that you want to augment with data from related lists. The function does not include products from later lists that do appear in the primary list.

Signature

LISTMERGEPRIMARY(mergeKey..., LIST(list)...)

Return Value

JSON list

Parameters

Parameter	Data Type	Necessity	Description
`mergeKey...`	String	Required	A comma-separated list of one or more keys. The function augments nodes from the first list with keys from the other lists when the values of their merge keys match.
`LIST(list)...`	JSON list	Required	A primary anonymous JSON array to be extended with data from one or more other anonymous JSON arrays. The function includes only nodes from the first list in the list that it returns. Use the `LIST` function to pass each JSON array. If you pass only a primary array, the function returns that array.

Primary Merge List Example

Sample data:

"ContactNames": [
  {
    "id": "0036ab",
    "lastName": "Jones",
    "firstName": "Cathy"
  },
  {
    "id": "3610xr",
    "lastName": "Smith",
    "firstName": "Ben"
  }
],
"ContactAddresses": [
  {
    "id": "0036ab",
    "city": "Phoenix",
    "state": "AZ"
  },
  {
    "id": "7723hw",
    "lastName": "Sacramento",
    "firstName": "CA"
  },
  {
    "id": "2787kq",
    "city": "San Francisco",
    "state": "CA"
  },
  {
    "id": "3610xr",
    "city": "Portland",
    "state": "OR"
  }
],
"ContactBirthdates": [
  {
    "id": "0036ab",
    "birthdate": "1976-10-04"
  },
  {
    "id": "7723hw",
    "birthdate": "1974-07-04"
  },
  {
    "id": "2787kq",
    "birthdate": "1973-10-01"
  },
  {
    "id": "3610xr",
    "birthdate": "1974-10-02"
  }
]

Formula: LISTMERGEPRIMARY("id", LIST(ContactNames), LIST(ContactAddresses), LIST(ContactBirthdates))

Return value:

[
  {
    "id": "0036ab",
    "lastName": "Jones",
    "firstName": "Cathy",
    "city": "Phoenix",
    "state": "AZ",
    "birthdate": "1976-10-04"
  },
  {
    "id": "3610fw",
    "lastName": "Smith",
    "firstName": "Ben",
    "city": "Portland",
    "state": "OR",
    "birthdate": "1974-10-02
  }
]

Omnistudio LISTSIZE Function

Returns the number of items in a list or a JSON array.

To test for an empty array, use the ISBLANK function.

Signature

LISTSIZE(list)

Return Value

Number

Parameters

Parameter	Data Type	Necessity	Description
`list`	List or JSON array	Required	A comma-separated list of values or a JSON array of values or nodes.

List Example

Formula: LISTSIZE(6, 7, 8, 9, 10)

Return value: 5

Empty List Example

Formula: LISTSIZE()

Formula: LISTSIZE(())

Return value: 0

JSON Array Example

Sample data:

"NameList": [
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  }
]

Formula: LISTSIZE(NameList)

Return value: 3

Empty Array Example

Sample data: "EmptyList": []

Formula: LISTSIZE([])

Formula: LISTSIZE(EmptyList)

Return value: 1

Formula: IF(ISBLANK(EmptyList), 0, LISTSIZE(EmptyList))

Return value: 0

Omnistudio MAPTOLIST Function

Converts a JSON object or a hierarchy of objects to a JSON list.

Signature

MAPTOLIST(jsonObject)

Return Value

JSON list

Parameters

Parameter	Data Type	Necessity	Description
`jsonObject`	JSON object	Required	A node or node path of a JSON object to convert to a JSON list. Each node can contain one or more nodes or key-value pairs.

JSON Object Example

Sample data:

"Contact": {
  "FirstName": "William",
  "MiddleName": "Leslie",
  "LastName": "Edison",
  "Parents": {
    "Father": {
      "FirstName": "Thomas",
      "MiddleName": "Alva",
      "LastName": "Edison"
    },
    "Mother": {
      "FirstName": "Mary",
      "MiddleName": "Stilwell",
      "LastName": "Edison"
    }
  }
}

Formula: MAPTOLIST(Contact)

Return value:

[
  {
    "LastName": "Edison"
  },
  {
    "MiddleName": "Leslie"
  },
  {
    "FirstName": "William"
  },
  {
    "Parents": {
      "Father": {
        "MiddleName": "Alva",
        "LastName": "Edison",
        "FirstName": "Thomas"
      },
      "Mother": {
        "MiddleName": "Stilwell",
        "LastName": "Edison",
        "FirstName": "Mary"
      }
    }
  }
]

Formula: MAPTOLIST(Contact:Parents)

Return value:

[
  {
    "Mother": {
      "MiddleName": "Stilwell",
      "LastName": "Edison",
      "FirstName": "Mary"
    }
  },
  {
    "Father": {
      "MiddleName": "Alva",
      "LastName": "Edison",
      "FirstName": "Thomas"
    }
  }
]

Formula: MAPTOLIST(Contact:Parents:Mother)

Return value:

[
  {
    "LastName": "Edison"
  },
  {
    "MiddleName": "Stilwell"
  },
  {
    "FirstName": "Mary"
  }
]

Omnistudio MAX Function

Returns the maximum value from a list or a JSON array of numeric values.

Signature

MAX(list)

Return Value

Number

Parameters

Parameter	Data Type	Necessity	Description
`list`	List of numbers	Required	A comma-separated list of numeric values or a list of numeric values from a JSON array.

List Example

Formula: MAX(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Return value: 10

JSON Array Example

Sample data:

"List": [ 
  {
    "Item": 3
  },
  {
    "Item": 4
  },
  {
    "Item": 5
  }
]

Formula: MAX(List:Item)

Return value: 5

Omnistudio MIN function

Returns the minimum value from a list or a JSON array of numeric values.

Signature

MIN(list)

Return Value

Number

Parameters

Parameter	Data Type	Necessity	Description
`list`	List of numbers	Required	A comma-separated list of numeric values or a list of numeric values from a JSON array.

List Example

Formula: MIN(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Return value: 1

JSON Array Example

Sample data:

"List": [ 
  {
    "Item": 3
  },
  {
    "Item": 4
  },
  {
    "Item": 5
  }
]

Formula: MIN(List:Item)

Return value: 3

Omnistudio SORTBY Function

Sorts a JSON list of objects by specified keys and returns a sorted JSON list. (Data Mappers only)

The function performs case-insensitive matching of keys and sorting of values.

Signature

SORTBY(LIST(list), key..., [:DSC])

Return Value

JSON list

Parameters

Parameter	Data Type	Necessity	Description
`LIST(list)`	JSON list	Required	An anonymous JSON array of objects to be sorted. Use the `LIST` function to pass a JSON array.
`key...`	String	Required	One or more keys by which the JSON objects are to be sorted.
`[:DSC]`	String	Optional	Sorts the results in descending order. You must enclose the value in single or double quotes, for example, `'[:DSC]'`. Omit the parameter to sort the results in ascending order by default.

Sort List in Ascending Order Example

Sample data:

"NameList": [
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  }
]

Formula: SORTBY(LIST(NameList), 'LastName', 'FirstName')

Return value:

[ 
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  },
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  }
]

Sort List in Descending Order Example

Sample data:

"NameList": [ 
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  }
]

Formula: SORTBY(LIST(NameList), 'LastName', 'FirstName', '[:DSC]')

Return value:

[
  {
    "FirstName": "Zellie",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Aaron",
    "LastName": "Xavier"
  },
  {
    "FirstName": "Mike",
    "LastName": "Smith"
  }
]

Omnistudio SUM function

Returns the sum of a list or a JSON array of numeric values.

Signature

SUM(list)

Return Value

Number

Parameters

Parameter	Data Type	Necessity	Description
`list`	List of numbers	Required	A comma-separated list of numeric values or a list of numeric values from a JSON array.

List Example

Formula: SUM(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Return value: 55

JSON Array Example

Sample data:

"List": [ 
  {
    "Item": 3.5
  },
  {
    "Item": 4.25
  },
  {
    "Item": 5.75
  }
]

Formula: SUM(List:Item)

Formula: SUM(%List:Item%)

Return value: 13.5

Did this article solve your issue?

Let us know so we can improve!

Product Area

Feature Impact

Edition

Experience

Omnistudio List and Array Functions

Omnistudio AVG Function

Signature

Return Value

Parameters

Omnistudio FILTER Function

Signature

Return Value

Parameters

Omnistudio LIST Function

Signature

Return Value

Parameters

Omnistudio LISTMERGE Function

Signature

Return Value

Parameters

Omnistudio LISTMERGEPRIMARY Function

Signature

Return Value

Parameters

Omnistudio LISTSIZE Function

Signature

Return Value

Parameters

Omnistudio MAPTOLIST Function

Signature

Return Value

Parameters

Omnistudio MAX Function

Signature

Return Value

Parameters

Omnistudio MIN function

Signature

Return Value

Parameters

Omnistudio SORTBY Function

Signature

Return Value

Parameters

Omnistudio SUM function

Signature

Return Value

Parameters