:meta-keywords: coradb list, list comprehension, list_comp, first, last, tail, at, size, range, reduce, collect_list, database list functions, sequence functions, aggregation
:meta-description: CoraDB list functions for processing and transforming collections, including list comprehensions, sequence element access, and size operations.

:tocdepth: 3

.. _list-fn:

**************
List Functions
**************

.. contents::
   :depth: 1
   :local:

Overview
========

List functions provide operations for working with collections, including list comprehensions that allow you to transform and filter data from lists, sequences, and other collection types, as well as functions for accessing and manipulating sequence elements.

.. note::

    All list functions work with both graph tables (using MATCH queries) and relational tables (using JOIN queries). The examples in this section primarily use graph tables, but the same functions apply to relational tables with identical syntax.


.. _fn-at:

AT
==

.. function:: AT(sequence, index)

    The **AT()** function returns the element at the specified index position in a sequence. Indexing is zero-based (the first element is at index 0).

    :param sequence: A sequence or collection
    :param index: Zero-based integer index position
    :rtype: The type of the element at the specified index, or NULL if index is out of bounds

    .. note::

        - Indexing is **zero-based**: the first element is at index 0
        - Returns **NULL** if the index is out of bounds (negative or >= sequence size)
        - Can be used with nested sequences by chaining AT() calls

**Setup: Create test graph data**

.. code-block:: sql

    CREATE EDGE TABLE Knows (
        id INT NOT NULL AUTO_INCREMENT, 
        source INT, 
        destination INT, 
        remark STRING
    );

    CREATE VERTEX TABLE Person (
        id INT, 
        firstName STRING, 
        lastName STRING, 
        age INT, 
        col1 SEQUENCE(integer)
    );

    INSERT VERTEX INTO Person VALUES (0, 'Jerry', 'Madonna', 10, {1, 2, 3});
    INSERT VERTEX INTO Person VALUES (1, 'John', 'Doe', 20, {4, 5, 6});
    INSERT VERTEX INTO Person VALUES (2, 'Alice', 'Johnson', 40, {7, 8, 9});
    INSERT VERTEX INTO Person VALUES (3, NULL, NULL, NULL, {null, null, null});

    INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 0) 
                  TO (SELECT Person FROM Person WHERE id = 1) 
                INTO Knows VALUES (1, 0, 1, 'best friend');
    INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 1) 
                  TO (SELECT Person FROM Person WHERE id = 2) 
                INTO Knows VALUES (2, 1, 2, 'worst friend');
    INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 2) 
                  TO (SELECT Person FROM Person WHERE id = 0) 
                INTO Knows VALUES (3, 2, 0, 'most hated');
    INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 2) 
                  TO (SELECT Person FROM Person WHERE id = 1) 
                INTO Knows VALUES (4, 2, 1, 'close friend');
    INSERT EDGE FROM (SELECT Person FROM Person WHERE id = 0) 
                  TO (SELECT Person FROM Person WHERE id = 3) 
                INTO Knows VALUES (5, 0, 3, 'distant friend');

Examples
--------

**Example 1: AT with a constant sequence**

.. code-block:: sql

    SELECT AT({1, 2, 3}, 1) AS element_at_index;

This query returns ``2``, the element at index 1.

**Example 2: AT with a collected list from graph query**

.. code-block:: sql

    SELECT AT(collect_list(b.id), 1) AS second_element
    FROM MATCH (a:Person)->(b:Person);

This query returns the second element (index 1) from the collected list of Person IDs.

**Example 3: AT with nested sequences**

.. code-block:: sql

    SELECT AT(AT({ {1,2}, {3,4}, {5,6} }, 1), 0) AS first_element_of_second_sequence;

This query returns ``3``, accessing the first element of the second nested sequence.

**Example 4: AT with a sequence of dates**

.. code-block:: sql

    SELECT AT({DATE '2025-01-01', DATE '2025-01-02', DATE '2025-01-03'}, 0) AS element_date_sequence;

This query returns ``DATE '2025-01-01'``, the first date in the sequence.

**Example 5: AT with mixed types including NULL**

.. code-block:: sql

    SELECT AT({1, NULL, 'three', 4.0}, 2) AS element_mixed_with_null;

This query returns ``'three'``, the element at index 2 in a mixed-type sequence.

Error Conditions
----------------

**Example 6: AT with out of bounds index**

