Forum

HomeHomeCoreCoreGeneralGeneralJSON Query - Extracting Data From Parent NodeJSON Query - Extracting Data From Parent Node
Previous
 
Next
New Post
11/9/2015 10:53 PM
 
Hi Kevin,
I've been re-visiting some of my OWS projects recently because one of my datasources is changing from XML to JSON web service responses. So I started trying to extract data from the resulting JSON object but quickly realised that I wasnt able to extract the data in the same way.


Take the example request below.


<JSON>
<PATH>http://maps.google.com/maps/api/geocode/json?latlng=-33.873038,151.20563&sensor=false</PATH>
<GET>
<CONTENTTYPE></CONTENTTYPE>
<QUERY></QUERY>
<HEADERS></HEADERS>
<BODY></BODY>
</GET>
<ROOT>results</ROOT>
<COLUMNS>
<COLUMN><NAME>formatted_address</NAME><XPATH>formatted_address</XPATH></COLUMN>
<COLUMN><NAME>status</NAME><XPATH>../status</XPATH></COLUMN>
</COLUMNS>
</JSON>


This request returns 8 "results" rows and I can successfully extract the "formatted_address" data item using the COLUMNS specification above.  BUT, I cant seem to get the "status" data item from the parent node.  I've tried various ways to get to the parent but they all fail.  The code above simply returns "r2i.OWS.Newtonsoft.Json.JavaScriptObject" in the status. 


With that said, can you advise if there is any way to extract the "status" and the formatted_address.  Obviously, the value in "status" will be the same for each of the rows returned but that's exactly what I want.  


Here is the JSON response from the request above..


