AnneC
Scholar

Using Query JSON to Get GSA Per Diem

Jump to solution

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.

Labels: (1)
0 Kudos
Reply
2 Replies
katie_chu
Nintex Workflow Cloud Expert
Nintex Workflow Cloud Expert

Re: Using Query JSON to Get GSA Per Diem

Jump to solution

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

Reply
AnneC
Scholar

Re: Using Query JSON to Get GSA Per Diem

Jump to solution
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!
0 Kudos
Reply