Functions on Sequences

A sequence is the result of any expression that returns zero or more items.

This section briefly discusses the built-in functions on sequences:

seq_concat

This function evaluates its arguments and concatenates the sequences they return into a single sequence. If an input is a scalar, it is treated as a sequence of size 1.

Example: 
SELECT p.id, seq_concat(p.firstName, p.lastName, p.address.city, p.address.phones[].number) AS UserDetails FROM Persons  p WHERE p.id = 3;

Explanation: This query returns the id and a combined sequence of the user's first name, last name, city, and phone numbers. The unbox operator '[]' is used to flatten the array of phone objects and extract the phone numbers into a sequence that seq_concat can process.

Output: 
{"id":3,"UserDetails":["John","Morgan","Middleburg",1234079,2066401]}

seq_distinct

This function returns the distinct values from the input sequence, eliminating duplicates.

Example: 
SELECT $area,count(*) AS cnt FROM Persons p, seq_distinct(p.address.phones[].areacode) AS $area GROUP BY $area;

Explanation: This query unnests the area codes from the phones array and then uses seq_distinct() to ensure that a user is counted only once per area code, even if they have multiple phone numbers with the same code.

Output: 
{"area":201,"cnt":1}
{"area":305,"cnt":2}
{"area":339,"cnt":3}
{"area":423,"cnt":1}
4 rows returned

seq_transform

This function transforms an input sequence to another sequence. The first argument is an expression that generates the sequence to be transformed (the input sequence) and the second argument is a mapper expression that is computed for each item of the input sequence. The result of the seq_transform expression is the concatenation of sequences produced by each evaluation of the mapper expression. The mapper expression can access the current input item using the $ variable.

Example: 
select p.firstname,seq_transform(p.address.phones[],{concat($.type,"phone"):concat($.areacode,"-",$.number)}) AS CONTACT_INFO  FROM Persons p;

Explanation: In this query, you concatenate the areacode and number fields for each phone and get a flat array of these as the contact information of each person.

Output: 
{"firstname":"Dana","CONTACT_INFO":[{"work phone":"201-3213267"},{"work phone":"201-8765421"},{"home phone":"339-3414578"}]}
{"firstname":"David","CONTACT_INFO":{"home phone":"423-8634379"}}
{"firstname":"Peter","CONTACT_INFO":[{"work phone":"339-4120211"},{"work phone":"339-8694021"},{"home phone":"339-1205678"},{"home phone":"305-8064321"}]}
{"firstname":"John","CONTACT_INFO":[{"work phone":"305-1234079"},{"home phone":"305-2066401"}]}
{"firstname":"John","CONTACT_INFO":{"home phone":"339-1684972"}}

5 rows returned

Sequence Aggregate Functions

These functions perform calculations across all items within an input sequence, and return a single aggregated result.

Table 12-2 Sequence Aggregate Functions

Function Description Example Additional Details
seq_count Returns the count of items in the sequence 
SELECT seq_count(p.connections[]) AS Count FROM Persons p WHERE id = 4;
Output:
{"Count":4}
  • Returns 0 if it is an empty sequence, else returns count of all items in the sequence.
seq_sum Returns the sum of all numeric items in the sequence 
SELECT seq_sum(p.connections[]) AS Sum FROM Persons p WHERE id = 4;
Output:
{"Sum":11}
  • Skips non-numeric items and considers only the numeric items.
  • Returns null if all items in the sequence are non-numeric.
seq_avg Returns the average of all numeric items in the sequence 
SELECT seq_avg(p.connections[]) AS Avg FROM Persons p WHERE id = 4;
Output:
{"Avg":2.75}
  • Skips non-numeric items and considers only the numeric items.
  • Returns null if all items in the sequence are non-numeric.
seq_min Returns the minimum atomic value among all items in the sequence 
SELECT seq_min(p.connections[]) AS Min FROM Persons p WHERE id = 4;
Output:
{"Min":1}
  • Can perform comparison only if all items in the sequence are homogenous or belong to the same data type family.
  • Behavior for various atomic data types:
    • String - Does a lexicographical comparison.
    • Boolean - FALSE is considered less than TRUE.
    • Binary - Does a raw byte-by-byte comparison treating each byte as a numerical value between 0 and 255.
    • Enum - Sorts based on numerical index.
    • Timestamp - Sorts from earliest point in time to latest.
seq_max Returns the maximum atomic value among all items in the sequence 
SELECT seq_max(p.connections[]) AS Min FROM Persons p WHERE id = 4;
Output:
{"Max":5}
  • Can perform comparison only if all items in the sequence are homogenous or belong to the same data type family.
  • Behavior for various atomic data types:
    • String - Does a lexicographical comparison.
    • Boolean - FALSE is considered less than TRUE.
    • Binary - Does a raw byte-by-byte comparison treating each byte as a numerical value between 0 and 255.
    • Enum - Sorts based on numerical index.
    • Timestamp - Sorts from earliest point in time to latest.