Groovy GINQ – SQL-Like Collection Queries with 12 Tested Examples

Groovy GINQ for SQL-like collection querying. 12+ examples covering select, where, joins, groupby, aggregates, and nested queries. Tested on Groovy 5.x.

“Why leave Groovy to write SQL when you can bring SQL into Groovy? GINQ lets you query collections like database tables – right in your code.”

Paul Deitel, Java How to Program

Last Updated: March 2026 | Tested on: Groovy 5.x, Java 17+ | Difficulty: Intermediate | Reading Time: 25 minutes

What is GINQ in Groovy?

If you’ve ever worked with SQL and wished you could write those same SELECT ... FROM ... WHERE queries directly against your in-memory collections, Groovy GINQ (Groovy-Integrated Native Query) is exactly what you need. Introduced in Groovy 4.0 (see the Groovy 4.0 release notes), GINQ brings a full SQL-like query syntax into the Groovy language itself. You can select, filter, sort, group, join, and aggregate data from lists, sets, maps, and any iterable – all without touching a database.

Think of GINQ as LINQ for the JVM. If you’ve used C#’s LINQ or Java Streams, you’ll feel right at home – but with a much more readable, SQL-inspired syntax. GINQ queries are compiled into standard Groovy code at compile time, so there’s no runtime overhead from a query engine. It’s pure syntactic sugar that makes data manipulation a joy.

Here’s what makes GINQ compelling:

  • SQL-like syntax – from, select, where, orderby, groupby, having, and joins all work as you’d expect
  • Works on any iterable – lists, sets, ranges, maps, or anything that implements Iterable
  • Compile-time transformation – GINQ queries are compiled to efficient Groovy code, not interpreted at runtime
  • Built-in aggregate functions – count(), sum(), avg(), min(), max() are all available
  • No dependencies – it’s part of core Groovy, no extra libraries needed

GINQ is especially useful when you have complex filtering, sorting, and aggregation logic that becomes hard to read with chained collect, findAll, and groupBy calls. Instead of reading code inside-out, you read it top to bottom – just like SQL. If you’re already comfortable with Groovy’s higher-order functions like collect, inject, and groupBy, GINQ gives you an alternative syntax that many find more readable for complex queries.

The basic syntax looks like this:

GINQ Basic Syntax

// GINQ follows SQL clause order
def result = GQ {
    from n in [1, 2, 3, 4, 5]
    where n > 2
    select n
}
println result.toList()

Output

[3, 4, 5]

Here are the examples showing everything GINQ can do.

Practical GINQ Examples

Example 1: Basic GINQ – from and select

What we’re doing: Writing the simplest GINQ queries to select and transform data from a list.

Example 1: Basic from/select

// Simple select - returns all elements
def numbers = GQ {
    from n in [10, 20, 30, 40, 50]
    select n
}
println "All: ${numbers.toList()}"

// Transform in the select clause
def doubled = GQ {
    from n in [1, 2, 3, 4, 5]
    select n * 2
}
println "Doubled: ${doubled.toList()}"

// Select from a range
def squares = GQ {
    from n in 1..6
    select n * n
}
println "Squares: ${squares.toList()}"

// Select a computed expression
def labels = GQ {
    from name in ['Alice', 'Bob', 'Charlie']
    select "Hello, ${name}!"
}
println "Greetings: ${labels.toList()}"

Output

All: [10, 20, 30, 40, 50]
Doubled: [2, 4, 6, 8, 10]
Squares: [1, 4, 9, 16, 25, 36]
Greetings: [Hello, Alice!, Hello, Bob!, Hello, Charlie!]

What happened here: The GQ { } block starts a GINQ query. The from clause binds a variable to each element in the collection, and select determines what gets returned. You can put any Groovy expression in the select clause – arithmetic, string interpolation, method calls – whatever you need. The result is a queryable object; call toList() to materialize it into a list.

Example 2: Filtering with where Clauses

What we’re doing: Using where to filter collections, just like SQL’s WHERE clause.

Example 2: where Clauses

def numbers = [12, 5, 87, 23, 41, 66, 9, 54, 31, 78]

// Simple condition
def large = GQ {
    from n in numbers
    where n > 50
    select n
}
println "Greater than 50: ${large.toList()}"

// Multiple conditions with && and ||
def filtered = GQ {
    from n in numbers
    where n > 20 && n < 70
    select n
}
println "Between 20-70: ${filtered.toList()}"

// Using modulo - even numbers only
def evens = GQ {
    from n in numbers
    where n % 2 == 0
    select n
}
println "Even numbers: ${evens.toList()}"

// String filtering
def names = ['Alice', 'Bob', 'Amanda', 'Charlie', 'Anna', 'Dave']
def aNames = GQ {
    from name in names
    where name.startsWith('A')
    select name
}
println "A-names: ${aNames.toList()}"

