Ask a Question

Compare JSON & JDBC response using groovy

SOLVED
N78A
Contributor

Compare JSON & JDBC response using groovy

I am trying to compare json response with jdbc response. Please find screenshot for the responses. I am using below groovy to compare value by value, but its only picking the latest attribute from json response instead of  comparing all. 

As of now I am doing it only for 2 attributes  - Transaction Id & Date.

 

import com.eviware.soapui.support.XmlHolder
import groovy.xml.XmlUtil
import groovy.util.XmlSlurper
import groovy.json.JsonSlurper
def groovyUtils = new com.eviware.soapui.support.GroovyUtils(context)
/**
* Model object for comparing
*/
@groovy.transform.Canonical
class Model {
def transactionId
def transactionDate
//def storeId
//def marketingName
//def storeVisitToCity
//def transactionChanel
//def transactionValue
//def totalDiscount


/**
* this will acception jdbc row
* @Param row
* @return
*/
def buildJdbcData(row) {
row.with {
transactionId = TRANSACTION_ID
transactionDate = TRANSACTION_DATE_KEY
//storeId = STORE_ID
//marketingName = MARKETING_NAME
//storeVisitToCity = STORE_VISIT_TO_CITY
//transactionChanel = TRANSACTION_CHANNEL
//transactionValue = TRANSACTION_VALUE
//totalDiscount = TOTAL_DISCOUNT

// log.info transactionId

}

}


/**
* this will accept the json TagInfo
* @Param tagInfo
* @return
*/
def buildJsonData(tagInfo){
transactionId = tagInfo.transactionId
//log.info transactionId
transactionDate = tagInfo.transactionDate
// storeId = tagInfo.@storeId
// marketingName = tagInfo.@marketingName
// storeVisitToCity = tagInfo.@storeVisitToCity
// transactionChanel = tagInfo.@transactionChanel
// transactionValue = tagInfo.@transactionValue
// totalDiscount = tagInfo.@totalDiscount
}
}

/**
* Creating the jdbcResponse from the response received, using fixed value for testing
* If you want, you can assign the response received directly using below instead of current and make sure you replace the step name correctly
* def jdbcResponse = context.expand('${JdbcStepName#Response}')
*/
def jdbcResponse = context.expand( '${JDBC#ResponseAsXML}' )

//log.info jdbcResponse

//Parsing the jdbc and build the jdbc model object list
def results = new XmlSlurper().parseText(jdbcResponse)
//log.info results.ResultSet.Row[0]
def jdbcObjects = []
def jdbcDataObjects = []
results.ResultSet.Row.each { Row ->
jdbcDataObjects.add(new Model().buildJdbcData(Row))
}

log.info "JDBC Response -> "+ results
log.info "JDBC "+ jdbcDataObjects


/**
* Creating the jsonResponse from the response received, using fixed value for testing
* If you want, you can assign the response received directly using below instead of current and make sure you replace the step name correctly
* def jsonResponse = context.expand('${JsonStepName#Response}')

*/
def restResponse = context.expand('${Request#Response}')
//log.info restResponse

//Parsing the json and build the json model object list
def arrayOfTagInfo = new JsonSlurper().parseText(restResponse)

//log.info arrayOfTagInfo

def jsonDataObjects = []
log.info "JSON Response -> "+arrayOfTagInfo
arrayOfTagInfo.transactions.each{ tagInfo ->
jsonDataObjects.add(new Model().buildJsonData(tagInfo))
}
log.info "JSON "+jsonDataObjects

//sorting the Data before checking for equality
//jdbcDataObjects.sort()
//jsonDataObjects.sort()

if (jdbcDataObjects.size() != jsonDataObjects.size()) {
System.err.println("Jdbc resultset size is : ${jdbcDataObjects.size()} and Json result size is : ${jsonDataObjects.size()}")
}
assert jdbcDataObjects == jsonDataObjects, "Comparison of Jdbc and Json data is failed"

 

On running the step, it gives me the result , please see attached image.

it only compares the transaction_date and not transaction id.

 

 

 

16 REPLIES 16
JHunt
Community Hero

You say this code is only comparing the date but not the id, but why do you say that? From your screenshot it looks like the end result is an assertion like this:

 

