How to validate Jmeter JSON response against Database data

Question

I'm searching for a solution how to assert Jmeter JSON response against Database data and print discrepancies in Assertion result message

Jmeter JSON response example

{"data": [
        {
        "code": 99,
        "name": "A"
    },
        {
        "code": 2,
        "name": "B"
    },
        {
        "code": 7,
        "name": "C"
    },
        {
        "code": 4,
        "name": "D"
    },
        {
        "code": 6,
        "name": "E"
    },
        {
        "code": 3,
        "name": "C"
    }
]}

Database data as fetched through JDBC Sampler as JSON

[
        {
        "name": "A",
        "code": 1
    },
        {
        "name": "B",
        "code": 2
    },
        {
        "name": "F",
        "code": 7
    },
        {
        "name": "D",
        "code": 4
    },
        {
        "name": "E",
        "code": 6
    },
        {
        "name": "C",
        "code": 3
    }
]

Assertion message example

        Discrepancies 
     API                          DB
{"code": 99,"name": "a"} <->    {"name": "A","code": 1}  

Answer 1

Have you tried using JSR223 Assertion?

Refer https://www.blazemeter.com/blog/scripting-jmeter-assertions-in-groovy-a-tutorial/

Thanks.

Answer 2

Assuming what you have above JSON values saved into fromJSON and fromDatabase JMeter Variables correspondingly

1. Add JSR223 Assertion as a child of the Sampler you want to fail

2. Put the following code into "Script" area:

   def fromJSON = new groovy.json.JsonSlurper().parseText(vars.get('fromJSON'))
   def fromDatabase = new groovy.json.JsonSlurper().parseText(new groovy.json.JsonBuilder(data: new groovy.json.JsonSlurper().parseText(vars.get('fromDatabase'))).toPrettyString())
   
   
   def differences = fromJSON.collectEntries { k, v -> fromDatabase[k] ? [k, (v - fromDatabase[k])] : [:] }
   
   if (differences.data.size() != 0) {
       AssertionResult.setFailure(true)
       AssertionResult.setFailureMessage('Discrepancies : ' + System.getProperty('line.separator')
               + new groovy.json.JsonBuilder(differences).toPrettyString())
   }   
   

3. That's it, in case of mismatches the assertion will generate Sampler failure and report the differences:

   

You might also be interested in alternative approaches which require less coding and Groovy knowledge, for example Jackson or JSONAssert, check out The Easiest Way To Compare REST API Responses Using JMeter article for more details.