Arrays

Array Support

NoQL supports many methods that perform operations on array fields. They can be used as part of select statements and queries.

NoQL uses sub-selects with a FROM array field to query array fields in collections. E.g.

Using sub-selects to query array fields

SELECT
    (SELECT * FROM Rentals WHERE staffId=2) AS t
FROM
    `customers`

Using $$ROOT as a column alias in a sub-select promotes the field to the root value of the result array.

Using '$$ROOT' as an alias in a sub-select

SELECT
    (SELECT filmId AS `$$ROOT` FROM Rentals WHERE staffId=2) AS t
FROM
    `customers`

Field references in array sub-selects

Array sub-selects compile to MongoDB $map / $filter. Inside those, NoQL has to choose whether a column means the current array element ($$this) or the parent document ($field / $$ROOT).

NoQL in the sub-select	Meaning	MongoDB
staffId	Current array element	$$this.staffId
`$favouriteFilms`	Parent document	$favouriteFilms
`$$ROOT.favouriteFilms`	Explicit root document	$$ROOT.favouriteFilms
`$this.staffId` or `$$this.staffId`	Legacy element workaround	$$this.staffId

Bare names are the normal way to read element fields. Prefix a path with $ (and wrap it in backticks) when you need a field from the outer document.

Bare names bind to the array element

SELECT
    (
        SELECT
            CASE
                WHEN numberOfTerms > 0 THEN premium * (12 / numberOfTerms)
                ELSE premium
            END AS annualPremium
        FROM policies
        WHERE status != 'Lead'
    ) AS activePremiums
FROM clients

premium, numberOfTerms and status all become $$this.… on each policies element.

Parent document path with $field

SELECT
    (
        SELECT *
        FROM Rentals
        WHERE INDEXOF_ARRAY(`$favouriteFilms`, filmId) >= 0
    ) AS favourites
FROM customers

Compiles to $indexOfArray: ["$favouriteFilms", "$$this.filmId"]. $favouriteFilms stays on the customer; filmId is the rental element.

Use >= 0 (or != -1) with INDEXOF_ARRAY. The function returns 0 when the value is the first match and -1 when it is missing.

Explicit root path with $$ROOT.field

SELECT
    (
        SELECT *
        FROM Rentals
        WHERE INDEXOF_ARRAY(`$$ROOT.favouriteFilms`, filmId) >= 0
    ) AS favourites
FROM customers

$$ROOT.favouriteFilms is equivalent to $favouriteFilms here and makes the outer-document intent obvious.

Mixing parent and element fields in the SELECT list

SELECT
    (
        SELECT
            filmId,
            `$customerId` AS customerId
        FROM Rentals
    ) AS t
FROM customers

filmId → $$this.filmId. `$customerId` → $customerId on the parent customer.

Backticks are required for $ paths

Identifiers that start with $ must be quoted: `$favouriteFilms`, `$$ROOT.favouriteFilms`, `$this.staffId`.

Do not use $this / $$this in new queries

`$this.field` and `$$this.field` are still accepted so existing queries keep working. New queries should use a bare field for the element and `$field` / `$$ROOT.field` for the parent.

$field and $$ROOT.field always mean the root document of the query. In a nested array sub-select they do not bind to an intermediate $map parent.

Slicing and sorting

Slicing the array is supported by limit and offset in queries

Slicing an array with limit and offset

SELECT
    (SELECT * FROM Rentals WHERE staffId=2 LIMIT 10 OFFSET 5) AS t
FROM
    `customers`

Sorting Arrays is supported in MongoDB 5.2+ and NoQL

Sorting Arrays is supported

SELECT id,
    (SELECT * FROM Rentals ORDER BY id DESC) AS totalRentals
FROM customers

Aggregation functions are not supported in a sub select

Aggregation functions are not supported in a sub select. For example, the following won't work sql --Wont'Work SELECT id, (SELECT count(*) AS count FROM Rentals) AS totalRentals FROM customers