// Combined: filter and transform
def result = GQ {
    from name in names
    where name.length() > 3
    select name.toUpperCase()
}
println "Long names uppercase: ${result.toList()}"

Output

Greater than 50: [87, 66, 54, 78]
Between 20-70: [23, 41, 66, 54, 31]
Even numbers: [12, 66, 54, 78]
A-names: [Alice, Amanda, Anna]
Long names uppercase: [ALICE, AMANDA, CHARLIE, ANNA, DAVE]

What happened here: The where clause accepts any boolean expression, just like findAll. You can use standard Groovy operators (&&, ||, !) and call any method on the bound variable. The combination of where + select is equivalent to findAll { }.collect { } but reads more naturally.

Example 3: Sorting with orderby

What we’re doing: Sorting query results using orderby with ascending and descending options.

Example 3: orderby

def numbers = [42, 17, 88, 3, 56, 29, 71]

// Ascending order (default)
def ascending = GQ {
    from n in numbers
    orderby n
    select n
}
println "Ascending:  ${ascending.toList()}"

// Descending order using 'in desc'
def descending = GQ {
    from n in numbers
    orderby n in desc
    select n
}
println "Descending: ${descending.toList()}"

// Sort strings by length
def names = ['Charlie', 'Al', 'Bob', 'Dave', 'Eve']
def byLength = GQ {
    from name in names
    orderby name.length()
    select name
}
println "By length:  ${byLength.toList()}"

// Multi-level sort - by length ascending, then alphabetically
def multiSort = GQ {
    from name in names
    orderby name.length(), name
    select name
}
println "Multi-sort: ${multiSort.toList()}"

// Filter + sort combined
def sortedFiltered = GQ {
    from n in numbers
    where n > 20
    orderby n in desc
    select n
}
println "Filtered desc: ${sortedFiltered.toList()}"

Output

Ascending:  [3, 17, 29, 42, 56, 71, 88]
Descending: [88, 71, 56, 42, 29, 17, 3]
By length:  [Al, Bob, Eve, Dave, Charlie]
Multi-sort: [Al, Bob, Eve, Dave, Charlie]
Filtered desc: [88, 71, 56, 42, 29]

What happened here: The orderby clause sorts the results. By default, it sorts in ascending order. Use in desc to reverse the order. You can sort by any expression – including computed values like name.length(). For multi-level sorting, separate the criteria with commas, just like SQL’s ORDER BY col1, col2.

Example 4: groupby with having

What we’re doing: Grouping data and filtering groups – the GINQ equivalent of SQL’s GROUP BY … HAVING.

Example 4: groupby and having

def words = ['apple', 'ant', 'bat', 'avocado', 'ball', 'cat', 'acorn', 'cap', 'banana']

// Group by first letter and count
def grouped = GQ {
    from w in words
    groupby w[0] as firstLetter
    select firstLetter, count(w)
}
grouped.each { row -> println "Letter '${row[0]}': ${row[1]} words" }
println()

// Group by first letter, only groups with 3+ words (having clause)
def popular = GQ {
    from w in words
    groupby w[0] as firstLetter
    having count(w) >= 3
    select firstLetter, count(w)
}
println "Letters with 3+ words:"
popular.each { row -> println "  '${row[0]}': ${row[1]} words" }
println()

// Group numbers by even/odd
def numbers = [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]
def evenOdd = GQ {
    from n in numbers
    groupby n % 2 as remainder
    select remainder, count(n), sum(n)
}
evenOdd.each { row ->
    def label = row[0] == 0 ? 'Even' : 'Odd'
    println "${label}: count=${row[1]}, sum=${row[2]}"
}

Output

Letter 'a': 4 words
Letter 'b': 3 words
Letter 'c': 2 words

Letters with 3+ words:
  'a': 4 words
  'b': 3 words

Odd: count=5, sum=25
Even: count=5, sum=30

What happened here: The groupby clause groups rows by a key expression. You can alias it with as. The having clause filters groups after aggregation – just like SQL. In the select, you can use aggregate functions (count, sum) alongside the grouping key. The result rows are tuple-like and accessed by index.

Example 5: Aggregate Functions – count, sum, avg, min, max

What we’re doing: Using all five built-in aggregate functions to compute statistics over collections.

Example 5: Aggregate Functions

def scores = [85, 92, 78, 95, 88, 76, 91, 83, 97, 70]

// All aggregates at once
def stats = GQ {
    from s in scores
    select count(s), sum(s), avg(s), min(s), max(s)
}
def row = stats.toList()[0]
println "Count: ${row[0]}"
println "Sum:   ${row[1]}"
println "Avg:   ${row[2] as double}"
println "Min:   ${row[3]}"
println "Max:   ${row[4]}"
println()

