Groovy Script creating duplicates while looping through JSON

Question

I have two sets of JSONs with me. One is a request JSON and another is response JSON. I need to compare request JSON with response JSON based on a certain field and if a match is found I need to merge the data from request and response json and if a match is not found the I still have merge the data but leave some fields blank/not found that are missing. I have created a code but I am getting duplicates when I am creating the final output structure. It will be really great if someone can help me fix the problem.

req_json:

{
  "val": [
    {
      "custNumber": "Z10000",
      "custID": "1000",
      "custName": "Jack",
      "custType": "Private"
    },
    {
      "custNumber": "Z20000",
      "custID": "2000",
      "custName": "Tina",
      "custType": "Private"
    },
    {
      "custNumber": "B31000",
      "custID": "3100",
      "custName": "ACME Holdings",
      "custType": "Business"
    }
  ]
}

resp_json:

{
  "SELECT_FROM_DB_response": {
    "row": [
      {
        "CUSTNO": "Z10000",
        "PRODUCTID": "P21000",
        "PRODUCTNAME": "KEYBOARD",
        "PRODUCTSTATUS": "Shipped",
      },
      {
        "CUSTNO": "Z20000",
        "PRODUCTID": "L53000",
        "PRODUCTNAME": "MOUSE",
        "PRODUCTSTATUS": "Processing",
      },
      {
        "CUSTNO": "B31000",
        "PRODUCTID": "N99500",
        "PRODUCTNAME": "MONITOR",
        "PRODUCTSTATUS": "Delivered",
      }
    ]
  }
}

My code is something as shown below. However, when I am executing it, I am getting duplicates as output. I need to fix this problem but so far I had no luck.

GROOVY CODE:

def mReqOrders = new JsonSlurper().parseText(req_json)
def mRespOrders = new JsonSlurper().parseText(resp_json)
Set finalJsonSet = []

mReqOrders.val.each { reqOrder -> 
                        mRespOrders.SELECT_FROM_DB_response.row.each { respOrder ->
                        if (reqOrder.custNumber == respOrder.CUSTNO) {
                            
                            finalJsonSet << [
                                order_custNumber: respOrder.CUSTNO,
                                order_custID: reqOrder.custID,
                                order_custName: reqOrder.custName, 
                                order_custType: reqOrder.custType,
                                order_productID: respOrder.PRODUCTID,
                                order_productNAME: respOrder.PRODUCTNAME,
                                order_productSTATUS: respOrder.PRODUCTSTATUS
                                ]
                        }
                        else {
                                finalJsonSet << [
                                order_custNumber: respOrder.CUSTNO,
                                order_custID: reqOrder.custID,
                                order_custName: reqOrder.custName, 
                                order_custType: reqOrder.custType,
                                order_productID: "not found",
                                order_productNAME: "not found",
                                order_productSTATUS: "not found"
                                ]       
                        }                       
                    }                   
                }
        //Convert the Set to JSON Format    
        finalJson = JsonOutput.prettyPrint(JsonOutput.toJson(finalJsonSet))
        println (finalJson)

CURRENT OUTPUT:

