NewtonSoft Deserialize nested JSON Blue Prism

[Digital_WorkerB]

I'm using Blue Prism to call the Microsoft Computer Vision API to recognize text from a PDF.

After getting the JSON response, Blue Prism transforms it into a Collection (DataTable) using either the JSON Utility from Blue Prism, or the Action Provided on the Microsoft Computer Vision skill: Get Read Operation Response.

Problem: Blue Prism is unable parse the JSON text into a Collection in certain cases due to a data type error.

Can someone sugest a code to convert this type of JSON structure into a DataTable which Blue Prism can read?

I have attached pastebin links for the 2 JSON (Body is limited to 30000 characters, can't copy them here).

READABLE in Blue Prism (contains special characters !'#$%&/()''):

https://pastebin.com/QMPCiQty

NOT READABLE in Blue Prism:

https://pastebin.com/V3HVssQz

Blue Prism gives the following errors:

Using 'Get Read Operation Response' action:

Internal : Unexpected error Data Type mismatch in array: Element '3' has type instead of expected type 'System.Double'

Using 'JSON Utility from Blue Prism':

Internal : Could not execute code stage because exception thrown by code stage: Data Type mismatch in array

I tried to parse the nested JSON into a datatable by creating the public classes using the tool provided at json2csharp.com, and then point to them and write to the data table. I also tried by not creating the classes, just parse the JSON directly to the data table by pointing to the property recognitionResults.

Without classes:

DataSet ds = JObject.Parse(json_txt)['recognitionResults'].ToObject();

With classes:

DataTable dt = (DataTable)JsonConvert.DeserializeObject(json_txt, (typeof(DataTable)));

Classes:

public class Word
{
    public List<double> boundingBox { get; set; }
    public string text { get; set; }
    public string confidence { get; set; }
}

public class Line
{
    public List<double> boundingBox { get; set; }
    public string text { get; set; }
    public List<Word> words { get; set; }
}

public class RecognitionResult
{
    public int page { get; set; }
    public double clockwiseOrientation { get; set; }
    public double width { get; set; }
    public double height { get; set; }
    public string unit { get; set; }
    public List<Line> lines { get; set; }
}

public class RootObject
{
    public string status { get; set; }
    public List<RecognitionResult> recognitionResults { get; set; }
}

Here is how the nested collection should look like in Blue Prism: Blue Prism Nested Response Collection

--------------------------------------------------
Disclaimer: This content was auto-posted from Stackoverflow. The original Stackoverflow question is here Stackoverflow Post, posted by David.

[BimalSebastian]

Hi DW,

Do you want to try the following code. Iv tried this for Microsoft NLP responses. I have included a sample JSON that works with this code.
You might have to make suitable adjustments.

class ParseLuisResponse
{
//string sampleJson = "{ \"query\": \"<URL>", \"topScoringIntent\": { \"intent\": \"None\", \"score\": 0.9240461 }, \"intents\": [ { \"intent\": \"None\", \"score\": 0.9240461 }, { \"intent\": \"GetInfrastructureInfo\", \"score\": 1.37703191E-05 } ], \"entities\": [], \"sentimentAnalysis\": { \"label\": \"positive\", \"score\": 0.7532452 }}";

public DataTable convertToDataTable(string j)
{
DataTable dt = new DataTable();
JToken JO = JToken.Parse(j);
switch (JO.Type)
{
case JTokenType.Array:
dt = convertJArrayToTable(j);
break;
case JTokenType.Object:
dt = convertJObjectToTable(j);
break;
}
return dt;

}
public DataTable convertJArrayToTable(string j)
{
var response = JsonConvert.DeserializeObject<List<Dictionary<string, Object>>>(j);

DataTable dt = new DataTable();
if (response != null)
{
dt.Columns.AddRange(response.FirstOrDefault().Select(x =>
new DataColumn
{
ColumnName = x.Key
}).ToArray());

foreach (Dictionary<string, Object> dict in response)
{
DataRow dr = dt.NewRow();
foreach (var kv in dict)
{
dr[kv.Key] = kv.Value.ToString();

}
dt.Rows.Add(dr);
}
}
return dt;

}
public DataTable convertJObjectToTable(string j)
{
var response = JsonConvert.DeserializeObject<Dictionary<string, Object>>(j);

DataTable dt = new DataTable();
if (response != null)
{
dt.Columns.AddRange(response.Select(x =>
new DataColumn
{
ColumnName = x.Key
}).ToArray());

DataRow dr = dt.NewRow();
foreach (var kv in response)
{
dr[kv.Key] = kv.Value.ToString();

}
dt.Rows.Add(dr);
}
return dt;
}
}


------------------------------
Bimal Sebastian
Consultant
Blueprism
Asia/Kolkata
------------------------------