Previous Next JavaScript must be enabled to correctly display this content

## 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 12-2 From the TV streaming application, fetch the shows and genres viewed by users

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 12-3 Fetch 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}]}

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.

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 12-4 Determine the number of users having phone numbers in different area codes

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.