UNWIND Function

UNWIND(array_expr)

NoQL has a high level unwind function that will unwind array fields. For Joins, the unwind join hint should be used.

UNWIND in SELECT

SELECT
  field1,
  UNWIND(arrFld) as arrFld
FROM
  test

Complex UNWIND

SELECT
    MERGE_OBJECTS(
        (SELECT
            t.CustomerID
            ,t.Name
        )
        ,t.Rental
        ) AS `$$ROOT`
FROM
    (SELECT
        id AS CustomerID
        ,`First Name` AS Name
        ,UNWIND(Rentals) AS Rental
    FROM customers) AS t

Supported Array Functions

ALL_ELEMENTS_TRUE

ALL_ELEMENTS_TRUE(array expr)

Returns true when all elements in the array are true.

Example ALL_ELEMENTS_TRUE usage

SELECT id,
    (CASE WHEN ALL_ELEMENTS_TRUE(Rentals) THEN 'Yes' ELSE 'No' END) AS test
FROM `customers`;

JOIN

JOIN(array expr, delimiter)

Joins all values into a string.

Example JOIN usage

SELECT id,
    JOIN((SELECT Name FROM Rentals),',') AS RentalNames
FROM customers;

ANY_ELEMENT_TRUE

ANY_ELEMENT_TRUE(array expr)

Returns true when any element in the array is true.

Example ANY_ELEMENT_TRUE usage

SELECT id,
    (CASE WHEN ANY_ELEMENT_TRUE(Rentals) THEN 'Yes' ELSE 'No' END) AS test
FROM `customers`;

ARRAY_ELEM_AT

ARRAY_ELEM_AT(array expr,position)

Returns the element of an array at a position.

Example ARRAY_ELEM_AT usage

SELECT id,
    ARRAY_ELEM_AT(Rentals,5) AS test
FROM `customers`;

ARRAY_RANGE

ARRAY_RANGE(start,stop,step)

Generates an array of numbers from to with the specified step.

Example ARRAY_RANGE usage

SELECT id,
    ARRAY_RANGE(0,10,2) AS test
FROM `customers`;

ARRAY_TO_OBJECT

ARRAY_TO_OBJECT(array expr)

Converts the array to an object.

Example ARRAY_TO_OBJECT usage

SELECT id,
    ARRAY_TO_OBJECT(OBJECT_TO_ARRAY(`Address`)) AS test
FROM `customers`;

CONCAT_ARRAYS

CONCAT_ARRAYS(array expr,...)

Concatenate the provided list of arrays.

Example CONCAT_ARRAYS usage

SELECT id,
    CONCAT_ARRAYS((SELECT `Film Title` AS `$$ROOT` FROM `Rentals`), ARRAY_RANGE(0,10,2)) AS test
FROM `customers`;

FIRST_IN_ARRAY

FIRST_IN_ARRAY(array expr)

Returns the first element of an array.

Example FIRST_IN_ARRAY usage

SELECT id,
    FIRST_IN_ARRAY(`Rentals`) AS test
FROM `customers`;

INDEXOF_ARRAY

INDEXOF_ARRAY(array expr,value,[start],[end])

Returns the index of the value in the array.

Example INDEXOF_ARRAY usage

SELECT id,
    INDEXOF_ARRAY((SELECT `Film Title` AS `$$ROOT` FROM `Rentals`),5) AS test
FROM `customers`;

IS_ARRAY

IS_ARRAY(array expr)

Returns true when the field is an array.

Example IS_ARRAY usage

SELECT id,
    (CASE WHEN IS_ARRAY(Rentals) THEN 'Yes' ELSE 'No' END) AS test
FROM `customers`;

LAST_IN_ARRAY

LAST_IN_ARRAY(array expr)

Returns the last element of an array.

Example LAST_IN_ARRAY usage

SELECT id,
    LAST_IN_ARRAY(`Rentals`) AS test
