Forum Discussion

Nmenon81's avatar
Nmenon81
Occasional Contributor
7 years ago

JSON (REST) to JDBC resultset comparison

I want to compare all the elements of a JSON REST response to corresponding elements in the JDBC resultset. I have a working solution when I need to compare specific tag values but now I have a large...
  • Alex99's avatar
    Alex99
    7 years ago

    Something like this?

     

    new XmlSlurper().parseText(<JDBC_TEXT>).ResultSet.Row.children().each { it->
	def key	= it.name().toLowerCase().replace("_", "")
	def value	= it.text()
	def foundElements = []

	for(s in <JSON_TEXT>.findAll( /"[\w]+" : "?[\w.-]{0,}"?/ )) {
		jsonkey   = s.replace("\"", "").split(":")[0].trim()
		jsonvalue = s.replace("\"", "").split(":")[1].trim()

		if(key.equalsIgnoreCase(jsonkey)) {
			foundElements.add(s)
		}
	}

	if(foundElements.size > 0) {
		log.info "for [key=${key}] ${foundElements.size} elements were found ${foundElements}"	
	} else {
		log.error "for [key=${key}] no elements were found"
	}
}




log.info "done"

    Result will look like this:

    ...
Wed Oct 24 21:50:20 CEST 2018:INFO:for [key=uvwaiverfee] 1 elements were found ["UvWaiverFee" : ""]
Wed Oct 24 21:50:20 CEST 2018:INFO:for [key=uvwaivercalc] 1 elements were found ["UvWaiverCalc" : ""]
Wed Oct 24 21:50:20 CEST 2018:INFO:for [key=uvfillerlock] 1 elements were found ["UvFillerLock" : ""]
Wed Oct 24 21:50:20 CEST 2018:ERROR:for [key=pbenupdcount] no elements were found
Wed Oct 24 21:50:20 CEST 2018:ERROR:for [key=pbenkey0] no elements were found
Wed Oct 24 21:50:20 CEST 2018:ERROR:for [key=rowcolumn] no elements were found
Wed Oct 24 21:50:20 CEST 2018:INFO:done
Alex99's avatar
Alex99
Contributor

Something like this?

 

new XmlSlurper().parseText(<JDBC_TEXT>).ResultSet.Row.children().each { it->
	def key	= it.name().toLowerCase().replace("_", "")
	def value	= it.text()
	def foundElements = []

	for(s in <JSON_TEXT>.findAll( /"[\w]+" : "?[\w.-]{0,}"?/ )) {
		jsonkey   = s.replace("\"", "").split(":")[0].trim()
		jsonvalue = s.replace("\"", "").split(":")[1].trim()

		if(key.equalsIgnoreCase(jsonkey)) {
			foundElements.add(s)
		}
	}

	if(foundElements.size > 0) {
		log.info "for [key=${key}] ${foundElements.size} elements were found ${foundElements}"	
	} else {
		log.error "for [key=${key}] no elements were found"
	}
}




log.info "done"

Result will look like this:

...
Wed Oct 24 21:50:20 CEST 2018:INFO:for [key=uvwaiverfee] 1 elements were found ["UvWaiverFee" : ""]
Wed Oct 24 21:50:20 CEST 2018:INFO:for [key=uvwaivercalc] 1 elements were found ["UvWaiverCalc" : ""]
Wed Oct 24 21:50:20 CEST 2018:INFO:for [key=uvfillerlock] 1 elements were found ["UvFillerLock" : ""]
Wed Oct 24 21:50:20 CEST 2018:ERROR:for [key=pbenupdcount] no elements were found
Wed Oct 24 21:50:20 CEST 2018:ERROR:for [key=pbenkey0] no elements were found
Wed Oct 24 21:50:20 CEST 2018:ERROR:for [key=rowcolumn] no elements were found
Wed Oct 24 21:50:20 CEST 2018:INFO:done
vivram's avatar
vivram
Occasional Contributor
7 years ago

Thanks Alex99

 

i'm getting this error (attached screenshot)

 

Not sure if I'm doing it right since I didn't understand the 

(<JDBC_TEXT>) 
AND <JSON_TEXT>

do we have to pass just the keyword or the entire JDBC response in "JDBC_TEXT" and similarly for "JSON_TEXT"

 

currently I've done this

