Unable to Deserialize JSON values obtained through API

I am trying to get few details like Names and corresponding vales from a Json string obtained through an API call.
Here is the screenshot of my API output:


We have title at the top and data goes more than 1000 rows. I need to get these transferred to a table and use them in a loop. And I am not sure how to do the same, I followed the instructions provided in this URL : https://forum.uipath.com/t/how-to-extract-the-exact-data-from-json-file/115745/3

Here are my variables


Here is screenshot of sequence:

This looks not like a typical JSON structure because in the 1st row there are the key-names and in the second row the values.
This is more like a CSV structure. I have no idea what those “\” mean. Maybe you can replace them.
Then i Would use “Generate Datatable” Activity. “\r\n” as line separator and “,” as column separator.
Or you save the string as “result.csv” and use “read csv” Activity.

@Frei_Janick - Thanks for the reply, Could you please let me know what is activity to do replace. I am sorry to ask this basic question but very new to UI Path as a matter of fact this is my first project.

Hi @DavidGopinath

It looks like you have a JSON file with another JSON string as one of its values.

Something like that should return you a valid JSON Object that you could then refer to:

JObject.Parse(System.Text.RegularExpressions.Regex.Unescape(yourJSON("message").ToString)).ToString

See this tiny sample for more details:
Main.xaml (4.7 KB)

@loginerror - Thanks for your response and guidance. Again I am so new this so I just followed your example, this time it worked without an error but the output is not correct. I know I am missing something here, kindly help me.

The output is correct :slight_smile: this is a difference between Json object and the json string. In the output panel, you see json object converted to string with the .ToString at the end.

What the entire code bit does is:

  • take the escaped json string
  • remove the escape signs \ (this is because if you want to use json object as a string within another json object, you need to escape the characters that could mess up the json structures, thus " becomes ")
  • after the string is escaped, I use JObject.Parse to convert my now unescaped json string to a json object
  • and then, just to display it, I add ToString so that it can be displayed using a write line activity.

But you could just as well do that to print just one of the values within:

JObject.Parse(System.Text.RegularExpressions.Regex.Unescape(yourJSON("message").ToString))("key").ToString

@loginerror - Thanks for the reply.
I did make the changes but the output is not as expected, I know I need to make changes in the key or value but I am not sure how to do that.

Here is the screenshot

So what I need is List of company name and it corresponding values also I need to get other columns as well. This is actually a replication of the project which I done successfully done in Excel VBA using Selenium package.

The process is to get all the credentials of the each client and then log in to each portal extract few info and loop the process until all portals are checked.

Well, it would seem it is just me completely missing what was said above by @Frei_Janick

Thus, after you do the unescape, you don’t parse the output of that into JSON, because you will have a comma-separated, CSV file output as string. Use a simple Assign activity to assign it to a string:

yourCSVString = System.Text.RegularExpressions.Regex.Unescape(yourJSON("message").ToString)

You can then save this string to a text file, so that you can paste it into the Generate Data Table activity ‘Sample Input field’ and play around with the options to extract the data:

I am sorry for being dumb, kindly bear with me as I am learning.
So I did what I comprehend, but still it did not work. I know for sure that my understanding seems to wrong. The text file has only this data : {“key”:“value”}
Here are the screenshot for reference


image

No problem. Well, you have to use the json object returned by your http request activity instead :slight_smile:

The http request activity has a response property. If you have there a variable with the response string, use that variable in the JObject.Parse part of the first assign activity (instead of my sample json code, it was just an example)

Actually my result from the HTTP request is stored to String, also you can see all the declared variables in this sequence.


I tried to change the variable of the HTTP result from string to Jobject but it throws an error.
image

Also, I did another method, again based on my understanding (which can be wrong)
I did change the first part with my JObject variable, but still it gave the same result in the text file as : {“key”:“value”}

I am clue less :cry:

Test to change the first Assign to:

Jsaonobj = JObject.Parse(strjason)

@ptrobot - Thanks for the response, I am getting the below error when I try the same.

Ok, that’s strange. Could you upload your workflow here so we can take a look?

Or if it contains sensitive data, maybe just save the strjason to a text file and upload it instead.

Actually, yes it contains sensitive data so the workflow cannot be shared.
But here is screenshot of the output copied from the output window.

OK. I have created a test workflow with jasonstr set to:

{"message": "\n\"Customer ID\",\"Company Name\",\"DBA Name\"" }

As you can see, the code works just fine. So I suspect that your jasonstr contains something else not in your uploaded screenshot that is preventing it from being parsed correctly by JObject.

image

See the attached file if you want to test for yourself. JsonTest.xaml (5.8 KB)

Okay, I did something stupid but it worked I am not sure whether this is the right approach.
Based on the comment provided by @Frei_Janick above I saved the output string to CSV file using Write Text File activity (below is screenshot) and it gave me the data I needed.
image

1 Like

@ptrobot
Actually I think you are correct the image I posted earlier was form the Output window, but when the same string is saved to Txt file I see some thing else.
Now this is learning for me. Thanks for your kind assistance!
Could you please let me what I did above is advisable or it might cause any issues in the later part of the automation.

When you open the “csvdata.csv” in Notepad, can you see any { "message": ...... } ? If not, then your strjason is just pure CSV data and what you have done is correct.

2 Likes

@ptrobot - Thanks for reply, yes I do not see this { "message": ...... }. I need to speak to my API developer to get the output as JSON string.
I really love this forum the responses are very quick and patience level of the assistance provided is very good.
Personal thanks to @loginerror @ptrobot @Frei_Janick

2 Likes