// Aggregate with groupby - scores by grade band
def students = [
    [name: 'Alice', dept: 'CS', score: 92],
    [name: 'Bob', dept: 'CS', score: 85],
    [name: 'Charlie', dept: 'Math', score: 78],
    [name: 'Diana', dept: 'Math', score: 95],
    [name: 'Eve', dept: 'CS', score: 88],
    [name: 'Frank', dept: 'Math', score: 72]
]

def deptStats = GQ {
    from s in students
    groupby s.dept as dept
    select dept, count(s), avg(s.score), min(s.score), max(s.score)
}
println "Department Statistics:"
deptStats.each { r ->
    println "  ${r[0]}: count=${r[1]}, avg=${String.format('%.1f', r[2] as double)}, min=${r[3]}, max=${r[4]}"
}

Output

Count: 10
Sum:   855
Avg:   85.5
Min:   70
Max:   97

Department Statistics:
  Math: count=3, avg=81.7, min=72, max=95
  CS: count=3, avg=88.3, min=85, max=92

What happened here: GINQ supports five aggregate functions: count(), sum(), avg(), min(), and max(). Without a groupby, they aggregate the entire collection into a single row. With groupby, they aggregate within each group – exactly like SQL.

Example 6: Inner Joins

What we’re doing: Joining two collections together using GINQ’s innerjoin – the equivalent of SQL INNER JOIN.

Example 6: Inner Joins

def employees = [
    [id: 1, name: 'Alice', deptId: 10],
    [id: 2, name: 'Bob', deptId: 20],
    [id: 3, name: 'Charlie', deptId: 10],
    [id: 4, name: 'Diana', deptId: 30],
    [id: 5, name: 'Eve', deptId: 20]
]

def departments = [
    [id: 10, deptName: 'Engineering'],
    [id: 20, deptName: 'Marketing'],
    [id: 30, deptName: 'Sales']
]

// Inner join - match employees to departments
def joined = GQ {
    from e in employees
    innerjoin d in departments on e.deptId == d.id
    select e.name, d.deptName
}
println "Employee - Department:"
joined.each { row -> println "  ${row[0]} -> ${row[1]}" }
println()

// Inner join with filter
def engOnly = GQ {
    from e in employees
    innerjoin d in departments on e.deptId == d.id
    where d.deptName == 'Engineering'
    select e.name, d.deptName
}
println "Engineers only:"
engOnly.each { row -> println "  ${row[0]}" }
println()

// Inner join with ordering
def sorted = GQ {
    from e in employees
    innerjoin d in departments on e.deptId == d.id
    orderby d.deptName, e.name
    select e.name, d.deptName
}
println "Sorted by dept, then name:"
sorted.each { row -> println "  ${row[1]}: ${row[0]}" }

Output

Employee - Department:
  Alice -> Engineering
  Bob -> Marketing
  Charlie -> Engineering
  Diana -> Sales
  Eve -> Marketing

Engineers only:
  Alice
  Charlie

Sorted by dept, then name:
  Engineering: Alice
  Engineering: Charlie
  Marketing: Bob
  Marketing: Eve
  Sales: Diana

What happened here: The innerjoin ... on clause combines two collections where the join condition matches. Only rows that have a match in both collections appear in the result – just like SQL INNER JOIN. You can chain where and orderby after the join for additional filtering and sorting.

Example 7: Left Join and Cross Join

What we’re doing: Using left outer joins to include unmatched rows, and cross joins to create cartesian products.

Example 7: Left Join and Cross Join

def employees = [
    [id: 1, name: 'Alice', deptId: 10],
    [id: 2, name: 'Bob', deptId: 20],
    [id: 3, name: 'Charlie', deptId: 99]  // No matching department!
]

def departments = [
    [id: 10, deptName: 'Engineering'],
    [id: 20, deptName: 'Marketing'],
    [id: 30, deptName: 'Sales']           // No employees!
]

// Left join - keeps all employees, even those without a department match
def leftJoined = GQ {
    from e in employees
    leftjoin d in departments on e.deptId == d.id
    select e.name, (d?.deptName ?: 'Unassigned')
}
println "Left Join (all employees):"
leftJoined.each { row -> println "  ${row[0]} -> ${row[1]}" }
println()

// Cross join - every employee paired with every department
def colors = ['Red', 'Blue']
def sizes = ['S', 'M', 'L']

def combos = GQ {
    from c in colors
    crossjoin s in sizes
    select "${c}-${s}"
}
println "Cross Join (all combos): ${combos.toList()}"

Output

Left Join (all employees):
  Alice -> Engineering
  Bob -> Marketing
  Charlie -> Unassigned