.. code-block:: sql

    SELECT AT({1, 2, 3}, 4) AS element_out_of_bounds;

This query returns an error because index 4 is beyond the sequence length.

**Example 7: AT with negative index**

.. code-block:: sql

    SELECT AT({1, 2, 3}, -1) AS element_negative_index;

This query returns an error because negative indices are not supported.

See Also
--------

*   :ref:`first() <fn-first>` - Returns the first element of a sequence
*   :ref:`last() <fn-last>` - Returns the last element of a sequence
*   :ref:`tail() <fn-tail>` - Returns all elements except the first from a sequence
*   :ref:`size() <fn-size>` - Returns the number of elements in a sequence
*   :ref:`range() <fn-range>` - Generates a sequence of integers
*   :ref:`reduce() <fn-reduce>` - Reduces a sequence to a single value using an accumulator
*   :ref:`list_comp() <fn-list-comp>` - Performs list comprehension operations
*   **SEQUENCE** data type - Native sequence/array type in CoraDB


.. _fn-collect-list:

COLLECT_LIST
============

.. function:: collect_list(expression [ORDER BY ...])

    The **collect_list()** function is an aggregate function that collects values from multiple rows into a single list. It can be used with or without GROUP BY clauses and supports ordering of collected elements.

    :param expression: An expression to collect from each row
    :param ORDER BY: Optional ordering clause to sort the collected elements
    :rtype: LIST

    .. note::

        The **collect_list()** function is particularly useful for aggregating data in graph queries and can be combined with other list functions like **first()**, **last()**, **size()**, and **reduce()**.

Examples
--------

**Example 1: Collect list with database attribute**

.. code-block:: sql

    SELECT collect_list(p1.firstName) AS first_names
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person);

This query collects all first names from persons who have outgoing KNOWS relationships.

**Example 2: Collect list with ORDER BY inside collect_list**

.. code-block:: sql

    SELECT
        p1.id,
        p1.firstName,
        collect_list(p2.firstName ORDER BY p2.firstName DESC) AS ordered_friends
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
    GROUP BY p1.id, p1.firstName;

This query collects friends' names ordered in descending alphabetical order for each person.

**Example 3: Collect list with ORDER BY and NULL values**

.. code-block:: sql

    SELECT
        p1.id,
        p1.firstName,
        collect_list(COALESCE(p2.firstName, 'Unknown') ORDER BY 1 ASC) AS ordered_friends_with_nulls
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
    GROUP BY p1.id, p1.firstName;

This query handles NULL values by replacing them with 'Unknown' and orders the results.

**Example 4: Collect list without GROUP BY**

.. code-block:: sql

    SELECT collect_list(p1.firstName) AS all_first_names
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person);

This query collects all first names into a single list without grouping.

**Example 5: Collect list with mixed attribute types**

.. code-block:: sql

    SELECT collect_list(CONCAT(p1.firstName, ' - ', p1.lastName)) AS mixed_attributes
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person);

This query creates a list by concatenating multiple attributes.

**Example 6: Collect list with no matching rows**

.. code-block:: sql

    SELECT collect_list(p1.firstName) AS first_names
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
    WHERE p1.id = 999;

This query returns NULL when there are no matching rows.

**Example 7: Collect list with all NULL values**

.. code-block:: sql

    SELECT collect_list(p1.firstName) AS first_names
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
    WHERE p1.firstName IS NULL;

This query collects NULL values into the list if all values are NULL.

See Also
--------

*   :ref:`list_comp() <fn-list-comp>` - Performs list comprehension operations
*   :ref:`first() <fn-first>` - Returns the first element of a sequence
*   :ref:`last() <fn-last>` - Returns the last element of a sequence
*   :ref:`at() <fn-at>` - Returns the element at a specific index in a sequence
*   :ref:`size() <fn-size>` - Returns the number of elements in a sequence
*   :ref:`range() <fn-range>` - Generates a sequence of integers within a range
*   :ref:`reduce() <fn-reduce>` - Reduces a sequence to a single value using an accumulator
*   :ref:`tail() <fn-tail>` - Returns all elements except the first from a sequence
*   **Aggregation functions** - SQL standard aggregation like SUM, AVG, MAX, MIN


.. _fn-first:

FIRST
=====

