JSONARRAYSIZE

Owned by Juliane Wetzel
Feb 07, 2020
1 min read

Syntax

JSONARRAYSIZE(<string containing a JSON array>)

Description

Returns the number of elements of the selected JSON arrays.

Examples

Given the following data:

KeyName
Group1Andy
Group1Alba
Group1Anna
Group1Affa
Group2Jeff
Group2June
Group2Jack
Group3Rick
Group3Rode
Group4

Molly

First create a group using GROUPBY(#RawData!Key).

Group
Group1
Group2
Group3
Group4

Next create a list with GROUPCONCAT(#RawData!Name).

GroupName_List
Group1[Andy, Alba, Anna, Affa]
Group2[Jeff, June, Jack]
Group3[Rick, Rode]
Group4[Molly]

Use that list to create a JSON array with TOJSON(#sheet1!Name_List).

GroupName_ListName_JSON
Group1[Andy, Alba, Anna, Affa]["Andy","Alba","Anna","Affa"]
Group2[Jeff, June, Jack]["Jeff","June","Jack"]
Group3[Rick, Rode]["Rick","Rode"]
Group4[Molly]["Molly"]

Then use JSONARRAYSIZE(#Sheet1!Name_JSON), and the results are the the number of elements in the corresponding JSON array.

GroupName_ListName_JSONJSONARRAYSIZE returns
Group1[Andy, Alba, Anna, Affa]["Andy","Alba","Anna","Affa"]4
Group2[Jeff, June, Jack]["Jeff","June","Jack"]3
Group3[Rick, Rode]["Rick","Rode"]2
Group4[Molly]["Molly"]1