{
  "results": [
    {
      "address_components": [
        {
          "long_name": "456",
          "short_name": "456",
          "types": [
            "street_number"
          ]
        },
        {
          "long_name": "Kent Street",
          "short_name": "Kent St",
          "types": [
            "route"
          ]
        },
        {
          "long_name": "Sydney",
          "short_name": "Sydney",
          "types": [
            "locality",
            "political"
          ]
        },
        {
          "long_name": "New South Wales",
          "short_name": "NSW",
          "types": [
            "administrative_area_level_1",
            "political"
          ]
        },
        {
          "long_name": "Australia",
          "short_name": "AU",
          "types": [
            "country",
            "political"
          ]
        },
        {
          "long_name": "2000",
          "short_name": "2000",
          "types": [
            "postal_code"
          ]
        }
      ],
      "formatted_address": "456 Kent St, Sydney NSW 2000, Australia",
      "geometry": {
        "location": {
          "lat": -33.873038,
          "lng": 151.20563
        },
        "location_type": "ROOFTOP",
        "viewport": {
          "northeast": {
            "lat": -33.8716890197085,
            "lng": 151.2069789802915
          },
          "southwest": {
            "lat": -33.8743869802915,
            "lng": 151.2042810197085
          }
        }
      },
      "place_id": "ChIJ4RM5HjyuEmsRd1HHgZi_85M",
      "types": [
        "street_address"
      ]
    },
    {
      "address_components": [
        {
          "long_name": "Sydney",
          "short_name": "Sydney",
          "types": [
            "locality",
            "political"
          ]
        },
        {
          "long_name": "New South Wales",
          "short_name": "NSW",
          "types": [
            "administrative_area_level_1",
            "political"
          ]
        },
        {
          "long_name": "Australia",
          "short_name": "AU",
          "types": [
            "country",
            "political"
          ]
        }
      ],
      "formatted_address": "Sydney NSW, Australia",
      "geometry": {
        "bounds": {
          "northeast": {
            "lat": -33.8561177,
            "lng": 151.2229549
          },
          "southwest": {
            "lat": -33.879786,
            "lng": 151.1971341
          }
        },
        "location": {
          "lat": -33.8674769,
          "lng": 151.2069776
        },
        "location_type": "APPROXIMATE",
        "viewport": {
          "northeast": {
            "lat": -33.8561177,
            "lng": 151.2229549
          },
          "southwest": {
            "lat": -33.879786,
            "lng": 151.1971341
          }
        }
      },
      "place_id": "ChIJP5iLHkCuEmsRwMwyFmh9AQU",
      "types": [
        "locality",
        "political"
      ]
    },
    {
      "address_components": [
        {
          "long_name": "Sydney",
          "short_name": "Sydney",
          "types": [
            "colloquial_area",
            "locality",
            "political"
          ]
        },
        {
          "long_name": "New South Wales",
          "short_name": "NSW",
          "types": [
            "administrative_area_level_1",
            "political"
          ]
        },
        {
          "long_name": "Australia",
          "short_name": "AU",
          "types": [
            "country",
            "political"
          ]
        }
      ],
      "formatted_address": "Sydney NSW, Australia",
      "geometry": {
        "bounds": {
          "northeast": {
            "lat": -33.4245981,
            "lng": 151.3426361
          },
          "southwest": {
            "lat": -34.1692489,
            "lng": 150.502229
          }
        },
        "location": {
          "lat": -33.8674869,
          "lng": 151.2069902
        },
        "location_type": "APPROXIMATE",
        "viewport": {
          "northeast": {
            "lat": -33.4245981,
            "lng": 151.3426361
          },
          "southwest": {
            "lat": -34.1692489,
            "lng": 150.502229
          }
        }
      },
      "place_id": "ChIJP3Sa8ziYEmsRUKgyFmh9AQM",
      "types": [
        "colloquial_area",
        "locality",
        "political"
      ]
    },
    {
      "address_components": [
        {
          "long_name": "2000",
          "short_name": "2000",
          "types": [
            "postal_code"
          ]
        },
        {
          "long_name": "Sydney",
          "short_name": "Sydney",
          "types": [
            "colloquial_area",
            "locality",
            "political"
          ]
        },
        {
          "long_name": "New South Wales",
          "short_name": "NSW",
          "types": [
            "administrative_area_level_1",
            "political"
          ]
        },
        {
          "long_name": "Australia",
          "short_name": "AU",
          "types": [
            "country",
            "political"
          ]
        }
      ],
      "formatted_address": "Sydney NSW 2000, Australia",
      "geometry": {
        "bounds": {
          "northeast": {
            "lat": -33.8535146,
            "lng": 151.2229549
          },
          "southwest": {
            "lat": -33.8854162,
            "lng": 151.1971341
          }
        },
        "location": {
          "lat": -33.8657305,
          "lng": 151.20733
        },
        "location_type": "APPROXIMATE",
        "viewport": {
          "northeast": {
            "lat": -33.8535146,
            "lng": 151.2229549
          },
          "southwest": {
            "lat": -33.8854162,
            "lng": 151.1971341
          }
        }
      },
      "place_id": "ChIJP-njCjuuEmsRcIe6P2t9ARw",
      "types": [
        "postal_code"
      ]
    },
    {
      "address_components": [
        {
          "long_name": "Sydney CBD",
          "short_name": "Sydney CBD",
          "types": [
            "political"
          ]
        },
        {
          "long_name": "Sydney",
          "short_name": "Sydney",
          "types": [
            "colloquial_area",
            "locality",
            "political"
          ]
        },
        {
          "long_name": "New South Wales",
          "short_name": "NSW",
          "types": [
            "administrative_area_level_1",
            "political"
          ]
        },
        {
          "long_name": "Australia",
          "short_name": "AU",
          "types": [
            "country",
            "political"
          ]
        }
      ],
      "formatted_address": "Sydney CBD, Sydney NSW, Australia",
      "geometry": {
        "bounds": {
          "northeast": {
            "lat": -33.8535146,
            "lng": 151.2229549
          },
          "southwest": {
            "lat": -33.8854162,
            "lng": 151.1860603
          }
        },
        "location": {
          "lat": -33.8708464,
          "lng": 151.20733
        },
        "location_type": "APPROXIMATE",
        "viewport": {
          "northeast": {
            "lat": -33.8535146,
            "lng": 151.2229549
          },
          "southwest": {
            "lat": -33.8854162,
            "lng": 151.1860603
          }
        }
      },
      "place_id": "ChIJKaeYMj-uEmsRAgZ4clX6UO8",
      "types": [
        "political"
      ]
    },
    {
      "address_components": [
        {
          "long_name": "New South Wales",
          "short_name": "NSW",
          "types": [
            "administrative_area_level_1",
            "political"
          ]
        },
        {
          "long_name": "Australia",
          "short_name": "AU",
          "types": [
            "country",
            "political"
          ]
        }
      ],
      "formatted_address": "New South Wales, Australia",
      "geometry": {
        "bounds": {
          "northeast": {
            "lat": -28.156192,
            "lng": 153.6387812
          },
          "southwest": {
            "lat": -37.5050318,
            "lng": 140.9992123
          }
        },
        "location": {
          "lat": -33.864174,
          "lng": 151.2052868
        },
        "location_type": "APPROXIMATE",
        "viewport": {
          "northeast": {
            "lat": -28.156192,
            "lng": 153.6387812
          },
          "southwest": {
            "lat": -37.5050318,
            "lng": 140.9992123
          }
        }
      },
      "place_id": "ChIJDUte93TLDWsRLZ_EIhGvgBc",
      "types": [
        "administrative_area_level_1",
        "political"
      ]
    },
    {
      "address_components": [
        {
          "long_name": "Australia",
          "short_name": "AU",
          "types": [
            "country",
            "political"
          ]
        }
      ],
      "formatted_address": "Australia",
      "geometry": {
        "bounds": {
          "northeast": {
            "lat": -9.2198215,
            "lng": 159.255497
          },
          "southwest": {
            "lat": -54.7772185,
            "lng": 112.9214336
          }
        },
        "location": {
          "lat": -25.274398,
          "lng": 133.775136
        },
        "location_type": "APPROXIMATE",
        "viewport": {
          "northeast": {
            "lat": -9.315742,
            "lng": 162.823061
          },
          "southwest": {
            "lat": -46.526369,
            "lng": 103.672671
          }
        }
      },
      "place_id": "ChIJ38WHZwf9KysRUhNblaFnglM",
      "types": [
        "country",
        "political"
      ]
    },
    {
      "address_components": [
        {
          "long_name": "Town Hall House, Druitt St, Stand N",
          "short_name": "Town Hall House, Druitt St, Stand N",
          "types": [
            "point_of_interest",
            "establishment"
          ]
        },
        {
          "long_name": "Sydney",
          "short_name": "Sydney",
          "types": [
            "locality",
            "political"
          ]
        },
        {
          "long_name": "Sydney",
          "short_name": "Sydney",
          "types": [
            "colloquial_area",
            "locality",
            "political"
          ]
        },
        {
          "long_name": "New South Wales",
          "short_name": "NSW",
          "types": [
            "administrative_area_level_1",
            "political"
          ]
        },
        {
          "long_name": "Australia",
          "short_name": "AU",
          "types": [
            "country",
            "political"
          ]
        },
        {
          "long_name": "2000",
          "short_name": "2000",
          "types": [
            "postal_code"
          ]
        }
      ],
      "formatted_address": "Town Hall House, Druitt St, Stand N, Sydney NSW 2000, Australia",
      "geometry": {
        "location": {
          "lat": -33.87279880000001,
          "lng": 151.2045694
        },
        "location_type": "APPROXIMATE",
        "viewport": {
          "northeast": {
            "lat": -33.87144981970851,
            "lng": 151.2059183802915
          },
          "southwest": {
            "lat": -33.87414778029151,
            "lng": 151.2032204197085
          }
        }
      },
      "place_id": "ChIJDx94qj6uEmsRNu0a8rcwx7I",
      "types": [
        "bus_station",
        "transit_station",
        "point_of_interest",
        "establishment"
      ]
    }
  ],
  "status": "OK"
}


Thanks
New Post
11/10/2015 7:27 AM
 

Hi Jliptak

I had the same problem and never found a single action solution.

In the end my workaround was to store the json into an Action variable and then run 2 queries. The first at the root and the second details level.

Your example query would be the one at the details level. To get the status do the same but at the root "<ROOT></ROOT>"

Hopefully someone else will have a much better solution

Regards

Tony

Previous
 
Next
HomeHomeCoreCoreGeneralGeneralJSON Query - Extracting Data From Parent NodeJSON Query - Extracting Data From Parent Node


 

New York, NY • Baltimore, MD • Vienna, VA • St. Louis, MO • Seattle, WA • info@openwebstudio.com

Bookmark & Share Bookmark and Share