Loop thru data using json data bank using xpath- soatest Version: 9.10.8.20191027

Michelle

This is what I have for my json data bank
for $index in (1 to count(/root) )
return (if(exists(
/root/pageContents[1]/item[$index]/id[1]))
then
/root/pageContents[1]/item[$index]/id[1]/text() || '-'||
/root/pageContents[1]/item[$index]/position[1]/text()
else "NA")

This is working fine if there is only one record in the section. If there are multiple records then it skips them.
In the sample data you can see pageContents contains 2 id {1484,1115} and pageContents-1484 has multiple contentReference values. I need to be able to capture all of the data and save to a writable data table.

Sample Data:
{
"id": 1,
"name": "home",
"version": "v1"
"pageContents": [
{"id": 1484,
"position": 1,
"bucket": {
"id": 9,
"contentLimit": 40,
"contentReferences": [
{"id": 5175,"position": 1,
"contentReference": {
"id": 2959,"isFetchable": true}},
{"id": 35,"position": 2,
"contentReference": {
"id": 3,"isFetchable": true}}]
},},
{
"id": 1115,
"position": 2,
"bucket": {
"id": 503,
"contentLimit": 40,
"contentReferences": [
{"id": 749,"position": 1,
"contentReference": {
"id": 506,"isFetchable": true}},
{"id": 746,"position": 2,
"contentReference": {
"id": 503,"isFetchable": true}},
{"id": 743,"position": 3,
"contentReference": {
"id": 284,"isFetchable": true}},
{"id": 806,"position": 4,
"contentReference": {
"id": 563,"isFetchable": true}},

benken_parasoft

Remove the "[1]" from your XPaths?

Michelle

for $index in (1 to count(/root) )
return (if(exists(
/root/pageContents[1]/item[$index]/id[]))
then
/root/pageContents[1]/item[$index]/id[]/text() || '-'||
/root/pageContents[1]/item[$index]/position[]/text()
else "NA")
I removed 'ALL' of the "[1]" and i got an error message invalid XPath expression: net.sf.saxon.trans.XPtahException: Unexpected token "]" at start of

So i tried adding [*] and it returned NA. I am very new at writing these.

williammccusker

Hi,

Is the desire to collect up all the "id" and "position" values as a string and put each of those into the writable data source? So for the example input you want the following values to be extracted, correct?

1484-1
1115-2

williammccusker

I was able to use this xpath

for $index in (1 to count(/root/pageContents/item/id) )
return
/root/pageContents/item[$index]/id/text() || '-' ||
/root/pageContents/item[$index]/position/text()

With a slightly smaller version of the example payload that had just the id and position fields of the pageContents to use the test button in the data bank and saw that it found multiple items. In the original Xpath the loop condition was on "/root" which I believe was only ever returning "1" but what I think what is actually desired is to loop over the count of the either "id" or "position" under pageContents.

For the case when nothing is found, would it make sense use to use the "Option" in the data bank to "Extract missing elements as" rather than building that logic into the xpath?

Michelle

Yes and then say under
1484-1 I would also want the
contentReferences 5175-1 with
contentReference 2959 -true
contentReferences 35 -2 with
contentReference 3 -true

1115 -2
contentReferences 749 - 1 with
contentReference 506 - true
contentReferences 746 - 2 with
contentReference 503 -true

Does that make sense

williammccusker

Hi,

The desire is to get those all those combined fields into the writable data source and then use them to do what? It would help if I had an idea of what the plan is to use those values for.

Michelle

The plan is really to validate that the data in the database is as expected. I wanted to be able to run my queries, have the data dumped somewhere and they be able to present to the developer and let them know that the data is as expected

Sample Data in my writable table
id 1
name home
version v1
visibilityStatus PUBLIC
pageContents 1484-1-100-0-true-false 1115-2-100-0-true-false
bucket 9-40-0-true-true-false-false-false-false-PUBLIC-LISTINGS-DEFAULT 9-40-0-true-true-false-false-false-false-PUBLIC-LISTINGS-DEFAULT
contentReferences 5175-1 34-1
contentReference 2959-true-Live -Live -http://xyza.com/ 3-true-Live -Live -http://xyza.com/

williammccusker

Hi,

To validate the data is as expected would you be able to use a Diff Tool instead? There are a number of different tools that can be used to do validation of a response.

https://docs.parasoft.com/display/SOA20202/Validation+Tools

Or is the only way to validate the data to dump it and have the developer perform the validation?

Michelle

I would still need to all the data in order to use the diff tool. Correct me if im wrong. I have never used that tools before.

williammccusker

Hi,

You can copy and paste the entire response payload into the Diff tool. For this case you would sent the Diff mode: "JSON" in the drop down box, then in the text area paste in the entire JSON payload that should be returned.

Then when the test is run the Diff tool will check that the received response exactly matches what is configured in the Diff tool. If it finds a field or value that does not match, or some item was added or remove it will report a failure with information about how the received response was different that what was expected.

Michelle

Ok so I think I may have gotten that to work. But for the initial run where i do not have a response. Is there are way to do what i wanted to above?

williammccusker

Hi,

Well for the initial run do you need to have anything? Can you run the test once and then create the diff tool based on the response? If you have to dump the response would it acceptable to just write the entire JSON response to a file?

https://docs.parasoft.com/display/SOA20202/Write+File

If you had to reduce the data then perhaps a script might be better and outputting it in the format you want or an XSLT transform but both of those are far more complex options that the Diff Tool of the Write File tool.

Michelle

So I have tried every possible thing thing I can think of and I get the following errors:
I checked to make sure I could write to the directory I was trying to and I could. I tried searching for why it was happening and did not find anything. So I am still back at the how can I get all of my data. Thanks so much for your patience

Error Message:
DataSource: Projections (row 1): Error writing from Get Page /{version}/pages/{id}{?projection}
Output to datafile.csv:
Stream closed.

Michelle

Good Morning

I think i have figured out most of what I want to do. One left thing I need to have 2) added into 1). I believe i need to do a loop and I am getting the brackets incorrect. Can you help? Thanks

1)
for $index in (1 to count(/root/pageContents[1]/item[]) )
return (if(exists(
/root/pageContents[]/item[$index]/id[1]))
then
/root/pageContents[*]/item[$index]/id[1]/text()
else "NA")

2)
for $index in (1 to count(/root/pageContents[1]/item[]) )
return
(if(exists(
/root/pageContents[]/item[]/bucket[]/contentReferences[$index]/item[]/id[1] ))
then
/root/pageContents[]/item[]/bucket[]/contentReferences[$index]/item[*]/id[1]/text()
else "NA")

williammccusker

Hi,

Is the purpose of those XPaths to just collect up the id's? Or do you still need to extract text from sibling fields? For example before it was returning the "id" and "position" field but these new paths appear to only be collecting the ids.

Michelle

So based on the sample data this is what i currently see:

id name version visibilityStatus isOverride pageContentsId bucketId contentReferencesId position contentReferenceId isFetchable
1 home v1 PUBLIC FALSE 1484 9 5175 1 2959 TRUE
1115 503 35 2 3 TRUE
215 2 749 1 506 TRUE
6842 1619 746 2 503 TRUE
6281 1460 743 3 284 TRUE
1487 122 806 4 563 TRUE
224 14 914 5 629 TRUE
218 12 1523 6 1040 TRUE
221 3 2690 7 1652 TRUE
230 10 8 1 11 TRUE

And this is what i want to see
id name version pageContentsId position bucketId contentLimit contentReferencesId position contentReferenceId isFetchable
1 home v1 1484 1 9 40 5175 1 2959 TRUE
NA NA NA NA NA NA NA 35 2 3 TRUE
NA NA NA 1115 2 503 40 749 1 506 TRUE
NA NA NA NA NA NA NA 746 2 503 TRUE
NA NA NA NA NA NA NA 743 3 284 TRUE
NA NA NA NA NA NA NA 806 4 563 TRUE
NA NA NA NA NA NA NA 914 5 629 TRUE
NA NA NA NA NA NA NA 1523 6 1040 TRUE
NA NA NA NA NA NA NA 2690 7 1652 TRUE
NA NA NA 215 3 2 100 8 1 11 TRUE

williammccusker

Hi,

I am not sure that xpath is going to work very well for that use case. Is the desire to take that data and write it out to a file? Perhaps a groovy script might give you better control to build up the desired output and then using a write file tool to write the results from the script to a file?

Here is a bit of an example of using a script to process the json message using groovy. It is missing the parts to build up the desired result but hopefully it gives you and idea of how the script would move through the json.

import com.parasoft.api.Application
import groovy.json.*

def process(input, context) {
    def jsonSlurper = new JsonSlurper()
    def object = jsonSlurper.parseText(input)

    pageContents = object["pageContents"] 
    Application.showMessage(pageContents.toString())

    for (page in pageContents) {
        id = page["id"]
        Application.showMessage(String.valueOf(id)) 

        bucket = page["bucket"] // Map
        contentReferences = bucket["contentReferences"]
        Application.showMessage(contentReferences.toString())
    }

    return "some desired string"
}

Michelle

Thank you so much. I got a chance to try out your suggestion and it will bring me back my data. As I have never used groovy before today I am still trying to figure out how to write it into a file. I am also looking at how I may need to tweak it if my response has more than one record. Thank you so much for your patience and help. It is very appreciated.

williammccusker

Hi,

For writing to a file you could return the data you want as a String and then chain a Write File Tool to the output of the Extension Tool. Then you wouldn't need to code the part to write to a file.

Michelle

I have been trying to get this to work for a while now. I am getting myself lost (not a programmer ) Are there samples online that i can follow? Im assuming I would need to string the values together but I have never used the write file tool and extension tool. I am not following how to chain them together. Thanks