.. function:: first(sequence)

    The **first()** function returns the first element from a sequence. If the sequence is empty, it returns NULL.

    :param sequence: A sequence or collection expression
    :rtype: Element of the same type as sequence elements

    .. note::

        The **first()** function is commonly used with functions that return sequences, such as **nodes()**, **relationships()**, or **collect_list()**.

Examples
--------

**Example 1: first() with a constant sequence**

.. code-block:: sql

    SELECT first({1, 2, 3}) AS first_element;

This query returns ``1``, the first element.

**Example 2: first() with collected list from graph query**

.. code-block:: sql

    SELECT first(collect_list(b.id)) AS first_element
    FROM MATCH (a:Person)->(b:Person);

This query collects IDs from matched vertices and returns the first one.

**Example 3: first() with nodes() function**

.. code-block:: sql

    SELECT property_of(first(nodes(pth)), lastName, Person) AS first_node
    FROM MATCH pth=(p1:Person)-[:Knows]->(p2:Person);

This query extracts the lastName of the first node in each path.

**Example 4: first() with grouping**

.. code-block:: sql

    SELECT p.id AS group_key,
           first(collect_list(k.destination)) AS first_element
    FROM MATCH (p:Person)-[k:Knows]->(b:Person)
    GROUP BY p.id
    ORDER BY p.id;

This query demonstrates first() with group-based aggregation.

**Example 5: first() with mixed types including NULL**

.. code-block:: sql

    SELECT first({1, NULL, 'three', 4.0}) AS first_element;

This query returns ``1``, the first element in the mixed-type sequence.

**Example 6: first() with empty sequence**

.. code-block:: sql

    SELECT first({}) AS first_element;

This query returns ``NULL`` when the sequence is empty.

**Example 7: first() with repeated elements**

.. code-block:: sql

    SELECT first({1,1,1,2,2,3}) AS first_element;

This query returns ``1``, the first element even when duplicated.

See Also
--------

*   :ref:`last() <fn-last>` - Returns the last element of a sequence
*   :ref:`tail() <fn-tail>` - Returns all elements except the first from a sequence
*   :ref:`at() <fn-at>` - Returns the element at a specific index in a sequence
*   :ref:`size() <fn-size>` - Returns the number of elements in a sequence
*   :ref:`range() <fn-range>` - Generates a sequence of integers within a range
*   :ref:`reduce() <fn-reduce>` - Reduces a sequence to a single value using an accumulator
*   :ref:`list_comp() <fn-list-comp>` - Performs list comprehension operations
*   :ref:`collect_list() <fn-collect-list>` - Aggregates values into a list
*   :ref:`nodes() <fn-nodes>` - Returns a list of vertices in a path (for graph queries)
*   :ref:`relationships() <fn-relationships>` - Returns a list of edges in a path (for graph queries)


.. _fn-last:

LAST
====

.. function:: last(sequence)

    The **last()** function returns the last element from a sequence. If the sequence is empty, it returns NULL.

    :param sequence: A sequence or collection expression
    :rtype: Element of the same type as sequence elements

    .. note::

        The **last()** function is commonly used with functions that return sequences, such as **nodes()**, **relationships()**, or **collect_list()**.

Examples
--------

**Example 1: last() with a constant sequence**

.. code-block:: sql

    SELECT last({1, 2, 3}) AS last_element;

This query returns ``3``, the last element.

**Example 2: last() with collected list from graph query**

.. code-block:: sql

    SELECT last(collect_list(b.id)) AS last_element
    FROM MATCH (a:Person)->(b:Person);

This query collects IDs from matched vertices and returns the last one.

**Example 3: last() with nodes() function**

.. code-block:: sql

    SELECT property_of(last(nodes(pth)), lastName, Person) AS last_node
    FROM MATCH pth=(p1:Person)-[:Knows]->(p2:Person);

This query extracts the lastName of the last node in each path.

**Example 4: last() with grouping**

.. code-block:: sql

    SELECT p.id AS group_key,
           last(collect_list(k.destination)) AS last_element
    FROM MATCH (p:Person)-[k:Knows]->(b:Person)
    GROUP BY p.id
    ORDER BY p.id;

This query demonstrates last() with group-based aggregation.

**Example 5: last() with mixed types including NULL**

.. code-block:: sql

    SELECT last({1, NULL, 'three', 4.0}) AS last_element;

This query returns ``4.0``, the last element in the mixed-type sequence.

**Example 6: last() with empty sequence**

.. code-block:: sql

    SELECT last({}) AS last_element;

This query returns ``NULL`` when the sequence is empty.

**Example 7: last() with repeated elements**

.. code-block:: sql

    SELECT last({1,1,1,2,2,3}) AS last_element;

This query returns ``3``, the last element.

See Also
--------

*   :ref:`first() <fn-first>` - Returns the first element of a sequence
*   :ref:`tail() <fn-tail>` - Returns all elements except the first from a sequence
*   :ref:`at() <fn-at>` - Returns the element at a specific index in a sequence
*   :ref:`size() <fn-size>` - Returns the number of elements in a sequence
*   :ref:`range() <fn-range>` - Generates a sequence of integers within a range
*   :ref:`reduce() <fn-reduce>` - Reduces a sequence to a single value using an accumulator
*   :ref:`list_comp() <fn-list-comp>` - Performs list comprehension operations
*   :ref:`collect_list() <fn-collect-list>` - Aggregates values into a list
*   :ref:`nodes() <fn-nodes>` - Returns a list of vertices in a path (for graph queries)
*   :ref:`relationships() <fn-relationships>` - Returns a list of edges in a path (for graph queries)


.. _fn-list-comp:

LIST_COMP
=========

.. function:: list_comp(variable IN collection | expression)

    The **list_comp()** function performs list comprehension operations, allowing you to transform elements from a collection by applying an expression to each element.

    :param variable: A variable name to represent each element in the collection
    :param collection: A collection expression (list, sequence, or set)
    :param expression: An expression applied to each element
    :rtype: LIST

    .. note::

        List comprehension syntax follows the pattern: ``list_comp(x IN collection | expression_using_x)`` where x is a variable representing each element.

Examples
--------

**Example 1: List comprehension with database attribute**

.. code-block:: sql

    SELECT list_comp(x IN {p1.firstName} | x) AS first_names
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person);

This query collects first names of all persons who have a KNOWS relationship.

**Example 2: List comprehension with mixed attribute types**

.. code-block:: sql

    SELECT list_comp(x IN {CONCAT(p1.firstName, ' - ', p1.age)} | x) AS mixed_attributes
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person);

This query creates a list of strings combining first names and ages.

**Example 3: List comprehension with ORDER BY inside collect_list**

.. code-block:: sql

    SELECT
        p1.id,
        p1.firstName,
        list_comp(x IN collect_list(p2.firstName ORDER BY p2.firstName DESC) | x) AS ordered_friends
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
    GROUP BY p1.id, p1.firstName;

This query collects friends' names in descending order for each person.

**Example 4: List comprehension in subquery**

.. code-block:: sql

    SELECT
        subquery.id,
        subquery.firstName,
        subquery.collected_first_names
    FROM (
        SELECT
            p1.id,
            p1.firstName,
            list_comp(x IN {p1.firstName} | x) AS collected_first_names
        FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
        GROUP BY p1.id, p1.firstName
    ) AS subquery
    WHERE subquery.collected_first_names IS NOT NULL;

This query uses list comprehension in a subquery and filters the results.

**Example 5: List comprehension with constant collection**

.. code-block:: sql

    SELECT list_comp(x IN {1, 2, 3} | x * 2) AS doubled_values;

This query processes a constant collection and applies an expression to each element.

**Example 6: List comprehension with empty collection**

.. code-block:: sql

    SELECT list_comp(x IN {} | x) AS empty_result;

This query returns an empty sequence ``{}`` when the input collection is empty.

**Example 7: List comprehension with NULL values**

.. code-block:: sql

    SELECT list_comp(x IN {NULL, 0, 'string', '4'} | x) AS with_nulls;

This query processes a collection containing NULL values, including them in the result.

See Also
--------

*   :ref:`first() <fn-first>` - Returns the first element of a sequence
*   :ref:`last() <fn-last>` - Returns the last element of a sequence
*   :ref:`tail() <fn-tail>` - Returns all elements except the first from a sequence
*   :ref:`size() <fn-size>` - Returns the number of elements in a sequence
*   :ref:`at() <fn-at>` - Returns the element at a specific index in a sequence
*   :ref:`range() <fn-range>` - Generates a sequence of integers within a range
*   :ref:`reduce() <fn-reduce>` - Reduces a sequence to a single value using an accumulator
*   :ref:`collect_list() <fn-collect-list>` - Aggregates values into a list
*   **COALESCE()** - Returns the first non-NULL value
*   :ref:`nodes() <fn-nodes>` - Returns a list of vertices in a path (for graph queries)
*   :ref:`relationships() <fn-relationships>` - Returns a list of edges in a path (for graph queries)
*   **SET, MULTISET, LIST** - Collection types in CoraDB