assert ['20170308','20170330'] == ['20170308','20170330']

And then it is not throwing an assertion error... so isn't it already correct?

 

Otherwise, can you explain differently what the problem is?

JHunt
Community Hero

Ok, I saw your problem now. I simplify it to this to show you the solution:

 

xml = '''
<Results>
  <ResultSet>
    <Row>
      <TRANSACTION_ID>123</TRANSACTION_ID>
      <TRANSACTION_DATE_KEY>20182103</TRANSACTION_DATE_KEY>
    </Row>
    <Row>
      <TRANSACTION_ID>456</TRANSACTION_ID>
      <TRANSACTION_DATE_KEY>20182003</TRANSACTION_DATE_KEY>
    </Row>
  </ResultSet>
</Results>'''
class Model {
    def transactionId
    def transactionDate

    def buildJdbcData(row) {
        transactionId = row.TRANSACTION_ID
        transactionDate = row.TRANSACTION_DATE_KEY
    }
}

new XmlSlurper().parseText(xml).ResultSet.Row.collect { new Model().buildJdbcData(it) }

The problem is that your "constructor" method isn't really a constructor. It's basically doing this:

 

    groovy.slurpersupport.NodeChildren buildJdbcData(row) {
        transactionId = row.TRANSACTION_ID
        transactionDate = row.TRANSACTION_DATE_KEY
return transactionDate }

That's because a method with no return statement will always return the last thing it evaluated. What you probably meant was:

    Model buildJdbcData(row) {
        transactionId = row.TRANSACTION_ID
        transactionDate = row.TRANSACTION_DATE_KEY
return this }

That gets us closer to the solution, but it's still not quite going to solve your problem.

 

a = new XmlSlurper().parseText(xml).ResultSet.Row.collect { new Model().buildJdbcData(it) }
b = new XmlSlurper().parseText(xml).ResultSet.Row.collect { new Model().buildJdbcData(it) }
assert a != b

But they should be the same right? Well since you haven't defined what Model.equals(Model) means, it's just coming back with "a is b", i.e. whether they are the same object, in this case they are not. You can fix it like this:

    ...
boolean equals(Model other) { (this.transactionId == other.transactionId) && (this.transactionDate == other.transactionDate) }
}

and now

assert a==b

But that's tedious for a lot of properties... instead I would just recommend that you do away with the whole Model class, and use a Map. A Map comes with Map.equals(Map) already defined.

 

import groovy.json.JsonSlurper

xml = '''
<Results>
  <Row>
    <transactionId>123</transactionId>
    <transactionDate>20182103</transactionDate>
  </Row>
  <Row>
    <transactionId>456</transactionId>
    <transactionDate>20182003</transactionDate>
  </Row>
</Results>'''

json = '''
{ 
  "Row": [{
    "transactionId":"123",
    "transactionDate":"20182103"
  }, {
    "transactionId":"456",
    "transactionDate":"20182003"
  }]
}'''

fromXml = new XmlSlurper().parseText(xml)
 .Row.collect { [transactionId: it.transactionId, transactionDate: it.transactionDate] }

fromJson = new groovy.json.JsonSlurper().parseText(json)
 .Row.collect { [transactionId: it.transactionId, transactionDate: it.transactionDate] }

assert fromXml == fromJson

Hi thanks for your response

 

You are correct, it gives results :

 

assert ['20170308','20170330'] == ['20170308','20170330']

 

but it does not display the assertion it has performed for other attribute that is - transaction date. 

 

so does it mean it is internally validating all the attributes and displaying only the latest in the result ? 

 

 

I checked it by manually changing the value of ID in JDBC XML , it captures it correctly but still at the time of comparing it only compared date and not ID and passed the step. 

nmrao
Champion Level 3

Would you mind providing both the responses in text format?


Regards,
Rao.
JHunt
Community Hero

Hi, I edited my response above after I saw the problem. Please check it above.

Hi Jhunt,

 

Thanks again. 

I need to understand the information you have shared, i am just a beginner in groovy / scripting / coding. 

 

Will get the understanding and get back to you. 

Thanks much for your time. 

Hi nmrao,

 

