Forum Discussion

Stalsy's avatar
4 years ago

Nested DataSource loops

Hi, I am new to ReadAPI and API testing, so I’m learning as I go along…

I’m using data driven testing and have the following issue:

My rest request uses the customerID to get information about the customer’s accounts.  So, the response is a json array that in its simplest form would look something like below (the real one has much more information per account):


   "customerId" : “11111”,

      "accounts" : [


            "accountId" : "1",

            "creationDate" : "2001-04-10",

            "currentBalance" : "10.00"



            "accountId" : "2",

            "creationDate" : "2003-01-30",

            "currentBalance" : "20.00"



            "accountId" : "23",

            "creationDate" : "2020-04-27",

            "currentBalance" : "30.00"





My problem is that one customer may have 1 account and the next one 30 accounts.  Data driven testing works with a fixed number of rows per iteration.  In previous tests I used the listagg function to collect the information of multiple rows in a single row, but listagg will not work here because there is a lot of information per account, plus I have customers with more than 100 accounts!


So the way I solved this is with nested dataSource loops:

  • The first SQL returns a bunch of customerIDs and the number of active accounts each customer has (dataSource1).
  • Then comes the REST request for a customer ID
  • Then I have a groovy script to test that the API ahs returned the number of accounts I expected
  • Then another SQL script to fetch the customer’s account information: accountID, creation Date and currentBalance (dataSource2).
  • Following is another groovy script to check that the accountID, creation Date and currentBalance of dataSource2 exist in the REST response
  • Loop to Datasource2
  • Loop to REST request


This solution works well but obviously apart from the initial query that it is fired only once,  it also fires a database query once per customerID, so it is not as efficient as my previous tests where a single DB query was enough to satisfy the whole test.

My question is, has anyone used an alternative method that might be more time efficient?



8 Replies

  • nmrao's avatar
    Champion Level 3
    Please the screen shot of test case.

    Is it that you need to create request per customer and send the request? or you can send all the customers into single request and send?

    Is it that you fetch the data from DB in order create the request?
  • nmrao's avatar
    Champion Level 3
    Is it possible to get the data in one pass Instead of two passes?
    • richie's avatar
      Community Hero

      Im probably not understanding something obvious here, but if the issues is the customers can have 1 to many accounts, could you not just have an initial query to retrieve x number or customer records where number or accounts is =1?

      Or is it part of your testable requirement you need to cover off scenarios where there arw multiple accounts per customer?


      • Stalsy's avatar

        Most customers have multiple accounts. I want to test as many scenarios as possible.  I could write a test to select customers with 1 account, another to test customers with 2 accounts, another test with 3 and so on.  Where do I stop? How many tests will I need?  We have a customer with 130 accounts! All my tests work with random selections of customers so that as I run them over and again, I'm testing different customers.  If I fix the number of accounts, then I'll certainly excluding the customers that don't have that number of accounts! 

    • Stalsy's avatar

      Of course I can get all the data in one pass, but it will look like the following, if say the Customer1 has one account, customer2 has 3 accounts, customer3 has one account and customer4 has four accounts :

      Customer1, account11, balance11









      I don't know how to process this data.  The API accepts one customerID and returns a json containing information on all the accounts for the customer.  

      • nmrao's avatar
        Champion Level 3
        Based on the data you shared, it seems you already did good.

        However, it is possible to do entirely in single groovy script too if one wishes to do so. It will be little lengthy.

        Are building json after getting the the query result for each customer id?
        And how do you verify the accounts for the each customer id? Compare against SQL result?