.. _fn-range:

RANGE
=====

.. function:: range(start, end, step)

    The **range()** function generates a sequence of integers from ``start`` to ``end`` with the specified ``step`` increment. The function handles both forward and reverse ranges, and automatically rounds floating-point arguments to the nearest integer.

    :param start: Starting value of the range (inclusive, rounded to nearest integer if float)
    :param end: Ending value of the range (inclusive, rounded to nearest integer if float)
    :param step: Increment value (rounded to nearest integer if float)
    :rtype: SEQUENCE OF INTEGER

    .. note::

        - The range is inclusive of ``start`` and inclusive of ``end``
        - If ``step`` is positive, generates an ascending sequence
        - If ``step`` is negative, generates a descending sequence
        - Returns an empty sequence if ``step`` direction is inconsistent with ``start``/``end``
        - Floating-point arguments are rounded to nearest integer (values >= 0.5 round up)

Examples
--------

**Example 1: Normal ascending range**

.. code-block:: sql

    SELECT range(1, 5, 1) AS rng;

This query returns ``{1, 2, 3, 4, 5}``, generating integers from 1 to 5.

**Example 2: Normal descending range**

.. code-block:: sql

    SELECT range(5, 1, -1) AS rng;

This query returns ``{5, 4, 3, 2, 1}``, generating integers from 5 down to 1.

**Example 3: Range with floating-point parameter**

.. code-block:: sql

    SELECT range(1.5, 11, 3) AS rng;

This query returns ``{2, 5, 8, 11}``, with start value ``1.5`` rounded to ``2``.

**Example 4: Range with different numeric types**

.. code-block:: sql

    SELECT range(30, 42, CAST(3 AS FLOAT)) AS rng;

This query returns ``{30, 33, 36, 39, 42}``, with FLOAT step converted to integer.

**Example 5: Range at boundary conditions**

.. code-block:: sql

    SELECT range(2147483640, 2147483646, 1) AS rng;

This query returns ``{2147483640, 2147483641, 2147483642, 2147483643, 2147483644, 2147483645, 2147483646}``, successfully generating values up to near INT_MAX.

**Example 6: Empty range - incongruent step direction**

.. code-block:: sql

    SELECT range(1, 5, -1) AS rng;

This query returns ``{}``, an empty sequence because positive step cannot reach from 1 to 5 going backwards.

**Example 7: Single value due to large increment**

.. code-block:: sql

    SELECT range(-5, 5, 11) AS rng;

This query returns ``{-5}``, only the start value as the increment exceeds the range.

See Also
--------

*   :ref:`first() <fn-first>` - Returns the first element of a sequence
*   :ref:`last() <fn-last>` - Returns the last element of a sequence
*   :ref:`tail() <fn-tail>` - Returns all elements except the first from a sequence
*   :ref:`size() <fn-size>` - Returns the number of elements in a sequence
*   :ref:`at() <fn-at>` - Returns the element at a specific index in a sequence
*   :ref:`reduce() <fn-reduce>` - Reduces a sequence to a single value using an accumulator
*   :ref:`list_comp() <fn-list-comp>` - Performs list comprehension operations
*   **SEQUENCE** data type - Native sequence/array type in CoraDB


.. _fn-reduce:

REDUCE
======

.. function:: reduce(accumulator = initial_value, variable IN collection | expression)

    The **reduce()** function reduces a collection to a single value by iteratively applying an expression that combines the accumulator with each element. This is a powerful aggregation tool for custom calculations beyond standard SQL aggregation functions.

    :param accumulator: A variable name representing the accumulated value, initialized with initial_value
    :param initial_value: The starting value for the accumulator
    :param variable: A variable name to represent each element in the collection
    :param collection: A collection expression (list, sequence, or set)
    :param expression: An expression that combines the accumulator and current element
    :rtype: Depends on the expression result type

    .. note::

        Reduce follows the pattern: ``reduce(v = init_value, x IN collection | v op x)`` where v accumulates the result and x represents each element.

Examples
--------

**Example 1: Sum with constant sequence**

.. code-block:: sql

    SELECT reduce(v = 0, x IN {1, 2, 3, 4, 5} | v + x) AS total;

