GINQ: SQL-like Collection Queries

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Apache Groovy 6.0 (6.0.0, 2026-09-22) and every example was run with Groovy 6.0.0 on JDK 26 (checked on 2026-10-14). GINQ is a query language embedded in Groovy: inside a GQ { } block the statements look like SQL (from, where, join, groupby, orderby, select) and operate on anything iterable in memory: lists, ranges, arrays, maps, JSON documents, rows already read from a database. Such queries are shorter than the equivalent chain of findAll, collect and groupBy (see Working with Collections) as soon as there is a join, a group or a window, and they read the same to anyone who knows SQL (see Querying Data (SELECT) and Aggregate and Window Functions). GINQ is part of the groovy-ginq module of the standard distribution. It became a stable feature in 6.0 (the custom code-generator extension point described near the end remains incubating), and 6.0 added the groupby ... into form and the set operations. The Java counterpart for collections is Java Streams & Collectors.

The examples query the greenhouse of the other pages: five plants and six sensor readings, declared once as records (see Records & Sealed Types). Every snippet below is a fragment that uses these two lists, and its output is what it printed when run after this setup.

record Plant(int id, String name, String zone, int minMoisture) { }
record Reading(int plantId, int hour, double celsius, int moisture) { }

def plants = [
    new Plant(1, 'tomato', 'south', 45), new Plant(2, 'basil', 'north', 35),
    new Plant(3, 'fern', 'east', 60), new Plant(4, 'mint', 'north', 55), new Plant(5, 'cactus', 'south', 10)
]
def readings = [
    new Reading(1, 6, 28.5, 52), new Reading(1, 9, 31.2, 41), new Reading(2, 6, 21.5, 38),
    new Reading(2, 9, 24.0, 33), new Reading(3, 9, 19.0, 66), new Reading(4, 6, 20.5, 58)
]

A first query

A GINQ expression is written inside a GQ { } block. The block is not a closure that runs: the compiler recognises the SQL-like words inside it and rewrites the whole expression into calls of a fluent API. The GQ wrapper exists so that ordinary code elsewhere in the script may still use the words from or select as names. GQ { } needs no import in a script, while the annotation of the same name does (import groovy.ginq.transform.GQ, see below). The value of GQ { } is a Queryable, a lazy result that runs the query when asked for a list, a stream, an iterator or a for loop.

def q = GQ { from p in plants where p.zone == 'north' select p.name }
println q.getClass().simpleName
println q.toList()
println q.stream().map { it.toUpperCase() }.toList()
println GQL { from p in plants where p.zone == 'north' select p.name }
for (name in q) print "$name; "
println()
QueryableCollection
[basil, mint]
[BASIL, MINT]
[basil, mint]
basil; mint;

GQL { ... } is shorthand for GQ { ... }.toList(), and it is used for most examples that follow. Laziness is visible when the source changes between the definition and the use: the query below is defined with five plants and returns six, because nothing runs until toList().

def lazyQ = GQ { from p in plants select p.name }
plants << new Plant(6, 'rosemary', 'south', 20)
println lazyQ.toList()
[tomato, basil, fern, mint, cactus, rosemary]

(The rosemary plant is removed again before the next snippets.)

Source: Groovy documentation: Using GINQ

The shape of a query

Every query is built from the same clauses, and they must appear in this order. Only from and select are required. Joins may follow from and repeat. A set operation (union, unionall, intersect, minus) glues one complete query to the next.

flowchart LR F["from"] --> J["join (any number)"] --> W["where"] --> G["groupby"] --> H["having"] --> O["orderby"] --> L["limit"] --> S["select"] S -.->|"union, unionall, intersect, minus"| N["next query: from ... select"]

The order is not the order of SQL’s select ... from: GINQ starts with the data and ends with the projection, the way a pipeline reads, and the clause that needs the names introduced by from always comes after it. This also lets an editor complete p. after from p in plants.

Source: Groovy documentation: GINQ syntax reference

Data sources

from alias in source accepts any Iterable, array, or Stream (and the result of another GINQ query), so a source can be a list, a range, a set, a map’s entrySet(), the list inside a parsed JSON document, or the result of another GINQ query. The alias names every element in the rest of the query. The implicit variable _rn is the zero-based row number of the result.

