Extract Data from JSON into separated columns

Hello Community,

the “Get Request” Node delivers the following output:

{
  "rates" : {
    "2021-02-05" : {
      "CAD" : 1.5344,
      "HKD" : 9.29,
      "ISK" : 154.9,


“KRW” : 1345.45,
“MYR” : 4.8777
},
“2021-02-04” : {
“CAD” : 1.5358,
“HKD” : 9.3003,
“ISK” : 156.1,

“KRW” : 1341.26,
“MYR” : 4.8686
}
},
“start_at” : “2021-02-04”,
“base” : “EUR”,
“end_at” : “2021-02-05”
}

In the next step, I know I have to use the JSON Path Node followed by the Ungroup to get the data in seperated columns.
date iso-code rate
2021-02-04 CAD 1.5358

2021-02-05 CAD 1.5344

“$.[*]” - wasnt the right JSON-Path.

Maybe someone can help.

Thanks and BR,
Sven

Hi Sven,
this is indeed tricky, and I fear I have no solution to it although I’m using the JSON Path Node quite frequently. I don’t think it is a correctly (or at least usefully) formatted JSON, e.g. a list (array) is usually defined with square brackets [ ]. Also, the date and iso-code is not a name in the JSON that can be targeted with the JSON Path node.

Is there a possibility to retrieve the data with the Get Request Node in a different format (e.g. with format=XML)? If yes, I think this would be the easiest solution.
If not, it might be possible to reformat the JSON (e.g. with some Regex replacements). The following JSON would already allow to retrieve the date with the list query $[‘rates’][*][‘date’], but not yet the iso-codes in the format you wanted.

  {
  "rates" : [
{"date": "2021-02-05", 
"CAD" : 1.5344,
"HKD" : 9.29,
"ISK" : 154.9,
"KRW" : 1345.45,
"MYR" : 4.8777
},
{"date": "2021-02-04",
"CAD" : 1.5358,
"HKD" : 9.3003,
"ISK" : 156.1,
"KRW" : 1341.26,
"MYR" : 4.8686
}
],
"start_at" : "2021-02-04",
"base" : "EUR",
"end_at" : "2021-02-05"
}

Best regards,
Daniela

3 Likes

If you provide the url the forum can “wrangle” with you together
bR

hi @daniela_digles and hi @Daniel_Weikert,

@Daniel_Weikert: source was: exchangeratesapi.io
@daniela_digles: I have already thought about that the json-file was not 100% correct, but I was able to get a result, but not exactly how I was hopeing for, due to the (nor correct) formatting.

I found a second source in xml-format and currently working with this one - it looks like, working with xml is a “bit” slower.

Thanks and BR,
Sven

Hi @sven-abx,

I found a workaround with two JSON to Table nodes, a Column List Loop und Unpivoting. So probably not really faster than the slower XML, but maybe it can still be optimized.
exchangeratesapi.knwf (20.9 KB)

Best regards,
Daniela

4 Likes

hi @daniela_digles,

thank you for your workflow and help.

br,
sven

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.