Using JMeter, how to compare table data with JSON data?

Question

I've got a task to compare all data fetched from postgreSQL table and all data located on a website that uses REST API (HTTP request with JSON data) in order to see which is missing.

I fetch data from postgreSQL using JDBC request (SELECT * FROM exampleTable), data is in standard SQL table format.

And I get REST API data using HTTP request sampler, data is in JSON format:

{"records":[{"id":"rec6iT8M0YFZc9kxf","fields":{"Birthday":"2010-09-01","Gender":"Female","Currently In Pasture":["rec7hRbjrgTaKWdCs"],"Breed":"Jersey","Weight":1800,"Name":"Jerri","Attachments":[{"id":"attbz","url":"https://IaGSYtK8SlS.jpg","filename":"Jersey_cow.jpg","size":1555316,"type":"image/jpeg","thumbnails":{"small":{"url":"https://_Jersey_cow.jpg","width":27,"height":36},"large":{"url":"https://dl/S4tl79.jpg","width":256,"height":341}}}]}

etc etc

both requests should have the same format of data (same "columns" for comparison > name, gender, breed, weight, etc)

I've tried using JSON Extractor post processor element to get individual variables from HTTP response and compare them to individual variables from JDBC request using the Response Assertion element but with no luck

I either get this error: Assertion failure message: java.lang.ClassCastException: java.util.ArrayList cannot be cast to java.lang.String (probably cause I am using the whole SQL data set instead of data from individual columns)

or something like: Assertion failure message: Test failed: text expected to match /${gender}/

I dont know how to successfully assign variables to data from SQL table and JSON response and then matching those variable values (seeing which entries are absent from what REST API fetches)

is there any easy way to do this using jmeter GUI and not having to rely on beanshell/groovy or any other kind of scripting (so just by using the elements available in jmeter) ?

thank you!

Dmitri T Dmitri T · Accepted Answer · 2018-11-19T13:18:41

Not knowing your database structure it is quite tricky to come up with exact solution, however you can convert JDBC Request output into a JSON using JSR223 PostProcessor

Example:

Given the following JDBC Request sampler configuration:
Which produces the following output:
I can convert it into JSON using the following Groovy code:
```
def result = vars.getObject('result')
def json = new groovy.json.JsonBuilder(result).toPrettyString()
log.info(json)
```
as you can see it contains column names along with the values, you should be able to use it for assertions.

More information: Debugging JDBC Sampler Results in JMeter

Using JMeter, how to compare table data with JSON data?

1 Answers