println GQL { from n in 1..4 select n * n }
println GQL { from e in [a: 1, b: 2].entrySet() select e.key, e.value }
println GQL { from p in plants select _rn, p.name }
[1, 4, 9, 16]
[[a, 1], [b, 2]]
[[0, tomato], [1, basil], [2, fern], [3, mint], [4, cactus]]

A String is a single element and not a sequence of characters: from c in 'abc' select c.toUpperCase() returns [ABC], so use 'abc'.toList() or 'abc' as List when the characters are wanted.

Source: Groovy documentation: Data source

Projection

select lists expressions. A single expression yields the bare values. Several expressions yield rows of type NamedRecord: a row can be read by index (row[0]), by destructuring, or by column name. A column without an alias is named after its expression when that is a simple property (p.name is name, and a computed column such as r.celsius * 2 is named by its source text), and as gives it an explicit name. The list of as aliases may be parenthesised. Printing a list of rows or a single row with println shows the values only, and toString() of a row shows the names.

println GQL { from p in plants where p.id <= 2 select p.name, p.zone }
def named = GQL { from p in plants where p.id <= 2 select (p.name as plant, p.zone as area) }
println named
println named.collect { it.plant }
println named[0].getClass().simpleName
println GQL { from p in plants where p.id <= 2 select new Tuple2(p.name, p.id) }
println GQL { from p in plants where p.id <= 2 select "${p.name}@${p.zone}" }
def (first, second) = named[0]
println "$first $second"
println named[0].toString()
[[tomato, south], [basil, north]]
[[tomato, south], [basil, north]]
[tomato, basil]
NamedRecord
[[tomato, 1], [basil, 2]]
[tomato@south, basil@north]
tomato south
(plant:tomato, area:south)

Names matter inside the query as well: a nested query or a derived table in from exposes its columns to the outer query by these names (see the nested queries section), and new Tuple2(...) or any other object in select is passed through unchanged.

Source: Groovy documentation: Projection

Filtering

where takes a boolean expression written with ordinary Groovy operators, including &&, ||, ! and the Groovy truth (see Booleans & the Groovy Truth). in and !in test membership in a list or in another GINQ query, and .exists() on a query tests whether it returns any row.

println GQL { from p in plants where p.zone == 'north' && p.minMoisture > 40 select p.name }
println GQL { from p in plants where p.zone == 'east' || p.minMoisture < 20 select p.name }
println GQL { from p in plants where p.name in ['fern', 'mint'] select p.name }
println GQL { from p in plants where p.name !in ['fern', 'mint'] select p.name }
println GQL { from p in plants where p.id in (from r in readings select r.plantId) select p.name }
println GQL { from p in plants where p.id !in (from r in readings select r.plantId) select p.name }
println GQL {
    from p in plants
    where (from r in readings where r.plantId == p.id && r.celsius > 30 select r).exists()
    select p.name
}
println GQL {
    from p in plants
    where !(from r in readings where r.plantId == p.id select r).exists()
    select p.name
}
[mint]
[fern, cactus]
[fern, mint]
[tomato, basil, cactus]
[tomato, basil, fern, mint]
[cactus]
[tomato]
[cactus]

The last two queries are the "semi-join" pair of SQL: plants that had at least one reading over 30 degrees, and plants that never had a reading. The subquery refers to p of the outer query, which is a correlated subquery. A function named exists(...) as in SQL is not part of GINQ (exists(from ...) is rejected by the compiler), the method form on the parenthesised query is the one to use.

Source: Groovy documentation: Filtering

Joining

Each join adds a data source and an on condition made of == comparisons combined with &&, ||. The join flavours match SQL: join (or innerjoin), leftjoin, rightjoin, fulljoin, and crossjoin, which has no on. innerhashjoin and the other hashjoin variants ask for a hash join explicitly. The unmatched side of an outer join is null, so reach into it with the safe-navigation operator (r?.hour, see Object Operators); a plain r.hour throws a NullPointerException on the unmatched row.

