Getting values from JSON

[sunep]

I have begun my journey into gamma (again) by trying to read values from a json file. I am getting a bit of success, but it is not quite like I want it.

My problem is that i need the value in the field “Vaerdi” when I have a certain “AnalysetypeId” see attached zip for patch and a sample json file. I do get that, but besides the correct slices, I also get empty slices.

I also feel that I am not doing this in an optimal way.

So any advice on how I can just get the slices that have the right content?

HowDoIReadValuesFromJSON.zip.zip (5.9 KB)

[tonfilm]

as for empty slices, you can use the Keep pin to filter those out. see this video towards the end:

HowTo Use a ForEach Loop

[sebescudie]

You could use XPathGetValues with this query : /Svaroversigt/Svaroversigt/Laboratorieresultater[AnalysetypeId="iupac_NPU03568"]/Vaerdi. You would retrieve all the Vaerdi where AnalysetypeId is iupac_NPU03568.

Pro tip : there is a Visual Studio Code plugin called XML Tools that allows you to highlight some portion of an XML document and automagically give you the correct XPath query to retrieve it.

[ravazquez]

just to add to @sebescudie’s Pro Tip, you can do the same in chrome or Firefox by using the dev tools (Ctrl+Shift+I in FF), then navigating to the specific element and selecting, then: right click/Copy/XPath:

[sunep]

@tonfilm

@tonfilm

as for empty slices, you can use the Keep pin to filter those out

I am actually using keep. I get a spread of the same size as the input but with empty slices.

@sebescudie and @ravazquez am having a JSON file, not XML.

it seems like the xpath query doesn’t work here. I might be doing something wrong

[sunep]

@ravazquez you made something for me i think two years ago. I am now trying to recreate that in VL.

[bjoern]

The following expression works:
//Laboratorieresultater/AnalysetypeId[contains(text(),'iupac_NPU02319')]/../Vaerdi

JSON-reader.vl (13.5 KB)

[ravazquez]

Hey @sunep, I do remember cooking up some regex or similar for you in the past, glad to hear you are porting it to gamma : ) .

Have you looked at the “Read JSON” help patch? It shows how you get an XDocument from a JSON file, you can then use XPath to navigate and get what you need as if it was XML:

You don’t have to do it like that, but the nodes are there and it’s relatively straightforward.

Hope that helps.

[bjoern]

Concerning the Keep: Use the condition from the inner foreach in the outer one too.

[sunep]

Thanks guys.
I can see that it makes sense. I need to use more time in Gamma to become more fluent.

[bjoern]

One more thing TryParse fails (at least on my system) when a comma is used as decimal separator.
So when you want to convert the strings to float you maybe have too replace it with a dot beforehand.

[sebescudie]

@sunep

@sebescudie and @ravazquez am having a JSON file, not XML.

it seems like the xpath query doesn’t work here. I might be doing something wrong

how weird, got it working here :

reg. XML, as you can see on my screenshot, using ToString on an XDocument (which is the output type of JSONReader) gives you an XML string.

@ravazquez

you can do the same in chrome or Firefox by using the dev tools

ah cool, nice one!

[sunep]

@bjoern Thanks a lot. I have now also fitered out elements that are failed responses. thanks a lot. Now I have something that is almost elegant and very usable.