GINQ: Groovy-Integrated Query

GINQ (Groovy 4) is a SQL-like query DSL over collections (and other data sources).

Supports from, innerjoin/leftjoin/rightjoin/fulljoin/crossjoin, where, groupby/having, orderby (with nullsfirst/nullslast), limit, select distinct, window functions, nested queries, exists, and parallel querying (@GQ(parallel = true)).

@GQ annotation on methods: @groovy.ginq.transform.GQ def query() { from ... select ... }.

def people = [
    [name: 'Ana', age: 30, dept: 'IT'],
    [name: 'Bo', age: 25, dept: 'HR'],
    [name: 'Cy', age: 35, dept: 'IT']
]
def depts = [[code: 'IT', title: 'Technology'], [code: 'HR', title: 'People']]

def result = GQ {
    from p in people
    where p.age > 26
    orderby p.age in desc
    select p.name, p.age
}
println result.toList()

def joined = GQ {
    from p in people
    join d in depts on p.dept == d.code
    select p.name, d.title
}
println joined

def stats = GQ {
    from p in people
    groupby p.dept
    having count() > 1
    select p.dept, count() as headcount, avg(p.age) as avgAge, max(p.age) as oldest
}
println stats

def paged = GQ {
    from n in 1..100
    where n % 7 == 0
    select n
    limit 2, 3                       // offset 2, size 3
}

def windowed = GQ {
    from p in people
    select p.name, p.age, (rowNumber() over(orderby p.age)) as rn, (lag(p.age) over(orderby p.age)) as prevAge
}

def sub = GQ { from p in people where p.age > (from q in people select avg(q.age)) select p.name }