Cross Join (all combos): [Red-S, Red-M, Red-L, Blue-S, Blue-M, Blue-L]

What happened here: A leftjoin returns all rows from the left collection. When there’s no match on the right side, the right-side variable is null, so we use safe navigation (d?.deptName) and the Elvis operator (?:) for a default. A crossjoin produces every possible pair – useful for generating combinations but be careful with large collections as the result size is multiplicative.

Example 8: GINQ with Custom Objects (POGOs)

What we’re doing: Querying collections of custom Groovy objects (POGOs) instead of maps – a common real-world pattern.

Example 8: GINQ with Custom Objects

import groovy.transform.ToString

@ToString(includeNames = true)
class Product {
    String name
    String category
    BigDecimal price
    int stock
}

def products = [
    new Product(name: 'Laptop', category: 'Electronics', price: 999.99, stock: 25),
    new Product(name: 'Mouse', category: 'Electronics', price: 29.99, stock: 150),
    new Product(name: 'Desk', category: 'Furniture', price: 299.99, stock: 40),
    new Product(name: 'Chair', category: 'Furniture', price: 199.99, stock: 60),
    new Product(name: 'Monitor', category: 'Electronics', price: 449.99, stock: 35),
    new Product(name: 'Lamp', category: 'Furniture', price: 49.99, stock: 100),
    new Product(name: 'Keyboard', category: 'Electronics', price: 79.99, stock: 90)
]

// Find expensive electronics, sorted by price descending
def expensive = GQ {
    from p in products
    where p.category == 'Electronics' && p.price > 50
    orderby p.price in desc
    select p.name, p.price
}
println "Expensive Electronics:"
expensive.each { row -> println "  ${row[0]}: \$${row[1]}" }
println()

// Category summary - count, average price, total stock
def summary = GQ {
    from p in products
    groupby p.category as cat
    select cat, count(p), avg(p.price), sum(p.stock)
}
println "Category Summary:"
summary.each { row ->
    println "  ${row[0]}: ${row[1]} products, avg \$${String.format('%.2f', row[2])}, ${row[3]} in stock"
}
println()

// Find products with below-average stock
def avgStock = products.collect { it.stock }.sum() / products.size()
def lowStock = GQ {
    from p in products
    where p.stock < avgStock
    orderby p.stock
    select p.name, p.stock
}
println "Below avg stock (avg=${String.format('%.0f', avgStock)}):"
lowStock.each { row -> println "  ${row[0]}: ${row[1]} units" }

Output

Expensive Electronics:
  Laptop: $999.99
  Monitor: $449.99
  Keyboard: $79.99

Category Summary:
  Electronics: 4 products, avg $389.99, 300 in stock
  Furniture: 3 products, avg $183.32, 200 in stock

Below avg stock (avg=71):
  Laptop: 25 units
  Monitor: 35 units
  Desk: 40 units
  Chair: 60 units

What happened here: GINQ works well with POGOs (Plain Old Groovy Objects). You access properties with dot notation inside where, orderby, and select clauses. You can also reference external variables (like avgStock) in the where clause. This makes GINQ a natural fit for querying domain objects in a Groovy list.

Example 9: Nested GINQ (Subqueries)

What we’re doing: Nesting one GINQ query inside another – the equivalent of SQL subqueries.

Example 9: Subqueries and Chaining

def orders = [
    [id: 1, customer: 'Alice', amount: 250.00],
    [id: 2, customer: 'Bob', amount: 120.00],
    [id: 3, customer: 'Alice', amount: 340.00],
    [id: 4, customer: 'Charlie', amount: 89.00],
    [id: 5, customer: 'Bob', amount: 510.00],
    [id: 6, customer: 'Alice', amount: 175.00],
    [id: 7, customer: 'Charlie', amount: 430.00]
]

// Compute the average outside, then use it in a GINQ filter
def avgAmount = orders.collect { it.amount }.sum() / orders.size()
def aboveAvg = GQ {
    from o in orders
    where o.amount > avgAmount
    orderby o.amount in desc
    select o.customer, o.amount
}
println "Orders above average:"
aboveAvg.each { row -> println "  ${row[0]}: \$${row[1]}" }
println()

// Find customers whose total spending is above 400
def bigSpenders = GQ {
    from o in orders
    groupby o.customer as cust
    having sum(o.amount) > 400
    orderby sum(o.amount) in desc
    select cust, sum(o.amount)
}
println "Big spenders (total > \$400):"
bigSpenders.each { row -> println "  ${row[0]}: \$${row[1]}" }
println()