println GQL { from p in plants join r in readings on p.id == r.plantId select p.name, r.hour, r.celsius }
println GQL { from p in plants leftjoin r in readings on p.id == r.plantId select p.name, r?.hour }
println GQL { from p in plants rightjoin r in readings on p.id == r.plantId select p?.name, r.hour }.size()
println GQL { from p in plants fulljoin r in readings on p.id == r.plantId select p?.name, r?.hour }.size()
println GQL { from p in plants crossjoin r in readings select p.name, r.hour }.size()
println GQL { from p in plants innerhashjoin r in readings on p.id == r.plantId select p.name }.size()
println GQL { from p in plants join r in readings on p.id == r.plantId && r.celsius > 25 select p.name, r.celsius }
[[tomato, 6, 28.5], [tomato, 9, 31.2], [basil, 6, 21.5], [basil, 9, 24.0], [fern, 9, 19.0], [mint, 6, 20.5]]
[[tomato, 6], [tomato, 9], [basil, 6], [basil, 9], [fern, 9], [mint, 6], [cactus, null]]
6
7
30
6
[[tomato, 28.5], [tomato, 31.2]]

The left join keeps the cactus, which has no reading, with a null hour. The right join has as many rows as readings (six), the full join adds the cactus to that (seven), and the cross join pairs all five plants with all six readings (thirty). A join on a non-equality condition (on p.minMoisture > r.moisture) was accepted by 6.0.0 too, but it can only be executed as a nested loop, and the syntax description of GINQ speaks of == comparisons, so prefer == in on and put the other conditions in where.

Source: Groovy documentation: Joining

Grouping

groupby is SQL’s GROUP BY, and having is HAVING. In Groovy 6.0 the preferred form ends with into g: the group is bound to a variable g, a GroupResult that is itself a Queryable, so the aggregates are real method calls (g.count(), g.sum(...), g.avg(...), g.max(...)) and the key is g.key. A group of several keys can name them with as, and the names become properties of g.

println GQL { from r in readings groupby r.plantId into g select g.key, g.count() }
println GQL {
    from r in readings
    groupby r.plantId into g
    select g.key as plant, g.count() as n, g.avg(r -> r.celsius) as mean, g.max(r -> r.celsius) as peak
}
println GQL { from r in readings groupby r.plantId into g having g.count() > 1 select g.key, g.sum(r -> r.moisture) }
println GQL { from r in readings groupby r.plantId as plant, r.hour as at into g select g.plant, g.at, g.count() }
[[1, 2], [2, 2], [3, 1], [4, 1]]
[[1, 2, 29.8500000000000000, 31.2], [2, 2, 22.7500000000000000, 24.0], [3, 1, 19.0000000000000000, 19.0], [4, 1, 20.5000000000000000, 20.5]]
[[1, 93], [2, 71]]
[[4, 6, 1], [2, 6, 1], [1, 6, 1], [3, 9, 1], [2, 9, 1], [1, 9, 1]]

Source: Groovy documentation: Grouping

Three things in the output are worth noting. avg returns a BigDecimal with a long scale, so round it for display (avg(...).setScale(1, RoundingMode.HALF_UP) or String.format). The aggregate methods of g take a lambda over the group’s elements (r -> r.celsius). And the order of the groups is not specified: the last query lists them in a different order from the others, so add an orderby whenever the order matters. The where placed after groupby ... into is reserved for the future, so filter groups with having.

The classic style

The older form has no into. The aggregates are special functions written with the group’s data source in the select and having clauses, and every non-aggregate column in select must appear in the groupby. It is more compact for simple cases and is the only form that does not need g.

println GQL { from r in readings groupby r.plantId select r.plantId, count() as n, max(r.celsius) as peak }
println GQL { from r in readings groupby r.plantId having avg(r.celsius) > 22 select r.plantId, avg(r.celsius) }
println GQL { from r in readings groupby r.plantId select r.plantId, list(r.hour) as hours, median(r.celsius) as mid }
println GQL {
    from p in plants
    groupby p.zone as area, (p.minMoisture > 40) as thirsty
    select area, thirsty, count() as n
}
[[1, 2, 31.2], [2, 2, 24.0], [3, 1, 19.0], [4, 1, 20.5]]
[[1, 29.8500000000000000], [2, 22.7500000000000000]]
[[1, [6, 9], 29.85], [2, [6, 9], 22.75], [3, [9], 19.0], [4, [6], 20.5]]
[[south, true, 1], [north, false, 1], [south, false, 1], [north, true, 1], [east, true, 1]]

Source: Groovy documentation: Classic groupby style

The aggregate functions available in this form are count(), count(expr), min, max, sum, avg, median, stdev, stdevp, var, varp, list (collect the values into a list) and agg (a custom aggregation, incubating). A computed group key must be parenthesised before as ((p.minMoisture > 40) as thirsty) or the compiler reads thirsty as a type name.