new XmlSlurper().parseText(SKU).ResultSet.Row.children().each { it->
def key = it.name().toLowerCase().replace("_", "")
def value = it.text()
def foundElements = []

for(s in sku.findAll( /"[\w]+" : "?[\w.-]{0,}"?/ )) {
jsonkey = s.replace("\"", "").split(":")[0].trim()
jsonvalue = s.replace("\"", "").split(":")[1].trim()

if(key.equalsIgnoreCase(jsonkey)) {
foundElements.add(s)
}
}

if(foundElements.size > 0) {
log.info "for [key=${key}] ${foundElements.size} elements were found ${foundElements}"
} else {
log.error "for [key=${key}] no elements were found"
}
}

 


log.info "done"

 

  • Alex99's avatar
    Alex99
    Contributor
    7 years ago

    Correct, JSON_TEXT was ment to be a placeholder for your json string and JDBC_TEXT for the jdbc response respectively.

     

    The error you are getting comes from this line in your code, SKU is probably not defined?

    new XmlSlurper().parseText(SKU).ResultSet.Row.children().each { it->

    Also to make it, hopefully, easier to unterstand, here another example. This time with all the values and some comments:

    def JDBC_TEXT = """<Results>
   <ResultSet fetchSize="100">
      <Row rowNumber="1">
         <COMPANY_CODE>01</COMPANY_CODE>
         <POLICY_NUMBER>TEST</POLICY_NUMBER>
         <BENEFIT_SEQ>99</BENEFIT_SEQ>
         <BENEFIT_TYPE>UV</BENEFIT_TYPE>
         <PROCESSED_TO_DATE>99999999</PROCESSED_TO_DATE>
         <STATUS_CODE/>
         <STATUS_REASON/>
         <STATUS_DATE>0</STATUS_DATE>
         <PLAN_CODE/>
         <ISSUE_DATE>0</ISSUE_DATE>
         <PARENT_BENEFIT_SEQ>1</PARENT_BENEFIT_SEQ>
         <PAY_UP_DATE>0</PAY_UP_DATE>
         <MATURE_EXPIRE_DATE>0</MATURE_EXPIRE_DATE>
         <PROCESSING_CYCLE/>
         <INF_RIDER_SEQ>0</INF_RIDER_SEQ>
         <LAST_CHG_DATE>0</LAST_CHG_DATE>
         <LAST_CHG_TIME>538976288</LAST_CHG_TIME>
         <DATE_OF_BIRTH>0</DATE_OF_BIRTH>
         <ISSUE_AGE>0</ISSUE_AGE>
         <UNDERWRITING_CLASS/>
         <SEX_CODE/>
         <INSURED_DEATH_IND/>
         <SECOND_INSURED_DOB>0</SECOND_INSURED_DOB>
         <SEC_INS_ISS_AGE>0</SEC_INS_ISS_AGE>
         <SEC_INS_UWCLS/>
         <SEC_INS_SEX_CODE/>
         <SEC_INS_DEATH_IND/>
         <VALUE_PER_UNIT>0.00</VALUE_PER_UNIT>
         <NUMBER_OF_UNITS>0.00000</NUMBER_OF_UNITS>
         <ANN_PREM_PER_UNIT>0.00000</ANN_PREM_PER_UNIT>
         <MODE_PREMIUM>0.00</MODE_PREMIUM>
         <COMM_MDL_FCTRS_FL/>
         <BENEFIT_FEE>0.00</BENEFIT_FEE>
         <NON_COMM_HELTH_FLG/>
         <COMM_BASED_ON_DATE/>
         <PREM_BASED_ON_DATE/>
         <UNITS_NAR_FLAG/>
         <CATEGORY_CODE/>
         <RENEWAL_TYPE/>
         <AA_MODEL_FLAG/>
         <ETI_MX>0E-7</ETI_MX>
         <POL_LINE_OF_BUSNSS/>
         <PRODUCT_TYPE/>
         <AGENT_SPLIT_CTL>0</AGENT_SPLIT_CTL>
         <COMMISSION_CLASS/>
         <ISSUE_STATE/>
         <RESIDENT_STATE/>
         <AUX_AGE>0</AUX_AGE>
         <BASIC_BILL_DATE>0</BASIC_BILL_DATE>
         <EFF_PREM_DATE>0</EFF_PREM_DATE>
         <MEC_DATE>0</MEC_DATE>
         <UNDERWRITER_CODE/>
         <UNDERWRITER_TYPE/>
         <REIN_CODE>0</REIN_CODE>
         <PREM_TAX_BASIS_FL/>
         <JOINT_FLAG/>
         <ALLCTD_TARGET_PREM>0.00</ALLCTD_TARGET_PREM>
         <ALLCTD_MIN_PREM>0.00</ALLCTD_MIN_PREM>
         <FV_FUND_VALUE_CODE/>
         <FV_BASIS1>0.00</FV_BASIS1>
         <FV_BASIS2>0.00</FV_BASIS2>
         <FV_INCOME1>0.00</FV_INCOME1>
         <FV_INCOME2>0.00</FV_INCOME2>
         <FV_BALANCE1>0.00</FV_BALANCE1>
         <FV_BALANCE2>0.00</FV_BALANCE2>
         <FV_TOT_LOAN_BASIS>0.00</FV_TOT_LOAN_BASIS>
         <FV_TOTAL_LOAN_INC>0.00</FV_TOTAL_LOAN_INC>
         <FV_TOTAL_LOAN_BAL>0.00</FV_TOTAL_LOAN_BAL>
         <FV_GUAR_RATE>0.00</FV_GUAR_RATE>
         <FV_GUAR_DEPOSITS>0.00</FV_GUAR_DEPOSITS>
         <FV_DEP_LOADS>0.00</FV_DEP_LOADS>
         <FV_GR_WITHDRAWALS>0.00</FV_GR_WITHDRAWALS>
         <FV_WITHDRAWAL_LOAD>0.00</FV_WITHDRAWAL_LOAD>
         <FV_COI>0.00</FV_COI>
         <FV_BASE_DED>0.00</FV_BASE_DED>
         <FV_PROCESS_LOADS>0.00</FV_PROCESS_LOADS>
         <FV_PRODUCT_TYPE/>
         <FV_TYPE/>
         <FV_PROCESS_RULE/>
         <FV_DEP_ALLOC/>
         <FV_WTD_ALLOC/>
         <FV_DED_ALLOC/>
         <FV_COI_ALLOC/>
         <FV_LON_ALLOC/>
         <FV_PLD_ALLOC/>
         <FV_LAST_VAL_DATE>0</FV_LAST_VAL_DATE>
         <FV_LANDMARK_DATE>0</FV_LANDMARK_DATE>
         <FV_DEP_ALLOC_FLAG/>
         <FV_SPECIAL_FUND/>
         <FV_ORDER_NUM>0</FV_ORDER_NUM>
         <FV_COMM_CLASS/>
         <FV_WAIVER_FEE/>
         <FV_WAIVER_CALC/>
         <FV_FILLER_LOCK/>
         <UV_GUAR_COI_RATE>1.28665</UV_GUAR_COI_RATE>
         <UV_CURR_COI_RATE>0.82090</UV_CURR_COI_RATE>
         <UV_CURRENT_NAR>90.26115</UV_CURRENT_NAR>
         <UV_TOTAL_BASIS_ISS>0.00</UV_TOTAL_BASIS_ISS>
         <UV_TAX_BASIS_ISSUE>0.00</UV_TAX_BASIS_ISSUE>
         <UV_EI_1>0.00</UV_EI_1>
         <UV_EI_2>0.00</UV_EI_2>
         <UV_EI_3>0.00</UV_EI_3>
         <UV_EI_4>0.00</UV_EI_4>
         <UV_EI_5>0.00</UV_EI_5>
         <UV_EI_6>0.00</UV_EI_6>
         <UV_EI_7>0.00</UV_EI_7>
         <UV_EI_8>0.00</UV_EI_8>
         <UV_EI_9>0.00</UV_EI_9>
         <UV_EI_10>0.00</UV_EI_10>
         <UV_EI_11>0.00</UV_EI_11>
         <UV_EI_12>0.00</UV_EI_12>
         <UV_ANN_EI_1>0.00</UV_ANN_EI_1>
         <UV_ANN_EI_2>0.00</UV_ANN_EI_2>
         <UV_ANN_EI_3>0.00</UV_ANN_EI_3>
         <UV_ANN_EI_4>0.00</UV_ANN_EI_4>
         <UV_ANN_EI_5>0.00</UV_ANN_EI_5>
         <UV_ANN_EI_6>0.00</UV_ANN_EI_6>
         <UV_ANN_EI_7>0.00</UV_ANN_EI_7>
         <UV_ANN_EI_8>0.00</UV_ANN_EI_8>
         <UV_ANN_EI_9>0.00</UV_ANN_EI_9>
         <UV_ANN_EI_10>0.00</UV_ANN_EI_10>
         <UV_ANN_EI_11>0.00</UV_ANN_EI_11>
         <UV_ANN_EI_12>0.00</UV_ANN_EI_12>
         <UV_TOT_SPECIAL_AMT>100000.00</UV_TOT_SPECIAL_AMT>
         <UV_10201988_SP_AMT>0.00</UV_10201988_SP_AMT>
         <UV_12311986_FN_VAL>0.00</UV_12311986_FN_VAL>
         <UV_12311988_FN_VAL>0.00</UV_12311988_FN_VAL>
         <UV_WAIVER_FEE/>
         <UV_WAIVER_CALC/>
         <UV_FILLER_LOCK/>
         <PBEN_UPD_COUNT>0</PBEN_UPD_COUNT>
         <PBEN_KEY0>30314353503030314120202020203939</PBEN_KEY0>
         <ROW_COLUMN>3031435350303031412020202020393955563939393939393939202020202020202020202020202020202020202020202020202020202030312020202020202020202020202020202020202020202020202020200000000128665C0000000082090C000000009026115C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000010000000C000000000000000C000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000</ROW_COLUMN>
      </Row>
   </ResultSet>
</Results>
"""


def JSON_TEXT = """{
   "GetBenefitDetailsResult" : {
      "BenefitDetails" : [
         {
            "CompanyCode" : "01",
            "PolicyNumber" : "TEST",
            "BenefitSeq" : 99,
            "BenefitType" : "UV",
            "ProcessedToDate" : 99999999,
            "StatusCode" : "",
            "StatusReason" : "",
            "StatusDate" : 0,
            "PlanCode" : "",
            "IssueDate" : 0,
            "ParentBenefitSeq" : 1,
            "PayUpDate" : 0,
            "MatureExpireDate" : 0,
            "ProcessingCycle" : "",
            "InfRiderSeq" : 0,
            "LastChgDate" : 0,
            "LastChgTime" : 538976288,
            "DateOfBirth" : 0,
            "IssueAge" : 0,
            "UnderwritingClass" : "",
            "SexCode" : "",
            "InsuredDeathInd" : "",
            "SecondInsuredDob" : 0,
            "SecInsIssAge" : 0,
            "SecInsUwcls" : "",
            "SecInsSexCode" : "",
            "SecInsDeathInd" : "",
            "ValuePerUnit" : 0.00,
            "NumberOfUnits" : 0.00000,
            "AnnPremPerUnit" : 0.00000,
            "ModePremium" : 0.00,
            "CommMdlFctrsFl" : "",
            "BenefitFee" : 0.00,
            "NonCommHelthFlg" : "",
            "CommBasedOnDate" : "",
            "PremBasedOnDate" : "",
            "UnitsNarFlag" : "",
            "CategoryCode" : "",
            "RenewalType" : "",
            "AaModelFlag" : "",
            "EtiMx" : 0E-7,
            "PolLineOfBusnss" : "",
            "ProductType" : "",
            "AgentSplitCtl" : 0,
            "CommissionClass" : "",
            "IssueState" : "",
            "ResidentState" : "",
            "AuxAge" : 0,
            "BasicBillDate" : 0,
            "EffPremDate" : 0,
            "MecDate" : 0,
            "UnderwriterCode" : "",
            "UnderwriterType" : "",
            "ReinCode" : 0,
            "PremTaxBasisFl" : "",
            "JointFlag" : "",
            "AllctdTargetPrem" : 0.00,
            "AllctdMinPrem" : 0.00,
            "TypeCode" : "",
            "ParType" : "",
            "NonForfeiture" : "",
            "Dividend" : "",
            "OtherInfo" : "",
            "AccumDividends" : 0.0,
            "PremiumsPaid" : 0.0,
            "DividendsCredited" : 0.0,
            "TaxBasis" : 0.0,
            "ExcessDividend" : "",
            "UlRiderFvSeq" : 0,
            "CapOytOption" : "",
            "MaximizeOytFlag" : "",
            "OriginalUnits" : 0.0,
            "OrLPOverride" : "",
            "BillLoanNetDiv" : "",
            "Opt8TarFlag" : "",
            "EtiRpuEndowment" : 0.0,
            "EtiRpuPattern" : "",
            "PpPtOytDivs" : 0.0,
            "FundValue" : {
               "FvFundValueCode" : "",
               "FvBasis1" : 0.0,
               "FvBasis2" : 0.0,
               "FvIncome1" : 0.00,
               "FvIncome2" : 0.00,
               "FvBalance1" : 0.00,
               "FvBalance2" : 0.00,
               "FvTotLoanBasis" : 0.00,
               "FvTotalLoanInc" : 0.00,
               "FvTotalLoanBal" : 0.00,
               "FvGuarRate" : 0.00,
               "FvGuarDeposits" : 0.00,
               "FvDepLoads" : 0.00,
               "FvGrWithdrawals" : 0.00,
               "FvWithdrawalLoad" : 0.00,
               "FvCoi" : 0.00,
               "FvBaseDed" : 0.00,
               "FvProcessLoads" : 0.00,
               "FvProductType" : "",
               "FvType" : "",
               "FvProcessRule" : "",
               "FvDepAlloc" : "",
               "FvWtdAlloc" : "",
               "FvDedAlloc" : "",
               "FvCoiAlloc" : "",
               "FvLonAlloc" : "",
               "FvPldAlloc" : "",
               "FvLastValDate" : 0,
               "FvLandmarkDate" : 0,
               "FvDepAllocFlag" : "",
               "FvSpecialFund" : "",
               "FvOrderNum" : "0",
               "FvCommClass" : "",
               "FvWaiverFee" : "",
               "FvWaiverCalc" : "",
               "FvFillerLock" : ""
            },
            "UnitValue" : {
               "UvGuarCoiRate" : 1.28665,
               "UvCurrCoiRate" : 0.82090,
               "UvCurrentNar" : 90.26115,
               "UvTotalBasisIss" : 0.00,
               "UvTaxBasisIssue" : 0.00,
               "UvEi1" : 0.00,
               "UvEi2" : 0.00,
               "UvEi3" : 0.00,
               "UvEi4" : 0.00,
               "UvEi5" : 0.00,
               "UvEi6" : 0.00,
               "UvEi7" : 0.00,
               "UvEi8" : 0.00,
               "UvEi9" : 0.00,
               "UvEi10" : 0.00,
               "UvEi11" : 0.00,
               "UvEi12" : 0.00,
               "UvAnnEi1" : 0.00,
               "UvAnnEi2" : 0.00,
               "UvAnnEi3" : 0.00,
               "UvAnnEi4" : 0.00,
               "UvAnnEi5" : 0.00,
               "UvAnnEi6" : 0.00,
               "UvAnnEi7" : 0.00,
               "UvAnnEi8" : 0.00,
               "UvAnnEi9" : 0.00,
               "UvAnnEi10" : 0.00,
               "UvAnnEi11" : 0.00,
               "UvAnnEi12" : 0.00,
               "UvTotSpecialAmt" : 100000.00,
               "Uv10201988SpAmt" : 0.00,
               "Uv12311986FnVal" : 0.00,
               "Uv12311988FnVal" : 0.00,
               "UvWaiverFee" : "",
               "UvWaiverCalc" : "",
               "UvFillerLock" : ""
            },
            "OtherRider" : {
               "OrRiderType" : "",
               "OrMthlyDed" : 0.0,
               "OrPuaFace" : 0.0
            },
            "Base" : {
               "BaThirdPrtyFlag" : "",
               "BaOrTotInfFlag" : "",
               "BaTamraPrem" : 0.0,
               "BaTamraFlag" : "",
               "BaOrDivIntegra" : "",
               "BaOrVanishFlag" : "",
               "BaOrPuaSeq" : 0.0,
               "BaOrSpecialFee" : "",
               "BaOrWaiverFee" : "",
               "BaOrWaiverCalc" : "",
               "BaOrBenefitLock" : 0.0,
               "BaOrTamraTrmnt" : "",
               "BaOrCovLvlDiv" : ""
            },
            "ShadowBenefit" : {
               "SuTypeCode" : "",
               "SuSubtypeCd" : "",
               "SuUnitsCtrl" : "",
               "SuDeduction" : 0.0,
               "SuPremiumsPaid" : 0.0,
               "SuDivCredited" : 0.0,
               "SuTaxBasis" : 0.0,
               "SuCompSubType" : "",
               "SuTotInfFlag" : "",
               "SuLPOverride" : "",
               "SuTamraFlag" : "",
               "SuRowSource" : "",
               "SuBundledBenSeq" : 0,
               "SuJointSelection" : "",
               "SuSpecialFee" : "",
               "SuWaiverFee" : "",
               "SuWaiverCalc" : "",
               "SuBenefitLock" : 0,
               "SuTamraTrmnt" : ""
            },
            "SurrenderLoad" : {
               "SlTableCode" : "",
               "SlRateUpAge" : 0,
               "SlPercent" : 0.0,
               "SlFlatAmount" : 0.0,
               "SlPctCeaseDate" : 0,
               "SlFlatCeaseDate" : 0,
               "Sl2NdTableCode" : "",
               "Sl2NdRateAgeUp" : 0,
               "Sl2NdPercent" : 0.0,
               "Sl2NdFlatAmount" : 0.0,
               "Sl2NdPctCsDate" : 0,
               "Sl2NdFltCsDate" : 0,
               "SlPctMthlyDed" : 0.0,
               "SlFlatMthlyDed" : 0.0,
               "SlPremiumsPaid" : 0.0,
               "SlDivCredited" : 0.0,
               "SlTaxBasis" : 0.0,
               "SlLPOverride" : "",
               "SlWaiverCalc" : "",
               "SlBenefitLock" : 0
            },
            "PaidUp" : {
               "PuTypeCode" : "",
               "PuIntPdToDate" : 0,
               "PuAccruInt" : 0.0,
               "PuAccruFaceAmt" : 0.0,
               "PuPremiumsPaid" : 0.0,
               "PuDivCredited" : 0.0,
               "PuTaxBasis" : 0.0,
               "PuSpecialFee" : "",
               "PuWaiverFee" : "",
               "PuWaiverCalc" : "",
               "PuBenefitLock" : 0
            },
            "AnnuityRider" : {
               "ArBillingMode" : "",
               "ArBillingForm" : "",
               "ArBilledToDate" : 0,
               "ArPaidToDate" : 0,
               "ArActBillDate" : 0,
               "ArBillableAmount" : 0.00,
               "ArSurrenderLoad" : 0.0,
               "ArCurrAccumCash" : 0.0,
               "ArLastValDate" : 0,
               "ArOther" : "",
               "ArVarCovrFlag" : "",
               "ArWithProcDate" : 0,
               "ArFvCreatnFlag" : "",
               "ArPremDepRque" : "",
               "ArIndexedCovrFl" : "",
               "ArWaiverFee" : "",
               "ArWaiverCalc" : "",
               "ArBenefitLock" : 0
            },
            "BaseFund" : {
               "BfNonForfeiture" : "",
               "BfSpecifiedAmt" : 0.0,
               "BfCurrentDb" : 0.0,
               "BfDbOption" : "",
               "BfCurrSurrLoad" : 0.0,
               "BfMinimumPremium" : 0.0,
               "BfTarget" : 0.0,
               "BfTamraAmount" : 0.0,
               "BfGuidelinLvlPr" : 0.0,
               "BfGuidelinSngPr" : 0.0,
               "BfLastValDate" : 0,
               "BfDateNegative" : 0,
               "BfThirdPrtyFlag" : "",
               "BfTotInfFlag" : "",
               "BfVarCovrFlag" : "",
               "BfWithProcDate" : 0,
               "BfFvCreatnFlag" : "",
               "BfPremDepRque" : "",
               "BfRothIraCbFlg" : "",
               "BfBaseTargetExp" : 0.0,
               "BfWaiverFee" : "",
               "BfWaiverCalc" : "",
               "BfBenefitLock" : 0,
               "BfIndexedCovrFl" : "",
               "BfTreasryCvrFlg" : "",
               "BfHybridCovrFlg" : ""
            },
            "SpecifiedAmountIncrease" : {
               "SpOriginFlag" : "",
               "SpSpecifiedAmt" : 0.0,
               "SpTotInfFlag" : ""
            }
         }
      ],
      "GUID" : "TEST51564564DFSSFG",
      "ReturnCode" : 0,
      "Message" : null
   }
}<"""

//first transform json into a dictionary, grouped by the key
//e.g. {"CompanyCode" : "01", "CompanyCode" : "12",}
//will be aggregated as ["CompanyCode" : ["01", "12"]]
def jsonMap = [:]

for(s in JSON_TEXT.findAll( /"[\w]+" : "?[\w.-]{0,}"?/ )) {
	k = s.replace("\"", "").split(":")[0].trim()
	v = s.replace("\"", "").split(":")[1].trim()

	valueList = []

	if(jsonMap.containsKey(k)) {
		valueList = jsonMap.get(k)
	}

	valueList.add(v)
	jsonMap.put(k, valueList)
}


//aggregate the xml stuff also into a map, slightly easier, since it is not nested
def jdbcMap = [:]
new XmlSlurper().parseText(JDBC_TEXT).ResultSet.Row.children().each { child->
	def jdbcK = child.name().replace("_", "")	//you have to replace the _ otherwise it will not match the provided JSON keys
	def jdbcV	= child.text()

	valueList = []

	if(jdbcMap.containsKey(jdbcK)) {
		valueList = jdbcMap.get(jdbcK)
	}

	valueList.add(jdbcV)
	jdbcMap.put(jdbcK, valueList)
}

//now we want to find all matches
def myMatches = []

//thanks to this stackoverflow question: https://stackoverflow.com/questions/7672262/remove-key-value-from-map-while-iterating
def iJDBC = jdbcMap.entrySet().iterator()

while(iJDBC.hasNext()) {
	def kJDBC = iJDBC.next().key
	def iJSON = jsonMap.entrySet().iterator()

	while(iJSON.hasNext()) {
		def kJSON = iJSON.next().key

		if(kJSON.equalsIgnoreCase(kJDBC)) {
			myMatches.add("JSON: key=${kJSON} | value=${jsonMap.get(kJSON)}  >> matched >>  JDBC: key=${kJDBC} | value=${jdbcMap.get(kJDBC)}")
			iJDBC.remove()
			iJSON.remove()
		}
	}
}


//so after we found all matches, show them
log.info "all matches: ${myMatches}"

//do we have any values in the JDBC response that could NOT be matched?
log.warn "left over JDBC values: ${jdbcMap}"

//do we have any values in the JSON response that could NOT be matched?
log.warn "left over JSON values: ${jsonMap}"

log.info("end")

    The result should look something like this:

    Tue Oct 30 07:52:23 CET 2018:INFO:all matches: [JSON: key=CompanyCode | value=[01]  >> matched >>  JDBC: key=COMPANYC... 
Tue Oct 30 07:52:23 CET 2018:WARN:left over JDBC values: [PBENUPDCOUNT:[0], PBENKEY0:[3031435350303031412020202020393...
Tue Oct 30 07:52:23 CET 2018:WARN:left over JSON values: [GetBenefitDetailsResult:[], BenefitDetails:[], TypeCode:[],...
Tue Oct 30 07:52:23 CET 2018:INFO:end
    • vivram's avatar
      vivram
      Occasional Contributor
      7 years ago

      Thanks Alex, that was pretty detailed. :smileyhappy:

       

      for some reason I'm getting no matches. Getting the below

       

      Logs:

      INFO:all matches: []

      WARN:left over JDBC values: [ACCOUNTNUMBER:[657015, 657015, 657015, 657015, 657015, 657015, 657015, 657015, 657015, 657015......].......[.....] - //This pulls all keynote values

      WARN:left over JSON values: [:]  // This doesn't pull ??

      INFO:end

       

       

      Have attached my two response files

      • Alex99's avatar
        Alex99
        Contributor
        7 years ago

        I went and created a quick solution, but the regex won't mach each an every variation of possible characters.

        For your immediate problem, see code change below. For the future, you probably want to either find a better regex or implement some sort of json parser to collect the necessary data from the json response.

         

        There are a few websites that let you check if a regex will return any results, e.g. click me.

         

        Change this

        for(s in JSON_TEXT.findAll( /"[\w]+" : "?[\w.-]{0,}"?/ )) {

        to that

        for(s in JSON_TEXT.findAll( /"[\w]+"\s{0,}:\s{0,}"?[\w\s,:;.-]{0,}"?/ )) {