// Nested: select from a GINQ result
def topCustomers = GQ {
    from o in orders
    groupby o.customer as cust
    select cust, count(o) as orderCount, sum(o.amount) as total
}
// Use the intermediate result in another query
def vipList = GQ {
    from t in topCustomers
    where t.total > 500
    select t.cust, t.total
}
println "VIP customers (total > \$500):"
vipList.each { row -> println "  ${row[0]}: \$${row[1]}" }

Output

Orders above average:
  Bob: $510.00
  Charlie: $430.00
  Alice: $340.00

Big spenders (total > $400):
  Alice: $765.00
  Bob: $630.00
  Charlie: $519.00

VIP customers (total > $500):
  Charlie: $519.00
  Bob: $630.00
  Alice: $765.00

What happened here: For subquery-like patterns, compute the aggregate outside the GINQ and reference it in the where clause. You can also chain GINQ queries by passing the result of one query as the data source for another. The having + groupby combination works exactly like SQL – filter after aggregation, not before.

Example 10: limit and Distinct Results

What we’re doing: Limiting the number of results and removing duplicates – the GINQ equivalents of SQL’s LIMIT and DISTINCT.

Example 10: limit and distinct

def numbers = [5, 3, 8, 1, 9, 2, 7, 4, 6, 10]

// Top 3 largest numbers
def top3 = GQ {
    from n in numbers
    orderby n in desc
    limit 3
    select n
}
println "Top 3: ${top3.toList()}"

// Skip first 2, take next 4 (offset + limit)
def page = GQ {
    from n in numbers
    orderby n
    limit 2, 4
    select n
}
println "Page (offset=2, limit=4): ${page.toList()}"

// Distinct values
def dupes = [1, 2, 2, 3, 3, 3, 4, 4, 5]
def unique = GQ {
    from n in dupes
    select distinct(n)
}
println "Distinct: ${unique.toList()}"

// Combine: top 3 departments by order count
def orders = [
    [dept: 'Sales', amount: 100],
    [dept: 'Sales', amount: 200],
    [dept: 'Engineering', amount: 150],
    [dept: 'Marketing', amount: 300],
    [dept: 'Sales', amount: 250],
    [dept: 'Marketing', amount: 175],
    [dept: 'Engineering', amount: 400],
    [dept: 'HR', amount: 50]
]

def topDepts = GQ {
    from o in orders
    groupby o.dept as dept
    orderby count(o) in desc
    limit 2
    select dept, count(o), sum(o.amount)
}
println "Top 2 departments by order count:"
topDepts.each { row -> println "  ${row[0]}: ${row[1]} orders, \$${row[2]} total" }

Output

Top 3: [10, 9, 8]
Page (offset=2, limit=4): [3, 4, 5, 6]
Distinct: [1, 2, 3, 4, 5]
Top 2 departments by order count:
  Sales: 3 orders, $550 total
  Engineering: 2 orders, $550 total

What happened here: The limit clause takes one or two arguments. With one argument, it limits the number of results. With two (limit offset, count), it skips offset rows first – perfect for pagination. The distinct() function in the select clause removes duplicate values.

Example 11: GINQ with Maps

What we’re doing: Querying Groovy maps with GINQ – iterating over entries, keys, and values.

Example 11: GINQ with Maps

def inventory = [
    'Laptop': 25,
    'Mouse': 150,
    'Keyboard': 90,
    'Monitor': 35,
    'Webcam': 60,
    'Headset': 45
]

// Query map entries - find items with low stock
def lowStock = GQ {
    from entry in inventory.entrySet()
    where entry.value < 50
    orderby entry.value
    select entry.key, entry.value
}
println "Low stock items:"
lowStock.each { row -> println "  ${row[0]}: ${row[1]} units" }
println()

// Query map: total inventory value (assuming $100 per unit for simplicity)
def unitPrice = [
    'Laptop': 999, 'Mouse': 30, 'Keyboard': 80,
    'Monitor': 450, 'Webcam': 75, 'Headset': 60
]

def valueReport = GQ {
    from item in inventory.entrySet()
    innerjoin price in unitPrice.entrySet() on item.key == price.key
    orderby (item.value * price.value) in desc
    select item.key, item.value as qty, price.value as unitCost, (item.value * price.value) as totalValue
}
println "Inventory Value Report:"
println "  ${'Product'.padRight(12)} ${'Qty'.padLeft(5)} ${'Price'.padLeft(7)} ${'Value'.padLeft(10)}"
println "  ${'-' * 36}"
valueReport.each { row ->
    println "  ${row[0].padRight(12)} ${String.valueOf(row[1]).padLeft(5)} \$${String.valueOf(row[2]).padLeft(6)} \$${String.valueOf(row[3]).padLeft(9)}"
}

Output

Low stock items:
  Laptop: 25 units
  Monitor: 35 units
  Headset: 45 units

