JSONARRAYSIZE
Syntax
JSONARRAYSIZE(<string containing a JSON array>)
Description
Returns the number of elements of the selected JSON arrays.
Examples
Given the following data:
Key | Name |
|---|---|
Group1 | Andy |
Group1 | Alba |
Group1 | Anna |
Group1 | Affa |
Group2 | Jeff |
Group2 | June |
Group2 | Jack |
Group3 | Rick |
Group3 | Rode |
Group4 | Molly |
First create a group using GROUPBY(#RawData!Key).
Group |
|---|
Group1 |
Group2 |
Group3 |
Group4 |
Next create a list with GROUPCONCAT(#RawData!Name).
Group | Name_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).
Group | Name_List | Name_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.
Group | Name_List | Name_JSON | JSONARRAYSIZE 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 |