Sorting and pagination

orderby takes one or more expressions, each optionally followed by in asc (the default) or in desc, and in asc(nullslast), in asc(nullsfirst) and the same for desc place the null values explicitly. limit size or limit offset, size follows orderby.

println GQL { from p in plants orderby p.zone, p.minMoisture in desc select p.zone, p.name }
println GQL { from p in plants orderby p.name in desc select p.name }
println GQL { from p in plants leftjoin r in readings on p.id == r.plantId orderby r?.hour in asc(nullslast), p.name select p.name, r?.hour }
println GQL { from p in plants leftjoin r in readings on p.id == r.plantId orderby r?.hour in asc(nullsfirst), p.name select p.name, r?.hour }
println GQL { from r in readings orderby r.celsius in desc limit 3 select r.celsius }
println GQL { from r in readings orderby r.celsius limit 2, 3 select r.celsius }
[[east, fern], [north, mint], [north, basil], [south, tomato], [south, cactus]]
[tomato, mint, fern, cactus, basil]
[[basil, 6], [mint, 6], [tomato, 6], [basil, 9], [fern, 9], [tomato, 9], [cactus, null]]
[[cactus, null], [basil, 6], [mint, 6], [tomato, 6], [basil, 9], [fern, 9], [tomato, 9]]
[31.2, 28.5, 24.0]
[21.5, 24.0, 28.5]

limit 2, 3 skips two rows and returns the next three, as LIMIT 3 OFFSET 2 does in SQL. The word offset is not a GINQ keyword: limit 3 offset 2 was accepted when tried, but the offset was ignored and the first three rows were returned, so always use the comma form. Likewise limit is a clause keyword, so a variable called limit in a where clause is a compile error ("Invalid syntax found in limit clause"): rename the variable.

Source: Groovy documentation: Sorting, pagination

Set operations (6.0)

Groovy 6.0 can combine two complete queries with union (distinct rows of both), unionall (all rows, duplicates kept), intersect (rows in both) and minus (rows of the first that are not in the second). Each side is a full from ... select query, with its own where, groupby or limit, and the two must produce rows of the same shape. The operator word has to be on a line of its own, and the operations chain from left to right: Q1 union Q2 minus Q3 means (Q1 union Q2) minus Q3.

println GQL {
    from p in plants where p.zone == 'north' select p.name
    union
    from p in plants where p.minMoisture > 50 select p.name
}
println GQL {
    from p in plants where p.zone == 'north' select p.name
    unionall
    from p in plants where p.minMoisture > 50 select p.name
}
println GQL {
    from p in plants where p.zone == 'north' select p.name
    intersect
    from p in plants where p.minMoisture > 50 select p.name
}
println GQL {
    from p in plants where p.zone == 'north' select p.name
    minus
    from p in plants where p.minMoisture > 50 select p.name
}
[basil, mint, fern]
[basil, mint, fern, mint]
[mint]
[basil]

The same operations exist as methods on the Queryable (q1.union(q2), q1.intersect(q2), q1.minus(q2)), for code that builds queries from parts. Writing the whole expression on one line (select n union from m in ...) is rejected with "Invalid syntax found in from clause".

Source: Groovy documentation: Set operations

Window functions

A window function computes a value for each row from a set of related rows (its window) without collapsing them as groupby does. It is written in parentheses, always, as (function(args) over(...)), and the over clause has up to three parts: partitionby (split into independent groups), orderby (the order inside a partition) and a frame, rows lower, upper or range lower, upper, where 0 is the current row, a negative number is that many rows before it, a positive number that many after it, and null is unbounded on that side.

