JSON REST API: Validate multiple responses against a DataSource

kktulasi404kktulasi404 Posts: 10

Hi All,

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.


1|AB|JK|"A", "B", "C"|"D", "H"
2|CD|LM|"O|"L", "H","M"


  • kktulasi404kktulasi404 Posts: 10

    Sample JSON o/p: {
    "PV_COLUMN" : null,
    "CHILD_COL1" : "XC",
    "CHILD_COL2" : "TEST",
    "CHILD_COL3" : null
    "CHILD_COL1" : "ER",
    "CHILD_COL2" : "TEST",
    "CHILD_COL3" : "18-JAN-2017"

  • OmarROmarR Posts: 231 admin

    Good Morning kktulasi404 :)

    sips coffee

    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).

  • kktulasi404kktulasi404 Posts: 10

    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.


    So, when I pass value 1 as parameter I am expecting two records.
    My JSON response o/p is -
    "INPUT_VALUE" : null,
    "OUTPUT_TAB" : [
    "COLA" : "A",
    "COLB" : "B",
    "COLC" : null
    "COLA" : "E",
    "COLB" : "F",
    "COLC" : "18-JAN-2017"
    Tree looks like

    I have seen some exampled for using multiple sheets for the child records is that can be applicable for JSON assertor?

  • sunkarabhanusunkarabhanu Posts: 8

    Hi Omar, Do you have a solution of this post? I have the same issue and would like to find the steps needed to solve it.

  • OmarROmarR Posts: 231 admin
    edited August 2017

    Good morning fellow SOAtest users!

    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.


    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.

  • kktulasi404kktulasi404 Posts: 10

    Thank you will try and let you know

  • kktulasi404kktulasi404 Posts: 10

    I have tried the above steps at Step 6 it is giving error as Invalid JSON.

  • OmarROmarR Posts: 231 admin

    Within your JSON Assertor, under the "Expected JSON" tab, what does the output look like? Would you kindly post a screenshot of the payload shown in Literal mode?

  • kktulasi404kktulasi404 Posts: 10

    Hi Omar,

    Attached is the Messaging Client Structure. In the JSON assertor nothing is been displayed for now as the messaging client is not executed.
    When executed the Invalid JSON error occurred.

  • saileesailee Posts: 7

    @OmarR :
    I did above steps and in expected JSON tab of JSON assertor , i am getting blank root i.e. {}
    Could you please reply what will be the issue in this case.

    error message i m getting is :
    Error Message:
    DataSource: Actual_Data(Extracted) (row 1): Invalid JSON

    Additional Details:
    Source: Messaging Client Output

  • OmarROmarR Posts: 231 admin

    Good morning Sailee,

    Please ensure that the assertor is chained to the output of the "request traffic" of the Messaging client.

  • saileesailee Posts: 7
    edited August 2017

    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.

  • jakubiakjakubiak Posts: 738 admin

    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.

  • saileesailee Posts: 7

    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.

  • jakubiakjakubiak Posts: 738 admin

    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.

    If I have misunderstood your scenario, please clarify.

  • saileesailee Posts: 7

    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.

    Let me know your thoughts on this.

  • Ireneusz SzmigielIreneusz Szmigiel Posts: 219 ✭✭✭
    edited August 2017
  • saileesailee Posts: 7

    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

  • jakubiakjakubiak Posts: 738 admin

    Trying a single excel sheet seems like a good way to go - let us know how it works for you!

  • saileesailee Posts: 7
    edited September 2017

    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

        Object str=*mongoDB.getKeywords(  input, context);* //got null pointer exception 
        Application.showMessage("str: " + str);
        return str;

    I got stuck here ....
    I want to call below method which i have written in mongodb
    public static Object getKeywords(Object input,ExtensionToolContext context)
    throws SQLException, JSONException {
    //String URL_MONGODB = context.getEnvironmentVariableValue("URL_MONGODB");
    int PORT_MONGODB = Integer.parseInt(context.getEnvironmentVariableValue("PORT_MONGODB"));

    please suggest how can I call above method in my utility class,.
    Is this correct approach.

    Thank you in advance.

  • jakubiakjakubiak Posts: 738 admin

    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.

  • vashi83vashi83 Posts: 1
    edited February 2018

    Hi OmarR,
    I followed all the steps you mentioned and it worked fine for the first time.
    When I run the whole test sequentially again. it is not asserting all the values but only the first one.

    any suggestion will be of great help.

Sign In or Register to comment.