This query sums a constant sequence using reduce, returning ``15``.

**Example 2: Sum of IDs using reduce with graph query**

.. code-block:: sql

    SELECT 
        p1.id AS person_id,
        reduce(v = 0, x IN collect_list(p2.id) | v + x) AS sum_of_ids
    FROM MATCH (p1:Person)<-[:Knows]-(p2:Person)
    WHERE p1.id = 1
    GROUP BY p1.id;

This query calculates the sum of IDs for all persons who know person with id=1.

**Example 3: Concatenation of names**

.. code-block:: sql

    SELECT reduce(v = '', x IN (SELECT collect_list(p.firstName) FROM MATCH (p:Person)-[:Knows]->(p2:Person)) | CONCAT(v, x)) AS concatenated_names;

This query concatenates all first names into a single string.

**Example 4: Reduce with dynamic initial value**

.. code-block:: sql

    SELECT 
        p1.id AS person_id,
        reduce(v = p1.age, x IN collect_list(p2.age) | v + x) AS total_combined_age
    FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
    WHERE p1.id = 1
    GROUP BY p1.id, p1.age;

This query starts the accumulator with p1.age and adds all connected person ages to it.

**Example 5: Reduce in subquery**

.. code-block:: sql

    SELECT
        subquery.id,
        subquery.total_combined_age
    FROM (
        SELECT
            p1.id,
            reduce(v = p1.age, x IN collect_list(p2.age) | v + x) AS total_combined_age
        FROM MATCH (p1:Person)-[:Knows]->(p2:Person)
        GROUP BY p1.id, p1.age
    ) AS subquery
    WHERE subquery.total_combined_age IS NOT NULL;

This query uses reduce in a subquery and filters results in the outer query.

**Example 6: Reduce with empty collection**

.. code-block:: sql

    SELECT reduce(v = 0, x IN {} | v + x) AS result;

This query returns ``0``, the initial value, when the collection is empty.

**Example 7: Reduce with NULL in collection**

.. code-block:: sql

    SELECT reduce(v = 0, x IN {1, NULL, 3} | v + x) AS result;

This query handles NULL values in the collection according to the expression logic.

See Also
--------

*   :ref:`list_comp() <fn-list-comp>` - Performs list comprehension operations
*   :ref:`first() <fn-first>` - Returns the first element of a sequence
*   :ref:`last() <fn-last>` - Returns the last element of a sequence
*   :ref:`size() <fn-size>` - Returns the number of elements in a sequence
*   :ref:`collect_list() <fn-collect-list>` - Aggregates values into a list
*   **Aggregation functions** - SQL standard aggregation like SUM, AVG, MAX, MIN


.. _fn-size:

SIZE
====

.. function:: size(sequence)

    The **size()** function returns the number of elements in a sequence or collection. It works with any collection type and returns an integer representing the count of elements.

    :param sequence: A sequence or collection expression
    :rtype: INTEGER

    .. note::

        The **size()** function counts all elements in the sequence, including NULL values. An empty sequence returns 0.

Examples
--------

**Example 1: Size of a constant sequence**

.. code-block:: sql

    SELECT size({2, 2, 3, 3}) AS size_result;

This query returns ``4``, counting all elements including duplicates.

**Example 2: Size of a collected list from graph query**

.. code-block:: sql

    SELECT size(collect_list(p.id)) AS size_result
    FROM MATCH (p:Person)-[:Knows]->(p2:Person);

This query returns the total number of persons with KNOWS relationships.

**Example 3: Size with subquery**

.. code-block:: sql

    SELECT size(
      (SELECT collect_list(p.id)
       FROM MATCH (p:Person)-[:Knows]->(p2:Person)
       WHERE p.age > 35)
    ) AS size_result;

This query counts how many people with age greater than 35 have KNOWS relationships.

**Example 4: Size with grouping**

.. code-block:: sql

    SELECT p.id AS group_key,
           size(collect_list(k.remark)) AS size_result
    FROM MATCH (p:Person)-[k:Knows]->(b:Person)
    GROUP BY p.id;

This query counts how many relationships each person has.

**Example 5: Size of nested sequences**

.. code-block:: sql

    SELECT size({{1, 2}, {3, 4}, {5, 6}}) AS size_result;

This query returns ``3``, counting the number of nested sequences (not the total elements within them).