println GQL { from r in readings select r.plantId, r.hour, (rowNumber() over(partitionby r.plantId orderby r.hour)) as nth }
println GQL {
    from r in readings
    select r.plantId, r.celsius,
           (rank() over(orderby r.celsius in desc)) as hottest,
           (ntile(2) over(orderby r.celsius)) as half
}
println GQL {
    from r in readings
    select r.plantId, r.hour, r.celsius,
           (lag(r.celsius) over(partitionby r.plantId orderby r.hour)) as previous,
           (lead(r.celsius) over(partitionby r.plantId orderby r.hour)) as next
}
println GQL {
    from r in readings
    select r.plantId, r.hour, r.celsius,
           (sum(r.celsius) over(partitionby r.plantId orderby r.hour rows null, 0)) as running,
           (avg(r.celsius) over(partitionby r.plantId)) as plantMean
}
println GQL {
    from r in readings
    select r.celsius,
           (firstValue(r.celsius) over(orderby r.celsius)) as lowest,
           (lastValue(r.celsius) over(orderby r.celsius rows null, null)) as highest
}
[[1, 6, 0], [1, 9, 1], [2, 6, 0], [2, 9, 1], [3, 9, 0], [4, 6, 0]]
[[1, 28.5, 2, 1], [1, 31.2, 1, 1], [2, 21.5, 4, 0], [2, 24.0, 3, 1], [3, 19.0, 6, 0], [4, 20.5, 5, 0]]
[[1, 6, 28.5, null, 31.2], [1, 9, 31.2, 28.5, null], [2, 6, 21.5, null, 24.0], [2, 9, 24.0, 21.5, null], [3, 9, 19.0, null, null], [4, 6, 20.5, null, null]]
[[1, 6, 28.5, 28.5, 29.8500000000000000], [1, 9, 31.2, 59.7, 29.8500000000000000], [2, 6, 21.5, 21.5, 22.7500000000000000], [2, 9, 24.0, 45.5, 22.7500000000000000], [3, 9, 19.0, 19.0, 19.0000000000000000], [4, 6, 20.5, 20.5, 20.5000000000000000]]
[[28.5, 19.0, 31.2], [31.2, 19.0, 31.2], [21.5, 19.0, 31.2], [24.0, 19.0, 31.2], [19.0, 19.0, 31.2], [20.5, 19.0, 31.2]]

Reading the rows: rowNumber restarts at 0 in every partition, so the first reading of each plant is nth 0. rank gives the hottest reading rank 1 (31.2 is the highest temperature) and ntile(2) splits the rows into two halves by temperature. lag and lead look one row before and after in the partition and return null at the edges (both take an offset and a default value as the second and third argument). rows null, 0 is "from the start of the partition to the current row", which turns sum into a running total. Without orderby in the window, avg is computed over the whole partition, giving every row of plant 1 the same mean. A lastValue over the default frame would end at the current row, so the example widens the frame to rows null, null to see the last row of the partition.

The full list of window functions:

Function Meaning

rowNumber()

Zero-based position of the row in its partition.

rank(), denseRank()

Rank with gaps (after ties) and without gaps.

percentRank(), cumeDist()

Relative rank, as BigDecimal.

ntile(n)

Bucket number from 0 to n - 1.

lead(expr [, offset [, default]]), lag(...)

Value of the row after or before the current one.

firstValue(expr), lastValue(expr), nthValue(expr, n)

Value at the first, last or n-th row of the frame.

count(), count(expr), min, max, sum, avg, median, stdev, stdevp, var, varp

The aggregates, over the frame. Numeric results are BigDecimal (count is Long).

agg(expr)

Custom aggregation, incubating.

Source: Groovy documentation: Window functions

Nested queries

A query can appear wherever a data source or a value can: in from (a derived table), in where (the in and exists forms above), and in select, where it must yield at most one value (it throws TooManyValuesException otherwise, so add limit 1). In the second query below, a plant without readings gets null.

println GQL {
    from t in (from r in readings groupby r.plantId into g select g.key as id, g.avg(r -> r.celsius) as mean)
    join p in plants on p.id == t.id
    where t.mean > 22
    select p.name, t.mean
}
println GQL {
    from p in plants
    select p.name, (from r in readings where r.plantId == p.id orderby r.celsius in desc limit 1 select r.celsius) as peak
}
[[tomato, 29.8500000000000000], [basil, 22.7500000000000000]]
[[tomato, 31.2], [basil, 24.0], [fern, 19.0], [mint, 20.5], [cactus, null]]

GINQ has no with clause. A query kept in a variable fills the same role, because a Queryable is itself a data source:

def warm = GQ { from r in readings where r.celsius > 22 select r }
println GQL { from w in warm join p in plants on w.plantId == p.id select p.name, w.celsius }
[[tomato, 28.5], [tomato, 31.2], [basil, 24.0]]

Source: Groovy documentation: Nested GINQ

Querying JSON and database rows

Because any collection of maps or beans is a source, the parsed form of a JSON document (see JSON) can be queried directly:

import groovy.json.JsonSlurper

