List Functions¶

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.

AT ¶

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

Parameters:

sequence – A sequence or collection
index – Zero-based integer index position

Return type:

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

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

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

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

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

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

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

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

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

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

COLLECT_LIST ¶

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.

Parameters:

expression – An expression to collect from each row
BY (ORDER) – Optional ordering clause to sort the collected elements

Return type:

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

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

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

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

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

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

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

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.

FIRST ¶

first(sequence)¶

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

Parameters:: sequence – A sequence or collection expression
Return type:: 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

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

This query returns 1, the first element.

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

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

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

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

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

SELECT first({}) AS first_element;

This query returns NULL when the sequence is empty.

Example 7: first() with repeated elements

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

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

LAST ¶

last(sequence)¶

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

Parameters:: sequence – A sequence or collection expression
Return type:: 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

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

This query returns 3, the last element.

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

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

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

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

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

SELECT last({}) AS last_element;

This query returns NULL when the sequence is empty.

Example 7: last() with repeated elements

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

This query returns 3, the last element.

LIST_COMP ¶

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.

Parameters:

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

Return type:

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

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

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

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

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

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

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

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.

RANGE ¶

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.

Parameters:

start – Starting value of the range (inclusive, rounded to nearest integer if float)
end – Ending value of the range (inclusive, rounded to nearest integer if float)
step – Increment value (rounded to nearest integer if float)

Return type:

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

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

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

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

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

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

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

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

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

REDUCE ¶

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.

Parameters:

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

Return type:

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

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

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

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

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

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

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

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.

SIZE ¶

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.

Parameters:: sequence – A sequence or collection expression
Return type:: 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

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

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

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

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

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

SELECT size({}) AS size_result;

This query returns 0 for an empty sequence.

Example 7: Size of a sequence with NULL values

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

This query returns 4, as NULL values are counted.

TAIL ¶

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.

Parameters:: sequence – A sequence or collection expression
Return type:: 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

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

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

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

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

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

SELECT TAIL({}) AS tail_l;

This query returns an empty sequence {}.

Example 7: TAIL with sequence with one element

SELECT TAIL({1}) AS tail_l;

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

List Functions¶

Overview ¶

AT ¶

Examples¶

Error Conditions¶

See Also¶

COLLECT_LIST ¶

Examples¶

See Also¶

FIRST ¶

Examples¶

See Also¶

LAST ¶

Examples¶

See Also¶

LIST_COMP ¶

Examples¶

See Also¶

RANGE ¶

Examples¶

See Also¶

REDUCE ¶

Examples¶

See Also¶

SIZE ¶

Examples¶

See Also¶

TAIL ¶

Examples¶

See Also¶