FROM `customers`;

OBJECT_TO_ARRAY

OBJECT_TO_ARRAY(expr)

Converts the object to an array.

Example OBJECT_TO_ARRAY usage

SELECT id,
    OBJECT_TO_ARRAY(`Address`) AS test
FROM `customers`;

REVERSE_ARRAY

REVERSE_ARRAY(array expr)

Reverses the order of an array field.

Example REVERSE_ARRAY usage

SELECT id,
    REVERSE_ARRAY(`Rentals`) AS test
FROM `customers`;

SET_DIFFERENCE

SET_DIFFERENCE(array expr,...)

Returns an array as the difference of the provided arrays.

Example SET_DIFFERENCE usage

SELECT id,
    SET_DIFFERENCE((SELECT `Film Title` AS `$$ROOT` FROM `Rentals`), PARSE_JSON('[ 1,2,3,4]')) AS test
FROM `customers`;

SET_EQUALS

SET_EQUALS(array expr,...)

Returns true or false if the arrays are equal.

Example SET_EQUALS usage

SELECT id,
    SET_EQUALS((SELECT `Film Title` AS `$$ROOT` FROM `Rentals`), PARSE_JSON('[ 1,2,3,4]')) AS test
FROM `customers`;

SET_INTERSECTION

SET_INTERSECTION(array expr,...)

Returns an array as the difference of the provided arrays.

Example SET_INTERSECTION usage

SELECT id,
    SET_INTERSECTION((SELECT filmId AS `$$ROOT` FROM `Rentals`), PARSE_JSON('[ 1,2,3,4]')) AS
test FROM `customers`;

SET_IS_SUBSET

SET_IS_SUBSET(array expr,...)

Returns whether an array is a subset of another.

Example SET_IS_SUBSET usage

SELECT id,
    SET_IS_SUBSET((SELECT filmId AS `$$ROOT` FROM `Rentals`), PARSE_JSON(‘[ 1,2,3,4] ‘)) AS test
FROM `customers`;

SET_UNION

SET_UNION(array expr,...)

Returns an array as the union of the provided arrays.

Example SET_UNION usage

SELECT id,
    SET_UNION((SELECT filmId AS `$$ROOT` FROM `Rentals`), PARSE_JSON(‘[ 1,2,3,4] ‘)) AS test
FROM `customers`;

SIZE_OF_ARRAY

SIZE_OF_ARRAY(array expr)

Returns the size of array.

Example SIZE_OF_ARRAY usage

SELECT id,
    SIZE_OF_ARRAY(`Rentals`) AS test
FROM `customers`;

SUM_ARRAY

SUM_ARRAY(array expr,[field])

Sums the values in an array given an array field or sub-select and the field to sum.

Example SUM_ARRAY usage

SELECT
    SUM_ARRAY(`Rentals`, 'staffId') AS totalStaffIds
FROM `customers`;

Example SUM_ARRAY usage with a sub select

SELECT id,
    `First Name`,
    `Last Name`,
    SUM_ARRAY(
        (SELECT
            SUM_ARRAY(`Payments`, ‘Amount’) AS total
        FROM `Rentals`), ‘total’) AS t
FROM customers;

AVG_ARRAY

AVG_ARRAY(array expr,[field])

Averages the values in an array given an array field or sub-select and the field to average.

Example AVG_ARRAY usage

SELECT
    AVG_ARRAY(`Rentals`, 'staffId') AS avgStaffIds
FROM `customers`;

ZIP_ARRAY

ZIP_ARRAY(array expr,...)

Transposes an array of input arrays so that the first element of the output array would be an array containing, the first element of the first input array, the first element of the second input array, etc.

Example ZIP_ARRAY usage

SELECT id,
    ZIP_ARRAY(
        (SELECT `Film Title` AS `$$ROOT` FROM `Rentals`),ARRAY_RANGE(0,10,2)) AS test
FROM `customers`;