**Example 6: Size of an empty sequence**

.. code-block:: sql

    SELECT size({}) AS size_result;

This query returns ``0`` for an empty sequence.

**Example 7: Size of a sequence with NULL values**

.. code-block:: sql

    SELECT size({1, NULL, 3, NULL}) AS size_result;

This query returns ``4``, as NULL values are counted.

See Also
--------

*   :ref:`first() <fn-first>` - Returns the first element of a sequence
*   :ref:`last() <fn-last>` - Returns the last element of a sequence
*   :ref:`tail() <fn-tail>` - Returns all elements except the first from a sequence
*   :ref:`at() <fn-at>` - Returns the element at a specific index in a sequence
*   :ref:`range() <fn-range>` - Generates a sequence of integers within a range
*   :ref:`reduce() <fn-reduce>` - Reduces a sequence to a single value using an accumulator
*   :ref:`list_comp() <fn-list-comp>` - Performs list comprehension operations
*   :ref:`collect_list() <fn-collect-list>` - Aggregates values into a list
*   **COUNT()** - Aggregation function for counting rows
*   :ref:`nodes() <fn-nodes>` - Returns a list of vertices in a path (for graph queries)
*   :ref:`relationships() <fn-relationships>` - Returns a list of edges in a path (for graph queries)


.. _fn-tail:

TAIL
====

.. function:: TAIL(sequence)

    The **TAIL()** function returns a new sequence containing all elements except the first element from the input sequence. If the sequence has one element, it returns an empty sequence. If the sequence is empty, it returns an empty sequence.

    :param sequence: A sequence or collection expression
    :rtype: SEQUENCE

    .. note::

        The **TAIL()** function can be nested to skip multiple elements from the beginning of a sequence. For example, ``TAIL(TAIL(sequence))`` skips the first two elements.

Examples
--------

**Example 1: TAIL with a constant sequence**

.. code-block:: sql

    SELECT TAIL({1, 2, 3}) AS tail_l;

This query returns ``{2, 3}``, removing the first element.

**Example 2: TAIL with collected list from graph query**

.. code-block:: sql

    SELECT TAIL(collect_list(b.id)) AS tail_l
    FROM MATCH (a:Person)->(b:Person);

This query collects IDs from matched vertices and returns all except the first.

**Example 3: TAIL with nested sequences**

.. code-block:: sql

    SELECT TAIL(TAIL({ {1,2}, {3,4}, {5,6}})) AS tail_l;

This query demonstrates nested TAIL operations, first removing the first sequence element, then removing the first element from the result.

**Example 4: TAIL with grouping**

.. code-block:: sql

    SELECT p.id AS group_key,
           TAIL(collect_list(k.destination)) AS tail_l
    FROM MATCH (p:Person)-[k:Knows]->(b:Person)
    GROUP BY p.id
    ORDER BY p.id;

This query demonstrates TAIL with group-based aggregation.

**Example 5: TAIL with mixed types including NULL**

.. code-block:: sql

    SELECT TAIL({1, NULL, 'three', 4.0}) AS tail_l;

This query returns ``{NULL, 'three', 4.0}``, demonstrating that TAIL works with heterogeneous sequences.

**Example 6: TAIL with empty sequence**

.. code-block:: sql

    SELECT TAIL({}) AS tail_l;

This query returns an empty sequence ``{}``.

**Example 7: TAIL with sequence with one element**

.. code-block:: sql

    SELECT TAIL({1}) AS tail_l;

This query returns an empty sequence ``{}``, as there are no elements after the first.

See Also
--------

*   :ref:`first() <fn-first>` - Returns the first element of a sequence
*   :ref:`last() <fn-last>` - Returns the last element of a sequence
*   :ref:`at() <fn-at>` - Returns the element at a specific index in a sequence
*   :ref:`size() <fn-size>` - Returns the number of elements in a sequence
*   :ref:`range() <fn-range>` - Generates a sequence of integers within a range
*   :ref:`reduce() <fn-reduce>` - Reduces a sequence to a single value using an accumulator
*   :ref:`list_comp() <fn-list-comp>` - Performs list comprehension operations
*   :ref:`collect_list() <fn-collect-list>` - Aggregates values into a list
*   :ref:`nodes() <fn-nodes>` - Returns a list of vertices in a path (for graph queries)
*   :ref:`relationships() <fn-relationships>` - Returns a list of edges in a path (for graph queries)