[
    {
        "order_custNumber": "Z10000",
        "order_custID": "1000",
        "order_custName": "Jack",
        "order_custType": "Private",
        "order_productID": "P21000",
        "order_productNAME": "KEYBOARD",
        "order_productSTATUS": "Shipped"
    },
    {
        "order_custNumber": "Z20000",
        "order_custID": "1000",
        "order_custName": "Jack",
        "order_custType": "Private",
        "order_productID": "not found",
        "order_productNAME": "not found",
        "order_productSTATUS": "not found"
    },
    {
        "order_custNumber": "B31000",
        "order_custID": "1000",
        "order_custName": "Jack",
        "order_custType": "Private",
        "order_productID": "not found",
        "order_productNAME": "not found",
        "order_productSTATUS": "not found"
    },
    {
        "order_custNumber": "Z10000",
        "order_custID": "2000",
        "order_custName": "Tina",
        "order_custType": "Private",
        "order_productID": "not found",
        "order_productNAME": "not found",
        "order_productSTATUS": "not found"
    },
    {
        "order_custNumber": "Z20000",
        "order_custID": "2000",
        "order_custName": "Tina",
        "order_custType": "Private",
        "order_productID": "L53000",
        "order_productNAME": "MOUSE",
        "order_productSTATUS": "Processing"
    },
    {
        "order_custNumber": "B31000",
        "order_custID": "2000",
        "order_custName": "Tina",
        "order_custType": "Private",
        "order_productID": "not found",
        "order_productNAME": "not found",
        "order_productSTATUS": "not found"
    },
    {
        "order_custNumber": "Z10000",
        "order_custID": "3100",
        "order_custName": "ACME Holdings",
        "order_custType": "Business",
        "order_productID": "not found",
        "order_productNAME": "not found",
        "order_productSTATUS": "not found"
    },
    {
        "order_custNumber": "Z20000",
        "order_custID": "3100",
        "order_custName": "ACME Holdings",
        "order_custType": "Business",
        "order_productID": "not found",
        "order_productNAME": "not found",
        "order_productSTATUS": "not found"
    },
    {
        "order_custNumber": "B31000",
        "order_custID": "3100",
        "order_custName": "ACME Holdings",
        "order_custType": "Business",
        "order_productID": "N99500",
        "order_productNAME": "MONITOR",
        "order_productSTATUS": "Delivered"
    }
]

EXPECTED OUTPUT:

[
    {
        "order_custNumber": "Z10000",
        "order_custID": "1000",
        "order_custName": "Jack",
        "order_custType": "Private",
        "order_productID": "P21000",
        "order_productNAME": "KEYBOARD",
        "order_productSTATUS": "Shipped"
    },
    {
        "order_custNumber": "Z20000",
        "order_custID": "2000",
        "order_custName": "Tina",
        "order_custType": "Private",
        "order_productID": "L53000",
        "order_productNAME": "MOUSE",
        "order_productSTATUS": "Processing"
    },
    {
        "order_custNumber": "B31000",
        "order_custID": "3100",
        "order_custName": "ACME Holdings",
        "order_custType": "Business",
        "order_productID": "N99500",
        "order_productNAME": "MONITOR",
        "order_productSTATUS": "Delivered"
    }
]

**IMPORTANT NOTE:** Its important to keep in mind that a customer can place multiple orders so I had a scenario where there is 1 request json with 3 response json for the same customer number. Hence, I have kept two loops. — Arkesh Sharma, Mar 31 '21 at 09:57
To understand duplicates you have to define unique fields in each record. Think about using `groupBy`. — daggett, Mar 31 '21 at 10:41
Build a map from customerNumber to customer out of request. Then iterate the response and enrich each order by the customer data. — cfrick, Mar 31 '21 at 10:54
@daggett Thank you for your response. I am still learning so it will be helpful if you can elaboarate on your responses with details so that I am able to better understand it. — Arkesh Sharma, Mar 31 '21 at 12:32
@cfrick : Please clarify if my understanding is correct which is mentioned below: 1. pick customerNumber from the req_json and store it in a map 2. take one customerNumber from new map/list and start comparing it against the resp_json 3. Merge data from req_json and resp_json where a match is found. — Arkesh Sharma, Mar 31 '21 at 13:29
I have made some progress. I have written a piece of code that works in most cases but when the number of request jsons is more than the response jsons then it fails and messes up in the end. I will paste the code here. — Arkesh Sharma, Mar 31 '21 at 16:58
`code`def ct=0 mreqOrders.val.each{reqOrd->mResOrder.SELECT_FROM_DB_response.row.each{resOrd->if(reqOrd.custNumber==resOrd.CUSTNO){finalJsonSet<<[ord_custNumber:resOrd.CUSTNO,ord_custID:reqOrd.custID,ord_custName:reqOrd.custName,ord_custType:reqOrd.custType,ord_prdID:resOrd.prdID,ord_prdNAME:resOrd.prdNAME,ord_prdSTATUS:resOrd.prdSTATUS]counter=counter+1}}if(counter==0){finalJsonSet<<[ord_custNumber:mResOrder.SELECT_FROM_DB_response.row.CUSTNO,ord_custID:reqOrd.custID,ord_custName:reqOrd.custName,ord_custType:reqOrd.custType,ord_prdID:"NF",ord_prdNAME:"NF",ord_prdSTATUS:"NF"]}else{ct=0}}`code` — Arkesh Sharma, Mar 31 '21 at 16:59

