Parsing JSON/XML from web responses

A post was merged into an existing topic: Example: Constructing JSON

Hi @dgudkov -

I’m looking at the JSON file that would be a typical response back in a flat example:
Something like this:

Sample from Magento GET Inventory by SKU
{“item_id”:953294,“product_id”:690692,“stock_id”:1,“qty”:99,“is_in_stock”:true,“is_qty_decimal”:false,“show_default_notification_message”:false,“use_config_min_qty”:true,“min_qty”:10,“use_config_min_sale_qty”:1,“min_sale_qty”:1,“use_config_max_sale_qty”:true,“max_sale_qty”:10000,“use_config_backorders”:true,“backorders”:0,“use_config_notify_stock_qty”:true,“notify_stock_qty”:11,“use_config_qty_increments”:true,“qty_increments”:0,“use_config_enable_qty_inc”:true,“enable_qty_increments”:false,“use_config_manage_stock”:true,“manage_stock”:true,“low_stock_date”:null,“is_decimal_divided”:false,“stock_status_changed_auto”:0}

So in order to use this function we would have to parse out each of the columns and then run the function on it, correct?

In this case it's a flat JSON, so it can be trivially parsed using EasyMorph transformations. See below the JSON parsed.

parse-flat-json.morph (3.4 KB)

Parsing a nested hierarchical JSON would be trickier (until we introduce the "Parse JSON" action) and would probably require a combination of actions and functions.

1 Like

A post was split to a new topic: Constructing JSON

A post was merged into an existing topic: Example: Constructing JSON

So far it’s really good.
I can extract metadata and data from IBM Planning Analytics/TM1
very useful.

1 Like

Looking for guidance on the nested JSON (until introduction of the Parse action) - Is this something already available to work with?

To some extent. There are 3 options available how to extract data from nested JSONs until introduction of the Parse action (which is a big-big priority for us):

  1. Usual transformations and text functions that existed before 4.0.
  2. Function jsonvalue() described above - one function would be necessary per each nested value.
  3. Parsing JSON with PowerShell.
1 Like

***** moved from #lounge to #uncategorized

Tip to find the jsonpath quickly:

http://jsonpathfinder.com/

Hi Dmitry,

What’s the JSON path expression to filter only the objectId for de 2 items with objectType = Gebouweenheid in this array ?
How can I apply a filter in some way I always get errors?

I have tried the following but it throws an error. Maybe somethting with the quotes ?

jsonvalue(
[Response-Body],
‘adresMatches[0].adresseerbareObjecten[?(@.objectType)==“Gebouweenheid”].objectId’
)

Hi Nikolaas,

wait for the “Parse JSON” action in v4.1. While JSONPath expressions can be quite powerful, we will be recommending using “Parse JSON” and apply regular EasyMorph actions.

Hi

I understand that but I would like to use it now…

When I do the following it works when I test it in the online tool (without the surrounding quotes) https://jsonpath.curiousconcept.com/
In easyMorph I get an error. What could be the reason?

‘adresMatches[0].adresseerbareObjecten[?(@.objectType==“Gebouweenheid”)]’

jsonvalue(
[json], ‘adresMatches[0].adresseerbareObjecten[?(@.objectType==“Gebouweenheid”)]’
)

Hi Nikolaas,

Strings in filters should be wrapped in single quotes, not double quotes.

Try to replace “Gebouweenheid” with 'Gebouweenheid' and wrap the whole JSONPath query to double quotes instead of single quotes.

Works great ! I thought it was something with the quotes but I did not try your option;

When I use iterate webrequest on a table, the output of the request overwrites my table. How can I recover the original columns? The output of the webrequest should be appended as columns to my originial table because the original columns are used as parameters in the web request.

All iterate actions (e.g. “Iterate” or “Iterate table” replace input datasets. If you want to have it back merged with iteration results you do iterations in a derived table, then merge the original data using the “Merge” action.

To help with that, the “Iterate web request” action allows adding request path, body and headers (see the option below).

image

Hi Dmitry,

The problem that I have is the following:

I send address data to an API using iterate webrequest. That returns an ID of the address and other properties such as GPS coordinates,…

The matching of the API is “intelligent” in a way because when I send a street name like “Andre Schottplein” the API returns in the reponse André Schotteplein. So it returns the name of the street with the correct spelling i.e. with “é” in “André”. Because of that fact, I can no longer match my orinigal dataset wich has the value without the special character to the output of the web request.

When I try to get it from the request path, that special character is also transformed to “%20” in the url. Any suggestions how I could get the original values back so that I can add the data returned from the API to my original dataset?

https://basisregisters.vlaanderen.be/api/v1/adresmatch?adresMatchRequest.postcode=9620&adresMatchRequest.straatnaam=Andre**%20**Schotteplein

Maybe I should use the decode() function here ?

First, you can use the decode() or uridecode() functions to get rid of %20.

Second, if you use the option I mentioned earlier, you can link requests to responses. You may need to parse requests to extract keys but it will give you the relationship between what you send and what you receive. In this case you can do matching not by response data (which is modified in your case), but by request data (which unmodified in your case).

Hi

Thanks. Maybe I will upload an example :slight_smile: with my solution so that you can verify if that’s the solution you proposed.

Thanks