Dynamic JSON Parsing

Help Studio
, ,
1

I’m currently dealing with a dynamic JSON response and could use some expert advice. :brain::bulb: The challenge I’m facing is in extracting and organizing data—specifically low, mean, high, and their corresponding dates—into a DataTable.

Here’s the catch: the counts for low, mean, high, and dates are not fixed; they vary in each response.

Any tips, tricks, or insights on how to dynamically handle these varying counts and structure the data into a DataTable would be greatly appreciated!

I have attached the json response

Expected Output header should be like

Date low mean High
12.12.23 3 4 6
13.12.23 2 4 8
json.txt (2.3 KB)

2

we can quick convert the diffwrent sets e.g. the low:
grafik

Can dynamized find the properties / keys
grafik

And can also modify / customize the retrieval to get all in one datatable with low,high… markers if this is needed

3

I tried the second one you mention I get this error . Can you please help

The following errors were encountered while processing the workflow tree:
‘VisualBasicValue’: Compiler error(s) encountered processing expression
“DeserialisedJson(“market_rates_by_day”).Value(of JObject).properties().Select(Function (x) x.Name).ToArray”.
Option Strict On disallows late binding.
Type ‘JObject’ is not defined.

4

ensure:
grafik

6

Thanks but after import i get this error

The following errors were encountered while processing the workflow tree:
‘VisualBasicValue’: Compiler error(s) encountered processing expression “DeserialisedJson(“market_rates_by_day”).Value(Of JObject).Properties().Select(Function(x) x.Name).ToArray”.
Option Strict On disallows late binding.

7

Activity:
grafik

has output:
grafik

Variables:
grafik

ensure that always " is in use and not the inverted ones

For RnD / prototyping we recommend:
Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

8

Thanks .Once after changing the datatyple and imports i was able to bring the same output as yours . one more question can we retrive all the information into datatble with this linq DeserialisedJson(“market_rates_by_day”).Value(Of JObject).Properties().Select(Function(x) x.Name).ToArray …

9

as mentioned,

we would recommend to catch all within an intermediate format and the post process e.g. pivoting

10

Find some little starter help

grafik
grafik991×365 34.2 KB

Build DataTable - dtData

dtData =

(From x In myJObject("market_rates_by_day").Value(Of JObject).Properties().Select(Function (p) p.Name)
From jo In myJObject("market_rates_by_day")(x).Values(Of JObject) 
Let ra = jo.Properties.Select(Function (p2) p2.Value.toString).Append(x).Cast(Of Object).ToArray
Select r = dtData.Rows.Add(ra)).CopyToDataTable

Result:
grafik