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

MichelleMichelle Posts: 11

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}},

Comments

  • benken_parasoftbenken_parasoft Posts: 958 ✭✭✭

    Remove the "[1]" from your XPaths?

  • MichelleMichelle Posts: 11

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

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭

    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

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭
    edited January 28

    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?

  • MichelleMichelle Posts: 11

    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

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭

    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.

  • MichelleMichelle Posts: 11
    edited January 28

    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/

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭

    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?

  • MichelleMichelle Posts: 11

    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.

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭

    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.

  • MichelleMichelle Posts: 11

    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?

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭

    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.

  • MichelleMichelle Posts: 11

    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.

  • MichelleMichelle Posts: 11

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

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭

    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.

  • MichelleMichelle Posts: 11

    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

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭

    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"
    }
    
  • MichelleMichelle Posts: 11

    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.

  • williammccuskerwilliammccusker Posts: 472 ✭✭✭

    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.

  • MichelleMichelle Posts: 11

    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

Sign In or Register to comment.