Groovy Sql resultSet rendering to JSON via JsonBuilder

Question

I am trying to generate JSON output in Groovy (4.0.18) from generic SQL requests where the query is provided at runtime.

So I have captured the SQL results in a List (of ResultSets) and I captured the column names in a separate List (of Strings).

Now I just need to output it in JSON and wanted to use Groovy's JsonBuilder so I passed in the two Lists to my outputJsonResults() method where I create a JsonBuilder and try to iterate over the resultSet using the technique Paul King taught me with Groovy's XmlBuilder in a separate post.

Here's a simple example -

List columns = [ "id", "val" ]
List resultSet = [
    [ id:"id1", val:"val1" ],
    [ id:"id2", val:"val2" ],
    [ id:"id3", val:"val3" ]]

void outputJsonResults(columns, resultSet) {

        def json = new groovy.json.JsonBuilder()

        json {
            queryResults(
                    resultSet.each { row ->
                        columns.each { col ->
                            "$col"(row[col])
                        }
                    }
            )
        }

        println json.toPrettyString()

}

outputJsonResults(columns, resultSet)

It works and generates the JSON output but in addition to the array of resultSets there is one stray resultSet listed at the root of the JSON output and I can't figure out where it's coming from or how to eliminate it.

Here's the output generated -

{
    "id": "id3",
    "val": "val3",
    "queryResults": [
        {
            "id": "id1",
            "val": "val1"
        },
        {
            "id": "id2",
            "val": "val2"
        },
        {
            "id": "id3",
            "val": "val3"
        }
    ]
}

And what I am trying to generate is -

{
    "queryResults": [
        {
            "id": "id1",
            "val": "val1"
        },
        {
            "id": "id2",
            "val": "val2"
        },
        {
            "id": "id3",
            "val": "val3"
        }
    ]
}

Any help is appreciated.

Answer 1

What you are calling here is effectively queryResults(resultSet) plus the side-effects, that change the JSON builder. each returns the input (so the original resultSet) and executes the closure for side-effects. The side-effect here is the change of the current JSON-Node, which is the same place, where queryResults got added.

You can see this, when you replace the "$col"(row[col]) call with something else like bug("fix"). Then the result is:

{
    "bug": "fix",
    "queryResults": [
        {
            "id": "id1",
            "val": "val1"
        }, {}, {}
    ]
}

(or add more key-value-pairs to your mock-resultset).

You have to collect (for a list of maps) and subMap/collectEntries (for the maps) instead:

json {        
    queryResults(        
        resultSet.collect { row ->        
            // pick this:
            row.subMap(columns)
            // or this:
            columns.collectEntries { col ->        
                [col, row[col]]        
            }                             
        }            
    )                
}