Inventory Value Report:
  Product        Qty   Price      Value
  ------------------------------------
  Headset         45 $    60 $     2700
  Mouse          150 $    30 $     4500
  Webcam          60 $    75 $     4500
  Keyboard        90 $    80 $     7200
  Monitor         35 $   450 $    15750
  Laptop          25

What happened here: To query a map with GINQ, iterate over map.entrySet(). Each entry has a key and value. You can join two maps by their keys, which is a powerful way to combine related data. This is essentially the same as joining two database tables by a common column.

Example 12: Real-World Data Querying – Employee Analytics

What we’re doing: A realistic scenario combining multiple GINQ features to build an employee analytics report.

Example 12: Real-World Employee Analytics

def employees = [
    [id: 1, name: 'Alice', dept: 'Engineering', salary: 95000, yearsExp: 8],
    [id: 2, name: 'Bob', dept: 'Engineering', salary: 87000, yearsExp: 5],
    [id: 3, name: 'Charlie', dept: 'Marketing', salary: 72000, yearsExp: 3],
    [id: 4, name: 'Diana', dept: 'Engineering', salary: 105000, yearsExp: 12],
    [id: 5, name: 'Eve', dept: 'Marketing', salary: 68000, yearsExp: 2],
    [id: 6, name: 'Frank', dept: 'Sales', salary: 78000, yearsExp: 6],
    [id: 7, name: 'Grace', dept: 'Sales', salary: 82000, yearsExp: 7],
    [id: 8, name: 'Heidi', dept: 'Engineering', salary: 92000, yearsExp: 6],
    [id: 9, name: 'Ivan', dept: 'Marketing', salary: 75000, yearsExp: 4],
    [id: 10, name: 'Judy', dept: 'Sales', salary: 91000, yearsExp: 9]
]

def projects = [
    [empId: 1, project: 'Atlas', hours: 120],
    [empId: 1, project: 'Beta', hours: 80],
    [empId: 2, project: 'Atlas', hours: 100],
    [empId: 3, project: 'Gamma', hours: 90],
    [empId: 4, project: 'Atlas', hours: 150],
    [empId: 5, project: 'Gamma', hours: 60],
    [empId: 6, project: 'Delta', hours: 110],
    [empId: 7, project: 'Delta', hours: 95],
    [empId: 8, project: 'Beta', hours: 130],
    [empId: 9, project: 'Gamma', hours: 85],
    [empId: 10, project: 'Delta', hours: 105]
]

// 1. Department salary summary
println "=== Department Salary Report ==="
def deptReport = GQ {
    from e in employees
    groupby e.dept as dept
    orderby avg(e.salary) in desc
    select dept, count(e), min(e.salary), avg(e.salary), max(e.salary)
}
deptReport.each { r ->
    println "  ${r[0].padRight(14)} | headcount: ${r[1]} | min: \$${r[2]} | avg: \$${String.format('%.0f', r[3])} | max: \$${r[4]}"
}
println()

// 2. Top earners - above overall average salary
def avgSalary = employees.collect { it.salary }.sum() / employees.size()
println "=== Above Average Earners (avg=\$${String.format('%.0f', avgSalary)}) ==="
def topEarners = GQ {
    from e in employees
    where e.salary > avgSalary
    orderby e.salary in desc
    select e.name, e.dept, e.salary
}
topEarners.each { r -> println "  ${r[0].padRight(10)} ${r[1].padRight(14)} \$${r[2]}" }
println()

// 3. Join: employee project hours
println "=== Project Hours by Department ==="
def projectHours = GQ {
    from e in employees
    innerjoin p in projects on e.id == p.empId
    groupby e.dept as dept, p.project as proj
    orderby dept, sum(p.hours) in desc
    select dept, proj, count(p), sum(p.hours)
}
projectHours.each { r ->
    println "  ${r[0].padRight(14)} ${r[1].padRight(8)} ${r[2]} people, ${r[3]} hours"
}
println()

// 4. Senior employees (7+ years) with their total project hours
println "=== Senior Employees with Project Hours ==="
def seniors = GQ {
    from e in employees
    innerjoin p in projects on e.id == p.empId
    where e.yearsExp >= 7
    groupby e.name as name, e.dept as dept, e.yearsExp as years
    orderby sum(p.hours) in desc
    select name, dept, years, sum(p.hours)
}
seniors.each { r ->
    println "  ${r[0].padRight(10)} ${r[1].padRight(14)} ${r[2]} yrs, ${r[3]} total hrs"
}

Output

=== Department Salary Report ===
  Engineering    | headcount: 4 | min: $87000 | avg: $94750 | max: $105000
  Sales          | headcount: 3 | min: $78000 | avg: $83667 | max: $91000
  Marketing      | headcount: 3 | min: $68000 | avg: $71667 | max: $75000