def json = new JsonSlurper().parseText('''{"plants": [{"name": "tomato", "zone": "south", "height": 120},
  {"name": "basil", "zone": "north", "height": 30}, {"name": "mint", "zone": "north", "height": 25}]}''')
println GQL { from p in json.plants where p.zone == 'north' orderby p.height in desc select p.name, p.height }
[[basil, 30], [mint, 25]]

The rows returned by groovy.sql.Sql are maps, so GINQ can do the joins and groupings that are inconvenient in DataSet (see Databases with Groovy SQL). This runs against an in-memory H2 database; the join and the aggregation happen in memory on the rows already fetched, so for large tables do the work in SQL.

@GrabConfig(systemClassLoader = true)
@Grab('com.h2database:h2:2.3.232')
import groovy.sql.Sql

Sql.withInstance('jdbc:h2:mem:gq', 'sa', '', 'org.h2.Driver') { sql ->
    sql.execute 'create table plant (id int primary key, name varchar(40), zone varchar(10))'
    sql.execute 'create table reading (plant_id int, celsius double)'
    sql.executeInsert "insert into plant values (1, 'tomato', 'south')"
    sql.executeInsert "insert into plant values (2, 'basil', 'north')"
    sql.executeInsert 'insert into reading values (1, 31.2)'
    sql.executeInsert 'insert into reading values (2, 21.5)'
    sql.executeInsert 'insert into reading values (2, 24.0)'
    def plants = sql.rows('select * from plant')
    def readings = sql.rows('select * from reading')
    println plants.first().getClass().simpleName
    println GQL {
        from p in plants
        join r in readings on p.id == r.plant_id
        groupby p.name
        select p.name, max(r.celsius) as peak, count() as n
    }
}
GroovyRowResult
[[basil, 24.0, 2], [tomato, 31.2, 1]]

Source: Groovy documentation: Query JSON

Recipes

A few SQL idioms have a direct Groovy equivalent.

List comprehension. GQL is a compact filter-and-map, and the lazy result can be used straight in a for loop. For the nine-line case the GDK methods of Working with Collections are as short, which is the signal to use GQ only when the query has a join, group, window or sort.

CASE WHEN. Use a switch expression (see Control Structures):

println GQL {
    from p in plants
    select p.name, switch (p.minMoisture) { case 0..<20 -> 'dry' case 20..<50 -> 'medium' default -> 'wet' } as need
}
[[tomato, medium], [basil, medium], [fern, wet], [mint, wet], [cactus, dry]]

UPDATE. A query returns the original objects, so changing them in a peek or a loop updates the source, as an UPDATE ... WHERE would:

class Tagged { String name; String tag }
def things = [new Tagged(name: 'a'), new Tagged(name: 'b', tag: 'kept')]
GQ { from t in things where t.tag == null select t }.stream().peek { it.tag = 'none' }.toList()
println things*.tag
[none, kept]

Source: Groovy documentation: Common recipes

The @GQ annotation

@groovy.ginq.transform.GQ on a method makes the whole method body a GINQ expression, without the GQ { } wrapper, and the method returns the Queryable. The annotation takes the result type (List, Set, Collection, Iterable, Iterator, Stream, or an array type) and parallel.

import groovy.ginq.transform.GQ

@GQ
def warmer(List<Reading> rs, double above) {
    from r in rs where r.celsius > above orderby r.celsius in desc select r.plantId, r.celsius
}
def result = warmer(readings, 25)
println result.getClass().simpleName
println result.toList()

@GQ(List)
def plantIds(List<Reading> rs) {
    from r in rs select r.plantId
}
println plantIds(readings)

@GQ(Set)
def distinctIds(List<Reading> rs) { from r in rs select r.plantId }
println distinctIds(readings)
QueryableCollection
[[1, 31.2], [1, 28.5]]
[1, 1, 2, 2, 3, 4]
[1, 2, 3, 4]

Note that the parameter is called above and not limit, for the keyword reason given in the sorting section.

Source: Groovy documentation: @GQ annotation

Parallel and asynchronous querying

Parallel querying is off by default. GQ(parallel: true) { ... } (or @GQ(parallel = true)) runs the stages on a shared thread pool, which pays off for large sources and costs time for small ones. The pool lives until GQ { shutdown } is called (variants: shutdown immediate, the same as plain shutdown, waits for the running tasks, and shutdown abort does not wait); after that parallel queries stop working.

