Functions on Sequences

any* seq_concat(any*, ...)

seq_concat is a variadic function: it can have any number of arguments. It simply evaluates its arguments (if any) in the order they are listed in the argument list, and concatenates the sequences returned by these arguments.

In addition to the above there are also the following aggregate functions on sequences. They are described in the Sequence Aggregate Functions section.

• long seq_count(any*)
• number seq_sum(any*)
• number seq_avg(any*)
• any_atomic seq_min(any*)
• any_atomic seq_max(any*)

any* seq_distinct(any*)

Returns the distinct values of its input sequence.

Example:

Consider Users table providing information about users.

CREATE TABLE users ( id INTEGER,
                     firstName STRING,
                     lastName STRING,
                     otherNames ARRAY(RECORD(first STRING, last STRING)),
                     age INTEGER,
                     income INTEGER,
                     address JSON,
                     connections ARRAY(INTEGER),
                     expenses MAP(INTEGER),
PRIMARY KEY (id) );

INSERT INTO users VALUES ( 
   10,   
   "John",
   "Smith",
   [ {"first" : "Johny", "last" : "BeGood"} ],
   22,
   45000,
   {
      "street" : "Pacific Ave",
      "number" : 101,
      "city" : "Santa Cruz",
      "state" : "CA",
      "zip" : 95008,
      "phones" : [
         { "area" : 408, "number" : 4538955, "kind" : "work" },
         { "area" : 831, "number" : 7533341, "kind" : "home" },
         { "area" : 831, "number" : 7533382, "kind" : "mobile" }
      ]
   },
   [ 30, 55, 43 ],
   DEFAULT
);

A user may have multiple phone numbers in the same area code. To determine the number of users having phone numbers in different area codes, a particular user should be counted only once ( even if the user has more than one phone number with the same area code).

This is possible using the seq_distinct() function, which eliminates duplicate values from its input sequence. The user John Smith has three phone numbers, two of which have the same area code. However, only distinct area codes are fetched in the query below:

SELECT $area, count(*) AS cnt
FROM Users u, seq_distinct(u.address.phones.area) AS $area
GROUP BY $area

{"area":408,"cnt":1}
{"area":831,"cnt":1}