I have 2 groovy sql resultset, I need to combine the result set so that project_no should be unique and case_no can have multiple elements if there is a duplicate project_no

Below are the 2 groovy sql resultset



Desired List

[[project_no:0-10001,case_nos:[00492268]], [project_no:0-10160,case_nos:[01957580,01957590]] ,[project_no:1-10014,case_nos:[02022686,019126]], [project_no:1-2896337,case_nos:[02039596]]]

This is what I have tried

caseResultForAnalysis.each { ca ->
    def ptmp = [:], caseList = []
    ptmp["project_no"] = ca["project_no"]
    if (caseList.size() > 0) {
        ptmp["case_nos"] = caseList
mergedCaseResult.each { ma ->
    def ptmp = [:], caseList = []
    caseResultForUploads.each { cp ->
        if (!tempPrList.contains(cp["project_no"])) {
            ptmp["project_no"] = ma["project_no"]
        } else if (ma["project_no"] == cp["project_no"]) {
            //if (!ma["case_nos"].contains(cp["case_no"]))
            List tmp = ma["case_nos"]
            if (!tmp.contains(cp["case_no"]))
    if (caseList.size() > 0) {
        ptmp["case_nos"] = caseList

//1st list caseResultForAnalysis
//2nd List caseResultForUploads
//desired List mergedCaseResult

Is there a better way to do this, for better readability and less resource consumption?


2 Answers 2


Formulate your SQL query such that you get exactly the combined results you want. Don't use Groovy to do work that the database should be doing for you. It will be faster and easier.

  • 5
    \$\begingroup\$ Is this really a proper answer? Currently, it seems better placed as a comment. I feel as if you should give more information about how to make the changes to the program. \$\endgroup\$ Commented May 1, 2017 at 17:06
  • \$\begingroup\$ @t3chb0t I stand by this answer. The author asked if there is a "better way to do this". My answer is that it is silly to do this in Groovy, when it can be done in SQL. This code needs to be thrown away completely. I can't provide the SQL code, though, without knowing what the schema looks like. \$\endgroup\$ Commented Aug 18, 2017 at 16:55
  • \$\begingroup\$ @200_success You're right. Sorry. I guess I voted to hastily... let me put it right with +1. \$\endgroup\$
    – t3chb0t
    Commented Aug 18, 2017 at 16:59

To provide a Groovy related answer: There is a method groupBy() on Iterables, which is one of my favourites. You could

  • merge the lists into one list, for example using collectMany
  • groupBy the project_no of your maps
  • for each project_no transform the list of maps into a single list containing case_nos.

In Groovy, this is a three-liner:

List list1 = [[project_no:"0-10001",case_no:"00492268"],[project_no:"0-10160",case_no:"01957580"],[project_no:"1-10014",case_no:"02022686"]]
List list2 = [[project_no:"0-10160",case_no:"01957590"],[project_no:"1-10014",case_no:"019126"],[project_no:"1-2896337",case_no:"02039596"]]

List desiredResult = [[project_no:"0-10001",case_nos:["00492268"]], [project_no:"0-10160",case_nos:["01957580","01957590"]] ,[project_no:"1-10014",case_nos:["02022686","019126"]], [project_no:"1-2896337",case_nos:["02039596"]]]

List result = [list1, list2].collectMany{ it }
        .groupBy{ it.project_no }
        .collect { project_no, maps -> [project_no: project_no, case_nos: maps.case_no ] }

assert desiredResult == result

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.