Deserializing JSON string to JSON object

Help Studio
, ,
1

Hi, I’m trying to deserialize a string that contains JSON. It contains multiple entries.

Some context: I’m taking text from the body of an email (there’s plaintext JSON at the end of it) for survey results and outputting it into excel. But I’ve been unable to separate the questions and answers because of how it’s formatted.

For example:
{“question”:“Reference Number”,“answer”:“847564592”},{“question”:“Timestamp”,“answer”:“Mon, 16 Mar 2020 02:10:46 PM”},{“question”:“Name”,“answer”:“Alex Bob Johanson”},{“question”:“Do you enjoy corn soup?”,“answer”:“Yes”},{“question”:“Where are you from?”,“answer”:“Singapore”}

I’ve used Replace(“”“”,“'”) to change the " to '. The problem is that I can’t split the string with the commas because the array will output something like:
{‘question’:‘Reference Number’
it’s incomplete.

Is there any way to work around this? Any help is appreciated, thank you!

2 Likes
2

I’ve managed to work around separating the values and putting them into an array (where 1 array element is “{‘question’:‘Name’,‘answer’:‘Alex’}”) and I tried putting it into the JSON deserializer and I’m getting an error:
Deserialize JSON: Unable to cast object of type ‘Newtonsoft.Json.Linq.JValue’ to type ‘Newtonsoft.Json.Linq.JObject’.

image
image1057×474 18.4 KB

3

@kartistenn How do you want the Output to Appear?

4

The final idea is the questions and answers sorted in an excel sheet. But for now I’m trying to see if I can get my message box to show a question or answer when I call for it…

5

@kartistenn Assuming you have the Json in a Text File,

  1. Read the Josn Text File using Read Text File Activity, Store the output in a Variable say JsonString.
  2. Then Use Deserialize Json Activity, with Type Argument as Datatable.
  3. Supply a Datatable to the Output parameter of Json Deserialize.

This Shoul give you the questions and answers in Datatable Format which you can then write to an Excel File

2 Likes
6

My JSON is in a string actually. I’ll try your steps skipping the text file parts. If not, I guess I’ll have to try to figure out a workaround. Thanks! Will update in an hour or two

1 Like
7

I’ll summarise couple of problems I’ve encountered…
I put a JSON into a string (“{‘question’:‘Name’,‘answer’:‘Alex’}”)
Put it into the JSON deserializer and set the output to out_Json (type: JObject, Type Argument is also JObject) But I get the Deserialize JSON: Unable to cast object of type ‘Newtonsoft.Json.Linq.JValue’ to type ‘Newtonsoft.Json.Linq.JObject’. error.

I’ve tried making the JSON deserializer output variable a datatable and changed the type. But I encountered this error instead

image
image1231×249 14.7 KB

8

@kartistenn What is the Type argument used in Deserialize Json Activity?

9

@kartistenn Is it possible to give your Json Data?

10

Here is the plain JSON file.
In my program I’ve written some assigns to replace the " with ’ [{"question":"Reference Number","answer":"9845938475348573495"},{"question":"Tim - Pastebin.com

The Type argument used in Deserialize Json Activity was datatable
image
out_Json is also datatable type

Thanks!

11

@kartistenn Check this Workflow :
JsonToDT.zip (7.8 KB)

Check the Text File, I had Copy pasted the Data from the link you have provided. It worked for me. Can you Check if that is the way you needed it. And if you may, Spot the difference in your workflow and mine :sweat_smile:

3 Likes
12

Looks like my problem was editing how the JSON string worked, since I changed the " to ’ and other things with this line
JSONstring.Replace(“[”,“”).Replace(“]”,“”).Replace(“”“”,“'”).Replace(“{”,“”“{”).Replace(“}”,“}”“”).Replace(“”“,”, “”“”+Environment.NewLine)

It works now, thank you!

3 Likes
13

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.