cancel
Showing results for 
Search instead for 
Did you mean: 

Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

SOLVED
davecoleman
Contributor

Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

Hi all,

I have a datasource for JSON which looks at the response of a REST Response is outputting the nested array of IDs within it.

there are e.g. 8 values output for this response of "bookingID". How do I pass this to a dataSink to output in an Excel file? I went to complete this but only the last value is output.

davecoleman_0-1613587344267.png

 

1 ACCEPTED SOLUTION

Accepted Solutions
sonya_m
Community Manager

Re: Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

Richie, nmrao, thank you for your help!

 

Hi @davecoleman ! Were you able to solve this? Please share your solution with the Community if you were.

Otherwise, please either provide nmrao with the info they are requesting or, alternatively, contacting support can bring clarity to this, as well. 


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️

View solution in original post

7 REPLIES 7
richie
Community Hero

Re: Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

Hey @davecoleman,

Could you confirm exactly what the json looks like please? I always struggle with the row path and column path fields on this thing.

Ta,

Rich
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
davecoleman
Contributor

Re: Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

Hi @richie 

Hope this helps. Don't want to give away the full listing.

 

I ran the full functional test and it only output 1 value when there should be a few thousand. it comes down to an array on the output from this datasource. I can look towards a Groovy script but if ReadyAPI provides it without scripting, even better.

davecoleman_0-1613663852341.png

 

richie
Community Hero

Re: Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

Hey @davecoleman,

Cheers i just needed to appreciate the basic structure, so the bookings attribute is either an object or array and it contains repeating objects which contains a bookingId attribute and you want to save off the bookingId attribute values.....right?

Yeah, its gonna need some groovy i reckon. The way you have setup the datasource has a single transfer property....each one would park off a single bookingId. I dont see any way of doing it....especially as you say there could be many and the number isnt fixed.

Dunno if anyone else can think of any other non groovy options, but i cant!

Ta

Rich
if this helped answer the post, could you please mark it as 'solved'? Also if you consider whether the title of your post is relevant? Perhaps if the post is solved, it might make sense to update the Subject header field of the post to something more descriptive? This will help people when searching for problems. Ta
davecoleman
Contributor

Re: Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

thanks @richie . ill do some work on this and report back....

davecoleman
Contributor

Re: Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

Hi,

So I've used JsonSlurper to view the response and have extracted the related records to the Log. 

Now how do I pass each into an array / list to export via the Datasink? 

 

 

import groovy.json.*
import groovy.util.*

def json='[{    "message" : "Success",    "bookings" : [       {          "bookingId" : 60002172,          "bookingDate" : "1900-01-01T00:00:00"       },       {          "bookingId" : 59935582,          "bookingDate" : "1900-01-01"       },       {          "bookingId" : 53184048,          "bookingDate" : "2019-01-15",          "testId" : "12803798123",          "overallScore" : "PASS"       },       {          "bookingId" : 53183765,          "bookingDate" : "2019-01-15T13:45:00"       },       {          "bookingId" : 52783312,          "bookingDate" : "1900-01-01"       }    ] } ]'

def response = context.expand( json )
def parsedjson = new groovy.json.JsonSlurper().parseText(response)
log.info parsedjson
log.info " Count of records returned: " + parsedjson.size()
log.info " List of bookingIDs in this response: " + parsedjson.bookings*.bookingId

 

 

Any pointers would be appreciated. I could also work with exporting the list here on each iteration of the loop to a CSV file. but it would be good to know how a property could be created to use.

nmrao
Community Hero

Re: Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

What is your use case?
Are you going to use the saved data in any way later? And how?
For the above sample data how you want to see in the saved file? Sample please with columns and rows?


Regards,
Rao.
sonya_m
Community Manager

Re: Extract nested JSON response values in DataSource for JSON - how to export to file / excel?

Richie, nmrao, thank you for your help!

 

Hi @davecoleman ! Were you able to solve this? Please share your solution with the Community if you were.

Otherwise, please either provide nmrao with the info they are requesting or, alternatively, contacting support can bring clarity to this, as well. 


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️

View solution in original post

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors