Groovy Goodness: Get Row Number In GINQ Result Set

GINQ (Groovy-INtegrated Query) is added since Groovy 4. With GINQ we can query in-memory collections with SQL like statements. If we want to get the row numbers for each row in the query result set we can use the implicit variable _rn. We must specify _rn in the select expression of our GINQ query. We can even use as to give it a meaningful name in the result set.

In the following example we have a basic GINQ query where use _rn to get the row number of each row in the query result set:

def letters = GQ {
    from letter in ['a', 'b', 'c']
    select _rn, letter
}.collect { item -> [item._rn, item.letter] }

assert letters == [[0, 'a'], [1, 'b'], [2, 'c']]

In the following example we first parse JSON and then use GINQ to query it. We see in the example that the row number is based on the result after the where expression is applied:

import groovy.json.JsonSlurper

def json = new JsonSlurper().parseText('''
[
  {
    "id": 1001,
    "language": "Groovy"
  },
  {
    "id": 1002,
    "language": "Clojure"
  }, 
  {
    "id": 1003,
    "language": "Java"
  }
]
''')

def languages = GQ {
  from l in json
  where l.language == "Groovy"
  // We add 1 to _rn to make it 1-based instead of 0-based. 
  // Also we use as rowNumber to give a meaningful name.
  select _rn + 1 as rowNumber, l.id as id, l.language as name
}.collect { item -> [row: item.rowNumber, name: item.name] }

// Also notice the row number is calculated based
// on the result after applying the where clause.
assert languages.first() == [row: 1, name: "Groovy"]

Written with Groovy 4.0.3.