Kadir Ozdemir · Answer 1 · 2021-03-31T13:32:05.427

Try "find" instead "each" to check if customer numbers matched

mReqOrders.val.each { reqOrder -> 

                    def matchedRow = mRespOrders.SELECT_FROM_DB_response.row.find {respOrder -> reqOrder.custNumber == respOrder.CUSTNO}
                   
                    if (matchedRow) {

                        finalJsonSet << [
                            order_custNumber: respOrder.CUSTNO,
                            order_custID: reqOrder.custID,
                            order_custName: reqOrder.custName, 
                            order_custType: reqOrder.custType,
                            order_productID: respOrder.PRODUCTID,
                            order_productNAME: respOrder.PRODUCTNAME,
                            order_productSTATUS: respOrder.PRODUCTSTATUS
                            ]
                    } else {

                        finalJsonSet << [
                            order_custNumber: respOrder.CUSTNO,
                            order_custID: reqOrder.custID,
                            order_custName: reqOrder.custName, 
                            order_custType: reqOrder.custType,
                            order_productID: "not found",
                            order_productNAME: "not found",
                            order_productSTATUS: "not found"
                            ]  
                    }            
            }

Hi @Kadir Ozdemir, Thank you for taking time to replying to my query. I am getting the below output when I run the code snippet. Also the last statement ``` mRespOrders.SELECT_FROM_DB_response.row.each { respOrder -> ``` appears to be incomplete. Kindly help me. ``` ``` — Arkesh Sharma, Mar 31 '21 at 13:52

score 0 · Answer 2 · answered Mar 31 '21 at 20:00

Thank you all for looking into my query and taking time out to provide your valuable responses/feedbacks. I was able to figure out the solution to the problem and was able to test it out with various scenarios. The code now works fine for me and it gives the right output for all the cases presented to it. Below is the code I am attaching for your reference:

def mReqOrders = new JsonSlurper().parseText(req_json)
def mRespOrders = new JsonSlurper().parseText(resp_json)
Set finalJsonSet = []

def reqRespMatchCount = 0
mReqOrders.val.each { reqOrder -> 
                        mRespOrders.SELECT_FROM_DB_response.row.each { 
                          responseOrder ->
                                if (reqOrder.custNumber == responseOrder.CUSTNO) {
                                    finalJsonSet << [
                                    order_custNumber: responseOrder.CUSTNO,
                                    order_custID: reqOrder.custID,
                                    order_custName: reqOrder.custName, 
                                    order_custType: reqOrder.custType,
                                    order_productID: responseOrder.PRODUCTID,
                                    order_productNAME: responseOrder.PRODUCTNAME,
                                    order_productSTATUS: responseOrder.PRODUCTSTATUS
                                ]
                            reqRespMatchCount += 1
                        }                      
                    }
                    if (reqRespMatchCount == 0) {
                                finalJsonSet << [
                                order_custNumber: reqOrder.custNumber,
                                order_custID: reqOrder.custID,
                                order_custName: reqOrder.custName, 
                                order_custType: reqOrder.custType,
                                order_productID: "not found",
                                order_productNAME: "not found",
                                order_productSTATUS: "not found"
                                ]       
                        }
                    else {
                          reqRespMatchCount = 0
                          }
                }
        //Convert the Set to JSON Format    
        finalJson = JsonOutput.prettyPrint(JsonOutput.toJson(finalJsonSet))
        println (finalJson)

Groovy Script creating duplicates while looping through JSON

2 Answers2