=== Above Average Earners (avg=$84500) ===
  Diana      Engineering    $105000
  Alice      Engineering    $95000
  Heidi      Engineering    $92000
  Judy       Sales          $91000
  Bob        Engineering    $87000

=== Project Hours by Department ===
  Engineering    Atlas    3 people, 370 hours
  Engineering    Beta     2 people, 210 hours
  Marketing      Gamma    3 people, 235 hours
  Sales          Delta    3 people, 310 hours

=== Senior Employees with Project Hours ===
  Alice      Engineering    8 yrs, 200 total hrs
  Diana      Engineering    12 yrs, 150 total hrs
  Judy       Sales          9 yrs, 105 total hrs
  Grace      Sales          7 yrs, 95 total hrs

What happened here: This example showcases GINQ at its best – combining joins, aggregates, grouping, filtering, and sorting in a single coherent analytics workflow. Each query reads like a SQL statement but operates on in-memory collections. Notice how the join between employees and projects combined with groupby produces exactly the kind of report you’d normally write a database query for.

GINQ vs Collection Methods

If you already use Groovy’s higher-order functions like collect, inject, and groupBy, you might wonder when to use GINQ instead. Here’s a side-by-side comparison to help you decide:

GINQ vs Collection Methods

def people = [
    [name: 'Alice', age: 30, city: 'NYC'],
    [name: 'Bob', age: 25, city: 'LA'],
    [name: 'Charlie', age: 35, city: 'NYC'],
    [name: 'Diana', age: 28, city: 'LA'],
    [name: 'Eve', age: 32, city: 'NYC']
]

// --- TASK 1: Filter + Transform ---
// Collection style
def result1a = people
    .findAll { it.age > 28 }
    .collect { it.name.toUpperCase() }
    .sort()
println "Collection: ${result1a}"

// GINQ style
def result1b = GQ {
    from p in people
    where p.age > 28
    orderby p.name
    select p.name.toUpperCase()
}
println "GINQ:       ${result1b.toList()}"
println()

// --- TASK 2: Group + Count ---
// Collection style
def result2a = people
    .groupBy { it.city }
    .collectEntries { city, ppl -> [city, ppl.size()] }
println "Collection grouped: ${result2a}"

// GINQ style
def result2b = GQ {
    from p in people
    groupby p.city as city
    select city, count(p)
}
println "GINQ grouped:"
result2b.each { row -> println "  ${row[0]}: ${row[1]}" }
println()

// --- TASK 3: Complex - filter, group, aggregate, sort ---
// Collection style (gets messy)
def result3a = people
    .findAll { it.age >= 25 }
    .groupBy { it.city }
    .collectEntries { city, ppl ->
        [city, [count: ppl.size(), avgAge: ppl.collect { it.age }.sum() / ppl.size()]]
    }
    .sort { -it.value.count }
println "Collection complex: ${result3a}"

// GINQ style (stays clean)
def result3b = GQ {
    from p in people
    where p.age >= 25
    groupby p.city as city
    orderby count(p) in desc
    select city, count(p), avg(p.age)
}
println "GINQ complex:"
result3b.each { row -> println "  ${row[0]}: ${row[1]} people, avg age ${row[2]}" }

Output

Collection: [ALICE, CHARLIE, EVE]
GINQ:       [ALICE, CHARLIE, EVE]

Collection grouped: [NYC:3, LA:2]
GINQ grouped:
  NYC: 3
  LA: 2

Collection complex: [NYC:[count:3, avgAge:32.3333333333], LA:[count:2, avgAge:26.5]]
GINQ complex:
  NYC: 3 people, avg age 32.3333333333
  LA: 2 people, avg age 26.5

As you can see, for simple filtering and transformation, both approaches are equally readable. But as complexity grows – especially with grouping, aggregation, and sorting – GINQ’s SQL-like syntax stays flat and readable while chained collection methods become increasingly nested and harder to follow. Use whichever reads better for your specific use case. They’re not mutually exclusive – you can mix them freely.

Performance and Best Practices

Performance Notes

  • Compile-time transformation: GINQ queries are compiled to standard Groovy code by an AST transformation. There is no runtime query engine or interpreter – the compiled bytecode is as efficient as hand-written collection operations.
  • Join performance: GINQ joins use nested iteration (similar to nested loop joins in databases). For large collections, this is O(n*m). If you need hash-based joins for very large datasets, preprocess with groupBy and use map lookups instead.
  • Cross joins: Be extremely careful – a cross join of two lists with 1,000 elements each produces 1,000,000 rows. Always filter early with where to reduce the result set.
  • Memory: GINQ materializes results lazily where possible. Call toList() only when you actually need the full list in memory.

Best Practices

DO:

  • Use GINQ when you have complex queries with multiple clauses (filter + group + aggregate + sort)
  • Use where clauses early to reduce the dataset before joins and aggregations
  • Use limit when you only need the top N results
  • Use meaningful aliases with as in groupby for readability
  • Combine GINQ with regular Groovy collection methods when it makes sense

DON’T:

  • Use GINQ for trivial operations where findAll or collect would be cleaner
  • Use cross joins on large collections without thinking about the result size
  • Forget that GINQ requires Groovy 4.0+ – if you’re on an older version, stick to collection methods
  • Assume GINQ replaces actual database queries – for persistent data, use GORM or SQL directly

Conclusion

Groovy GINQ brings in-memory data querying. It brings the familiar, readable SQL syntax directly into your Groovy code, making complex data operations – filtering, grouping, joining, aggregating – feel natural and easy to reason about. Instead of nesting closures inside closures, you write top-to-bottom queries that any developer with SQL experience can immediately understand.

The beauty of GINQ is that it’s not an either/or choice. You can use it alongside Groovy’s collection methods, picking whichever approach is clearest for each situation. Simple filters? Use findAll. Complex multi-step analytics? GINQ shines. And since it compiles down to standard Groovy bytecode, there’s no runtime penalty for the improved readability.

Now that you can query collections with SQL-like syntax, you might want to explore how Groovy handles dependency management with Grape to pull in libraries without a build tool. And if you haven’t already, check out how Groovy’s higher-order functions compare for simpler collection operations.

Summary

  • GINQ provides SQL-like syntax (from, where, select, orderby, groupby, having) for querying any iterable
  • Supports innerjoin, leftjoin, and crossjoin to combine multiple collections
  • Built-in aggregates: count(), sum(), avg(), min(), max()
  • Use limit for pagination and distinct() for uniqueness
  • Compiled at build time via AST transformation – no runtime overhead
  • Requires Groovy 4.0+ (available in Groovy 5.x)

If you also work with build tools, CI/CD pipelines, or cloud CLIs, check out Command Playground to practice 105+ CLI tools directly in your browser — no install needed.

Up next: Groovy Grape Dependency Management

Frequently Asked Questions

What is GINQ in Groovy?

GINQ (Groovy-Integrated Native Query) is a SQL-like query syntax built into Groovy 4.0+. It lets you write from ... where ... select queries directly against in-memory collections like lists, sets, maps, and any iterable. GINQ queries are compiled to efficient bytecode at compile time – there is no runtime query engine involved.

What version of Groovy do I need for GINQ?

GINQ was introduced in Groovy 4.0.0. It is fully available in Groovy 5.x. If you’re on Groovy 3.x or earlier, you’ll need to upgrade to use GINQ. There are no extra dependencies required – GINQ is part of core Groovy.

How does GINQ compare to Java Streams?

GINQ and Java Streams solve similar problems but with very different syntax. GINQ uses a declarative SQL-like syntax (from, where, select, groupby, join) that reads top-to-bottom, while Streams use a fluent API with chained method calls. GINQ also has built-in join and aggregate support, which Streams lack natively. Performance is comparable since both compile to standard JVM bytecode.

Can GINQ query database tables directly?

No, GINQ operates only on in-memory collections (lists, sets, maps, iterables). It is not an ORM or database query tool. For database queries, use GORM (in Grails) or Groovy SQL. However, you can fetch data from a database into a list and then use GINQ to further filter, join, and aggregate that data in memory.

Does GINQ support all SQL features like window functions or UNION?

GINQ supports the most common SQL features: select, from, where, orderby, groupby, having, limit, joins (inner, left, right, cross, full), aggregate functions (count, sum, avg, min, max), distinct, and subqueries. It also supports window functions via the over clause. However, UNION is not directly supported – you can achieve the same result by concatenating collections before querying.

Previous in Series: Groovy Date Time Modern API

Next in Series: Groovy Grape Dependency Management

Related Topics You Might Like:

This post is part of the Groovy & Grails Cookbook series on TechnoScripts.com

Rahul

GroovyGroovy XML & JSON

collectionsginqGroovyquerysqltutorial

FacebookXPinterestRedditLinkedinWhatsappTelegramEmailShortlink

RahulAuthor posts

Avatar for Rahul

Rahul is a passionate IT professional who loves to sharing his knowledge with others and inspiring them to expand their technical knowledge. Rahul's current objective is to write informative and easy-to-understand articles to help people avoid day-to-day technical issues altogether. Follow Rahul's blog to stay informed on the latest trends in IT and gain insights into how to tackle complex technical issues. Whether you're a beginner or an expert in the field, Rahul's articles are sure to leave you feeling inspired and informed.

Related Posts ...

No comment

Leave a Reply

Your email address will not be published. Required fields are marked *