JSON REST API: Validate multiple responses against a DataSource

I am trying to validate the JSON response against an Excel sheet for each INPUT value passed.

I am using the JSON assertor and for each attribute have assigned the corresponding column name of the Excel.
But, for the attributes where it is a ARRAY/LIST or TABLE of values it is failed saying no value found.

Depending on the type of assertion you wish to make (E.g, Value, Structure, etc) , you will need to ensure you select the appropriate node in the JSON tree.

In my example below, I wish to create a "String Comparison" Assertion, therefore, I will need to select the appropriate element node that contains a string value. SOAtest makes it easy to identify the eligible nodes (Element Values) by placing an "S" next to the node. Similarly, if you were to create an "Occurrence" Assertion, SOAtest would place an "O" next to the eligible nodes (Element names).

Thanks Omar. I have selected the Value Occurrence assertion but it says Input value is not an Integer.

In Excel Sheet: I have given values as below and parameterized in the URL for the input_value and JSON assertor for the o/p values.
So, the first input will return two rows similarly if I have 2nd input in row 3 then I will again have multiple rows.

Thank you very much for clarifying your use case. In order to assert the values of repeated Child Elements in Sequence, we will need to take the time to implement the steps required for such an assertion. Below are the steps to accomplish this. Please refer to the screenshot below as you implement your scenario to confirm your setup is correct. This will work for both XML and JSON payloads by using the appropriate tools.

STEPS:

1) Extract the desired Element values in Sequence using a JSON databank and store them in a writable-datasource. This will isolate the desired values you wish to Assert. **Note: **you will need to modify the XPATH to extract all of the repeated elements in the sequence. When "Evaluating the Xpath", ensure that all the element values are shown in the "Result" Window".

2) Run your .tst thus far to ensure that your extracted Values are populated in the Writable-datasource successfully.

3) Now, in order for your JSON Assertor to assert the Extracted Element values, we will need to pass the Writable-datasource values through a Request. We do this by adding a new Test Suite to your current .tst and adding a Messaging client to it. We then Parameterize the payload with the values stored in the Writable-datasource.
For example, choose Literal for your Input mode in your Request and insert some JSON content like the following:{"data": ""}

After inserting a payload similiar to the one above, Change your input-mode to FORM-JSON. This will now allow you to Parameterize your Data values with the values in your Writable Datasource.

Similiary, for XML payloads, you could insert <data/> in Literal mode and then change to FORM-XML.

4) Set your Messaging Client's Transport to NONE. By setting the transport to NONE, we are configuring the Messaging Client to run any tools (Databank, Assertor, etc) associated with the test, but our actual request will NOT be sent anywhere. By selecting NONE for our transport, we are essentially saying "Do-not-send-to-an-Endpoint, just execute my chained tools!"

5) Chain a JSON assertor to the Messaging Client.
Please ensure that the assertor is chained to the output of the "request traffic" of the Messaging client.

6) Run your .tst to populate the assertor and configure it to assert against the values from your Table of Expected Values.

7) Finally, Navigate to the Top Parent Test Suite>Execution Options>Advanced Options and select Flat (Lockstep) for your Multiple Data Source Iteration. This will allow for Test execution to iterate over rows in different data sources together at the same time MEANING it will assert the rows from the table of expected values against the Writable-datasource in order!

Please try this on your end and let me know if you run into any trouble with any specific step! I would appreciate your feedback on this.

Thank you so much @OmarR . its worked.
My next step is to pass value from data source and fetch the response in another data source. I am using java class to pass query from data source to my database connectivity java class and now i want to pass this database response value to excel sheet in data source.but in extension tool at time i can pass only one data source.
I want to call multiple data source in one test case.

I am not sure how your tests are set up - but is it possible to set up a Text or XML Data Bank that extracts the value from the result of your database query? Data banks store values in a type of "virtual" data source that can be accessed by column name even if you have a specific explicit data source configured in your test.

no , I am passing result of database query into excel sheet. and compare to above scenario my "expected Data" is in excel as data source.
I want to know how can I send "Database Account" properties in my extension tool having java class so that i can use 1 java class to run multiple databases by fetching database property.

Is this your scenario: you have a Database Account property specified in your .tst file. You also have an Extension Tool that uses a Java class to make a database query. You would like the Java class to use the same database settings as you have specified in your Database Account?

If this is the case, I would put the data that you have specified in your Database Account into environment variables. Then, instead of specifying the values directly in the Database Account, you would use the ${varName} syntax to reference the environment variables. Then in your Java class, you can use the Parasoft API com.parasoft.api.ScriptingContext.getEnvironmentVariableValue() to reference the same environment variables. The nice thing about this approach is you can then modify the environment variables, and both places in your scenario will get the updated values.

Hi @jakubiak : thank you so much for your explanation. I tried above steps and did some more R&D , I don't see use of database account in my case as i am going to pass it from environment variables in my java code. also Mongo DB does not support connectivity in soatest using database account or DB.tst or datasource as database.

My next step is to map actual json and expected (database ) response using jsonpath in excel sheet and validate it in only one excel sheet. i.e. means my datasource will be one excel sheet for actual and expected response.

I am getting actual response in json and mongodb database response is in json.
I want to compare these two responses using json path in excel sheet. and pass this result to soatest to make test pass/fail as per validation

Hi @jakubiak as I have written in above i have created draft REST API automation framework.
I have created one utility class which will have three methods:
1. getactualResponse : i will fetch data from the class which will have response from writable datasource
2. getExpectedRepsosne : i will fetch data from the my generic class for mongodb connectivity.
3. CompareActualExpected : using if else i will compare actul and expected resp using jsonpath.
public Object getExpectedResponseFromDB () throws Exception, SQLException
{

What is the name of the class that has the getKeywords() method? It looks to me like you are not referencing it correctly - try referencing it using the fully qualified name of the class, for example com.company.mypackage.MongoDB.