1. SQL Reference Guide
  2. Built-in Functions
  3. Functions on Complex Values

Functions on Complex Values

size function

Returns the number of fields/entries of a complex item (array, map, record). The function accepts an empty sequence as argument, in which case it will return the empty sequence. The function will return NULL if its input is NULL. The result type is Integer.

Syntax:
integer? size(any?)

Semantics:

  • any: The any?element in the above syntax is a complex parameter. The size function accepts any complex parameter such as an array, map, or record. Although the parameter type appears as any?, the function raises an error if the given item is not complex.
  • return type: Integer

Example:

Every passenger usually carries one or more pieces of luggage during their travel. In the airline application, you can determine the number of bags owned by a passenger using the size function.
SELECT
fullName AS NAME,
size(bagInfo) AS BagCount
FROM BaggageInfo;

Explanation:

In this example, the information on all the luggage owned by each a passenger is available in the bagInfo array of each passenger record. The size function returns the size of the bagInfo array, which indicates the number of bags. If a passenger owns two pieces of luggage, the bagInfo array has two items and the size function returns the value as 2.

Output:
{"NAME":"Adelaide Willard","BagCount":1}
{"NAME":"Raymond Griffin","BagCount":1}
{"NAME":"Henry Jenkins","BagCount":1}
{"NAME":"Lucinda Beckman","BagCount":1}
{"NAME":"Michelle Payne","BagCount":1}
{"NAME":"Joanne Diaz","BagCount":1}
{"NAME":"Mary Watson","BagCount":1}
{"NAME":"Gerard Greene","BagCount":1}
{"NAME":"Fallon Clements","BagCount":1}
{"NAME":"Kendal Biddle","BagCount":1}
{"NAME":"Elane Lemons","BagCount":1}
{"NAME":"Adam Phillips","BagCount":1}
{"NAME":"Lorenzo Phil","BagCount":2}
{"NAME":"Omar Harvey","BagCount":1}
{"NAME":"Lisbeth Wampler","BagCount":1}
{"NAME":"Dierdre Amador","BagCount":1}
{"NAME":"Teena Colley","BagCount":1}
{"NAME":"Rosalia Triplett","BagCount":1}
{"NAME":"Zulema Martindale","BagCount":1}
{"NAME":"Doris Martin","BagCount":1}
{"NAME":"Zina Christenson","BagCount":1}

In the above query, since the bagInfo array contains one JSON document for each bag checked in by a passenger, the Bagcount in this query displays the number of bags that each passenger has checked into their flight.