Thanks for your response. Please find below

 

JSON Response

 

{
"customerId": "1234",
"yearMonth": "201703",
"transactions": [
{
"transactionId": "C111.158",
"transactionDate": 20170308,
"storeId": 1123,
"marketingName": "ABC Store",
"storeVisitToCity": "StoreCity",
"transactionChanel": "In-Store",
"transactionValue": 566,
"totalDiscount": -33
},
{
"transactionId": "C123.876",
"transactionDate": 20170330,
"storeId": 1111
"marketingName": "StoreName2",
"storeVisitToCity": "cityStore",
"transactionChanel": "In-Store",
"transactionValue": 117,
"totalDiscount": 0
}
]
}

--------------------------------------------------------

 

XML JDBC response

 

<Results>
<ResultSet fetchSize="10">
<Row rowNumber="1">
<TRANSACTION_ID>C111.158</TRANSACTION_ID>
<TRANSACTION_DATE_KEY>20170309</TRANSACTION_DATE_KEY>
<TRANSACTION_VALUE>566</TRANSACTION_VALUE>
<TOTAL_DISCOUNT>-33</TOTAL_DISCOUNT>
<STORE_ID>1123</STORE_ID>
<MARKETING_NAME>ABC Store</MARKETING_NAME>
<VISIT_TO_CITY>StoreCity</VISIT_TO_CITY>
<TRANSACTION_CHANNEL_DESC>In-Store</TRANSACTION_CHANNEL_DESC>
</Row>
<Row rowNumber="2">
<TRANSACTION_ID>C123.876</TRANSACTION_ID>
<TRANSACTION_DATE_KEY>20170330</TRANSACTION_DATE_KEY>
<TRANSACTION_VALUE>117</TRANSACTION_VALUE>
<TOTAL_DISCOUNT/>
<STORE_ID>1111</STORE_ID>
<MARKETING_NAME>StoreName2</MARKETING_NAME>
<VISIT_TO_CITY>cityStore</VISIT_TO_CITY>
<TRANSACTION_CHANNEL_DESC>In-Store</TRANSACTION_CHANNEL_DESC>
</Row>
</ResultSet>
</Results>

 

 

Hi,

 

I tried to use below:

 

def jdbcResponse = context.expand('${JDBC#ResponseAsXML#(//Results)}')

def results = new XmlSlurper().parseText(jdbcResponse)
.ResultSet.Row.collect{[transactionId: it.TRANSACTION_ID,
transactionDate: it.TRANSACTION_DATE_KEY,
transactionValue: it.TRANSACTION_VALUE,
totalDiscount: (it.TOTAL_DISCOUNT).toString(),
storeId: it.STORE_ID,
] }

log.info "JDBC -> " + results

 

def restResponse = context.expand('${Request#Response}')
def arrayOfTagInfo = new JsonSlurper().parseText(restResponse)
.transactions.collect{ [transactionId: it.transactionId,
transactionDate: it.transactionDate,
transactionValue: it.transactionValue,
totalDiscount:(it.totalDiscount).toString(),
storeId: it.storeId,

] }

log.info "JSON -> " + arrayOfTagInfo
assert results == arrayOfTagInfo

 

Now this is not able to compare values of - totalDiscount because in the values are :

JSON 

 

"totalDiscount": -32.4

"totalDiscount": 0

 

JDBC XML

 

<TOTAL_DISCOUNT>-32.4</TOTAL_DISCOUNT>

<TOTAL_DISCOUNT/> ---------------------------------------> because from database, value of totaldiscount is null for this particular tag.

 

 

Please help!

JHunt
Community Hero

Try it like this:

 

def results = new XmlSlurper().parseText(jdbcResponse)
 .ResultSet.Row
 .collect{
    [transactionId: it.TRANSACTION_ID,
    transactionDate: it.TRANSACTION_DATE_KEY,
    transactionValue: it.TRANSACTION_VALUE,
    totalDiscount: it.TOTAL_DISCOUNT ?: 0,
    storeId: it.STORE_ID]
}

This ?: means that if its null or empty string etc, it will be set to zero instead.

cancel
Showing results for 
Search instead for 
Did you mean: