Functions on Sequences

seq_concat function

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. This function accepts any data type as the input argument. If the input is a scalar, then the input item is treated as a sequence of size 1.

Syntax:

any* seq_concat(any*, ...)

Semantics:

any: The any? element in the above syntax can be of any data type. The seq_concat function accepts any number of input arguments. The arguments have to be in a comma-separated format.
return type: any type

Example 1:

In the TV streaming application, you can offer suggestions to the users regarding upcoming shows. This is usually based on the shows they have already watched or the genres they prefer.

SELECT acct_id, 

concat(stream_acct.acct_data[].firstName, ' ',stream_acct.acct_data[].lastName) AS Fullname,
seq_concat(stream_acct.acct_data[].contentStreamed[].showName) AS Uwatched,
seq_concat(stream_acct.acct_data[].contentStreamed[].genres) AS Uprefer
FROM stream_acct
ORDER BY acct_id

Explanation:

In this example, the seq_concat function lists all the watched shows and their genres as a comma-separated list.

Output:

{"acct_id":1,"Fullname":"Adam Phillips","Uwatched":["At the Ranch","Bienvenu"],"Uprefer":[["action","crime","spanish"],["comedy","french"]]}

{"acct_id":2,"Fullname":"Adelaide Willard","Uwatched":"Bienvenu","Uprefer":["comedy","french"]}

The concat function is also used in the query to concatenate the two strings, firstName and lastName and display it as a single object Fullname in the output. For more details, see concat Function.

Example 2:

In this TV streaming application example, you can retrieve the details of the shows watched by users.

SELECT

    concat(stream_acct.acct_data[].firstName, ' ',stream_acct.acct_data[].lastName) AS Fullname,
    CASE
        WHEN exists stream_acct.acct_data[].contentStreamed[].showId
        THEN seq_concat(stream_acct.acct_data[].contentStreamed[0].showName, stream_acct.acct_data[0].contentStreamed[0].seriesInfo[0].episodes[0], stream_acct.acct_data[0].contentStreamed[0].seriesInfo[0].episodes[1],stream_acct.acct_data[].contentStreamed[1].showName, stream_acct.acct_data[0].contentStreamed[0].seriesInfo[1].episodes[0], stream_acct.acct_data[0].contentStreamed[0].seriesInfo[1].episodes[1])
        ELSE "Start streaming your favorite shows here"
    END AS Showdetails 
FROM stream_acct WHERE acct_id=1

Explanation:

If a user has watched any show, the details are stored in the contentSreamed JSON field. The above query retrieves the date of streaming, details of the episode, the duration, and the time elapsed for each show. The seq_concat function is used to concatenate and display all the details in the output.

Output:

{"Fullname":"Adam Phillips","Showdetails":["At the Ranch",{"date":"2022-04-18","episodeID":20,"lengthMin":85,"minWatched":85},{"date":"2022-04-18","episodeID":30,"lengthMin":60,"minWatched":60},"Bienvenu",{"date":"2022-04-25","episodeID":40,"lengthMin":50,"minWatched":50},{"date":"2022-04-27","episodeID":50,"lengthMin":45,"minWatched":30}]}

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*)

seq_distinct function

Returns the distinct values of its input sequence.

Syntax:

any* seq_distinct(any*)

Semantics:

any: The seq_distinct function accepts any parameter type as the input argument.
return type: any type

Example:

Consider an application that maintains the information of the users. Create the schema for users table as follows:

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 the following user record into the table.

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
)

Explanation:

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.

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

Output:

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

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.