Solved

Using Query JSON to Get GSA Per Diem


Badge +6

I am trying to use Query JSON to pull GSA current rates for a travel reimbursement form and it's giving me fits!  Since this is a GSA rates query, I'm hoping it's a pretty common usage and some of you are also using it for travel reimbursement forms/workflows of your own.

 

I'm using the Query JSON action in NWC. To test it, I followed the Nintex help to put my JSON into a string convertor, then put it into the JSON Path tester. I wrote a workflow that calls the web service, then emails me the text of the response to get the full response text for testing.

 

Yesterday when I did this, the string converter worked, today I can't get it to, I get no error, nothing, just an empty results box. When t worked yesterday, it looked like all it did was replace " with ""

 

When I put the response content into the tester, I get something like this every time:

After parsing a value an unexpected character was encountered: r. Path 'JSON', line 2, position 13."

I tried using search and replace in notepad to get the backslashes added, but I get the same result.

 

My API calls to GSA are working, I can put the results in firefox or code beautify and see the tree view, and validators say it passes, so I know that part is good.

 

The one time I got it to work, I used this (leaving off my API key):
https://api.gsa.gov/travel/perdiem/v2/rates/zip/64110/year/2022?api_key=

Result was this:

{"request":null,"errors":null,"rates":[{"oconusInfo":null,"rate":[{"months":{"month":[{"value":123,"number":1,"short":"Jan","long":"January"},{"value":123,"number":2,"short":"Feb","long":"February"},{"value":123,"number":3,"short":"Mar","long":"March"},{"value":123,"number":4,"short":"Apr","long":"April"},{"value":123,"number":5,"short":"May","long":"May"},{"value":123,"number":6,"short":"Jun","long":"June"},{"value":123,"number":7,"short":"Jul","long":"July"},{"value":123,"number":8,"short":"Aug","long":"August"},{"value":123,"number":9,"short":"Sep","long":"September"},{"value":123,"number":10,"short":"Oct","long":"October"},{"value":123,"number":11,"short":"Nov","long":"November"},{"value":123,"number":12,"short":"Dec","long":"December"}]},"meals":64,"zip":"64110","county":"Jackson / Clay / Cass / Platte","city":"Kansas City","standardRate":"false"}],"state":"MO","year":2022,"isOconus":"false"}],"version":null}

Then, to get the rate for May, I used this expression:

$.rate[0].months.month[4].value

 

Result was correct:

200 OK

"FirstMatch": 123, "AllMatches": [123]}

 

  1. So what am I missing today that the blasted converter won't work?
  2. If the text needs to be converted to test it, do I need a regular expression to convert it in NWC between the web call and the JSON query?
  3. If so, WHY??? And what should the regex be?

TIA.

icon

Best answer by katie_chu 18 May 2022, 19:53

View original

2 replies

Userlevel 2
Badge +4

Hi Anne,


 


Whenever I have JSON Path issues, I always use this site to test with:


https://jsonpath.com/


 


It's been pretty consistent in outputting what NWC would output. So I used it to test with, and the expression I got to work was:


$.rates..rate..months.month[4].value

I tested it in my own tenant and it gave me the expected value of 123.


 


Let me know if it works for you!


Katie

Badge +6
OMG, thank you!!! This evaluator is so much better than the one Nintex's help linked to - I got it to work in seconds, it was the 2 dots before 'rate' that was the issue, I only had one. Seeing your reply made it jump right out - it wasn't so obvious with my using bracket notation. Thank you again!

Reply