println GQ(parallel: true) {
    from n1 in 1..1000
    join n2 in 1..10000 on n2 == n1
    where n1 <= 3 && n2 <= 5
    select n1, n2
}.toList()
GQ { shutdown }
[[1, 1], [2, 2], [3, 3]]

GINQ has no asynchronous API of its own, and none is needed: a query is an ordinary blocking expression, and Groovy 6.0’s async/await (see Async/Await) runs it in the background:

def query = async {
    GQL { from r in readings where r.celsius > 25 select r.plantId }
}
println 'launched, doing other work'
println await(query)
launched, doing other work
[1, 1]

Source: Groovy documentation: Parallel querying, asynchronous querying

Customising and optimising

Two options go in parentheses after GQ. optimize (on by default) lets GINQ rewrite the query into a better execution plan; GQ(optimize: false) turns it off, useful to compare a surprising result against the unoptimised query. astWalker names the class that turns the GINQ syntax tree into code. The default, GinqAstWalker, generates the calls on collections, and a custom class (an implementation of the incubating GinqAstVisitor interface) can target another backend. Both options give the same results here:

println GQ(optimize: false) {
    from n1 in [1, 2, 3]
    join n2 in [1, 2, 3] on n1 == n2
    where n1 > 1 && n2 < 3
    select n1, n2
}.toList()
println GQ(astWalker: 'org.apache.groovy.ginq.provider.collection.GinqAstWalker') { from n in [0, 1, 2] select n }.toList()
[[2, 2]]
[0, 1, 2]

The Queryable API under the syntax can be used directly, which is handy when a query is assembled at run time. With import static org.apache.groovy.ginq.provider.collection.runtime.Queryable.from:

def nums = [1, 2, 2, 3, 3, 4, 4, 5]
println from(nums).groupByInto(e -> e, g -> g.count() > 1).select((g, q) -> Tuple.tuple(g.key, g.count())).toList()
[[2, 2], [3, 2], [4, 2]]

Source: Groovy documentation: Customize GINQ, optimize GINQ, using the Queryable API directly

Syntax summary

Clause Form

from

from alias in source; exactly one per query, more sources come from joins.

join, innerjoin, leftjoin, rightjoin, fulljoin, crossjoin

join alias in source on cond && cond ...; crossjoin takes no on; hash variants such as innerhashjoin.

where

A boolean expression: &&, `

, `!, in, !in, .exists().

groupby, having

groupby expr [as name], ... [into g], then having cond. Classic style aggregates, or g.method(...) with into.

orderby

Each expression optionally followed by in asc or in desc, and also asc(nullslast), desc(nullsfirst) and so on.

limit

limit size or limit offset, size.

select

expr [as name], ...; parenthesise the list for named records; window functions in parentheses.

union, unionall, intersect, minus

On a line of their own between two complete queries.

Known limitations

  • The Groovy documentation lists static type checking as a limitation (GINQ "can not work well" with it enabled). Trying it with 6.0.0: simple queries, and joins, compile and run under @CompileStatic and @TypeChecked, but a groupby ... into g query in a @TypeChecked method failed at compile time with TypeNotPresentException: Type E not present. Keep groupby ... into out of type-checked code, or leave that code dynamic.

  • limit and offset: use the comma form, as shown above. The words from, select, limit and the other clause words are reserved inside a GQ block.

  • The syntax description of on is a list of conditions combined with && and ||, with == as the usual comparison. Other comparisons between the two sources were accepted when tried, but are best placed in where.

  • There is no with clause (use a variable) and no CASE (use switch).

  • The order of groups produced by groupby is not specified.

  • GINQ queries collections in memory. It does not generate SQL, so pushing work to a database is still the job of Databases with Groovy SQL.

Source: Groovy documentation: Known limitations

Where the book is outdated

The Manning book Groovy in Action (publisher page in the references) was written long before GINQ, so it does not cover it at all. Its chapters on collections and on groovy.sql.Sql describe the techniques GINQ now sits on top of. GINQ was introduced in Groovy 4.0 (2022) and became stable in 6.0, which added groupby ... into and the set operations, so online examples from 4.x and 5.x may use the classic groupby style only and cannot use the set operators. A function-style exists(from ...) is not valid GINQ in 6.0.0. For the older style of querying with GDK methods only, see Working with Collections. The JDK baseline is JDK 17 or later.

References