How to compare JSON data against DB data in JMeter

Question

I want to compare JSON response with DB data in JMeter. Below is Db table structure (Oracle database):

Table Structure

Below is the JSON

{
    "page": 2,
    "per_page": 6,
    "total": 12,
    "total_pages": 2,
    "data": [
        {
            "id": 7,
            "email": "michael.lawson@reqres.in",
            "first_name": "Michael",
            "last_name": "Lawson",
            "avatar": "https://reqres.in/img/faces/7-image.jpg"
        },
        {
            "id": 8,
            "email": "lindsay.ferguson@reqres.in",
            "first_name": "Lindsay",
            "last_name": "Ferguson",
            "avatar": "https://reqres.in/img/faces/8-image.jpg"
        },
        {
            "id": 9,
            "email": "tobias.funke@reqres.in",
            "first_name": "Tobias",
            "last_name": "Funke",
            "avatar": "https://reqres.in/img/faces/9-image.jpg"
        },
        {
            "id": 10,
            "email": "byron.fields@reqres.in",
            "first_name": "Byron",
            "last_name": "Fields",
            "avatar": "https://reqres.in/img/faces/10-image.jpg"
        },
        {
            "id": 11,
            "email": "george.edwards@reqres.in",
            "first_name": "George",
            "last_name": "Edwards",
            "avatar": "https://reqres.in/img/faces/11-image.jpg"
        },
        {
            "id": 12,
            "email": "rachel.howell@reqres.in",
            "first_name": "Rachel",
            "last_name": "Howell",
            "avatar": "https://reqres.in/img/faces/12-image.jpg"
        }
    ],
    "support": {
        "url": "https://reqres.in/#support-heading",
        "text": "To keep ReqRes free, contributions towards server costs are appreciated!"
    }
}

Below is the configuration I have made in JMeter

JDBC Request

JSON Extractor

Response Assertion

But I am getting null response:

Assertion Result

what mistake am I making?

I want to compare each cell data with JSON data. For example ID=7 with JSON and ID=9 with JSON and so on.

Is this possible to do this with any scripting language in JMeter, if yes please suggest the code for the same.

Answer 1

There are multiple problems with your setup:

1. You need to change your SQL query to SELECT ID from HHDIXI.Data, otherwise you will get LAST_NAME entries in the ID variable

2. You need to change your JSONPath query to $..data[*].id and set "Match No" to -1 otherwise you will get only first ID into idfromAPI variable

   Use Debug Sampler and View Results Tree listener combination to see what variables are being generated by the HTTP and JDBC request samplers.

3. If you want to compare each ID from API to each ID from the DB in one shot the only option is JSR223 Assertion

   Example code:

   1.upto(vars.get('ID_#') as int, { index ->
       def expected = vars.get('ID_' + index)
       def actual = vars.get('idfromAPI_' + index)
       if (expected != actual) {
           AssertionResult.setFailure(true)
           AssertionResult.setFailureMessage('Data mismatch, in the database: ' + expected + ', in the API: ' + actual)
       }
   })
   

   and demo: