Forum Discussion

N78A's avatar
N78A
Contributor
6 years ago
Solved

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

  • JHunt's avatar
    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
    • N78A's avatar
      N78A
      Contributor

      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. 

  • JHunt's avatar
    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?

    • N78A's avatar
      N78A
      Contributor

      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's avatar
        nmrao
        Champion Level 3
        Would you mind providing both the responses in text format?
  • JHunt's avatar
    JHunt
    Community Hero

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

  • JHunt's avatar
    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.

    • N78A's avatar
      N78A
      Contributor

      Thanks for your response, but it did not work :(

      may be because total discount has a negative value -> '-32.4'. 

       

      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,

       

       

      def arrayOfTagInfo = new JsonSlurper().parseText(restResponse)
      .transactions.collect{ [transactionId: it.transactionId,
      transactionDate: it.transactionDate,
      transactionValue: it.transactionValue,
      totalDiscount: it.totalDiscount?:0,

  • JHunt's avatar
    JHunt
    Community Hero

    OK, 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 != '' ? it.TOTAL_DISCOUNT : 0,
    //...
     
    def arrayOfTagInfo = new JsonSlurper().parseText(restResponse)
    .transactions.collect{ [transactionId: it.transactionId,
    transactionDate: it.transactionDate,
    transactionValue: it.transactionValue,
    totalDiscount: it.totalDiscount,
    //...
    • N78A's avatar
      N78A
      Contributor

      still no luck ..  :'(

       

      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.text()?:'0',

       

       

      def arrayOfTagInfo = new JsonSlurper().parseText(restResponse)
      .transactions.collect{ [transactionId: it.transactionId,
      transactionDate: it.transactionDate,
      transactionValue: it.transactionValue,
      totalDiscount: it.totalDiscount.text()?:'0',

       

      I get error - groovy.lang.MissingMethodException: No signature of method: java.math.BigDecimal.text() is applicable for argument types: () values: [] Possible solutions: next(), getAt(java.lang.String), wait(), wait(long), max(java.math.BigDecimal), wait(long, int) error at line: 26

       

      for json definitions it gives error - line 26

       

       

       

      ONE DOUBT - > ? I need to change in both the definitions - JSON & JDBC ? I am doing it correct ?

       

      • N78A's avatar
        N78A
        Contributor

        I tried with this as well 

         

        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 != '' ? it.TOTAL_DISCOUNT : 0,

         

        still no luck , I dont know if its because of the negative value of attribute or below format, when discount value is null in JDBC XML.

         

        <TOTAL_DISCOUNT>-32.4</TOTAL_DISCOUNT>

        <TOTAL_DISCOUNT/>

         

        JSON

         

        "totalDiscount": -32.4

         "totalDiscount": 0

         

        Please look at it !!