Solved

parsing / query

  • 1 December 2023
  • 4 replies
  • 60 views

Userlevel 1
Badge +4

I am using Xtensions to call an internal web API that returns data in JSON format, and I now want to parse through that JSON to get the value I want.  The workflow action that calls the API will store the result in a collection variable.  I have to use a “Get item from collection” action to make it readable by the “Query JSON” action.  I do this all time for the easy stuff.  Now I have an issue because now the value I want is within an array of values in the JSON.  If I did a Query JSON action, I would have to use something like [0] or [1] in the JSON expression to obtain the first entry, or the second entry, etc.  I want to loop through all the entries.

My idea is that I can “Query JSON” to obtain the portion of JSON that contains that array, then I can do a loop action or For Each action.  However, when I use “Count items in collection” it only returns a count of 1.  This is incorrect. In my test data, there should be 5 items.  Is there another method of counting these items?

Another thing it keeps doing is adding escape characters, like the \ slash.  I see them when I do a “Log to instance details.”  I think these are messing up my Query JSON commands.  Is anything else dealing with this and have ideas?

 

icon

Best answer by PabloL 7 December 2023, 18:09

View original

4 replies

Userlevel 1
Badge +4

Update on this -- I made some progress.  I used a “Start a loop” action and started an index at 0, and I called a “Query JSON” action for each index [0] through [4] or [5] or whatever and when that query finally returns no data, then I know I can exit the loop.  A little bit messy but I am now getting somewhere.

Badge +8

Hi @PabloL , have you added

"x-ntx-render-version": 2

to your Swagger definition file?

This will ensure that an Array of complex Objects are returned, rather than raw JSON. 

Userlevel 1
Badge +4

Yes, I have that “x-ntx-render-version” line in the Swagger definition.

I’ll reply later with an update on my working version of this issue.

Userlevel 1
Badge +4

Here is what I developed that works to parse the results of the Web API call and obtain the values I am looking for.

 

First, the overall workflow looks like this (see 1st image below) -

It calls the Web API to get the extended record, creating a Nintex collection variable.

I use “Get Item from collection” to strip away the outer brackets. This output can be read by a Query JSON action later.

I initialize a string that begins the syntax of an HTML table that will contain the values from the array.

I start a loop that will end only when I know the Query JSON does not return any more results.

Inside the loop, I run Query JSON (see 2nd image below) starting at index = 0 (and index will increment by 1 each time through the loop until no more results).

If the query finds results, then do another Query JSON to look for specific values within that result, and if the value is of a certain type, then add the amount value to the HTML table, etc.

 

Here is the 1st Query JSON:

 

Here is the 2nd Query JSON:

 

If you ask “Why don’t you only do 1 call for the specific information?, the 2nd Query JSON”  Because I need to know when I can stop the loop, and the simplest way I can determine that is when the 1st call returns no results.  Sometimes the 2nd call will return no results at index = 2, but I still want the results at index = 3.

 

Yes, it would have been easier if I could have done a “For Each” loop or if I had been able to use “Count items in Collection” but those did not work with the data that I had.  This method works.

Reply