JSON to Datatable: Error converting Value

Question

I have this JSON, which is valid according to JSONbeautify.com :

{
    "date_check": "2023-04-05",
    "updated_datas": [
        {
            "FIELD_NAME": "BLN_ADS_LINKS_DETECTED",
            "NEW_VALUE": false,
            "OLD_VALUE": true
        },
        {
            "FIELD_NAME": "ADS_LINKS_DETECTED",
            "NEW_VALUE": null,
            "OLD_VALUE": "?"
        }
    ]
}

I'd like to display it in a datatable. I use this function:

 Public Function JsonStringToDataTable(ByVal json As String) As DataTable
        Dim jsonLinq = JObject.Parse(json)
        Dim srcArray = jsonLinq.Descendants().Where(Function(d) TypeOf d Is JArray).First()
        Dim trgArray = New JArray()

        For Each row As JObject In srcArray.Children(Of JObject)()
            Dim cleanRow = New JObject()

            For Each column As JProperty In row.Properties()

                If TypeOf column.Value Is JValue Then                   
                    cleanRow.Add(column.Name, column.Value)
                    End If
            Next

            trgArray.Add(cleanRow)
        Next

        Return JsonConvert.DeserializeObject(Of DataTable)(trgArray.ToString(), New JsonSerializerSettings With {.NullValueHandling = NullValueHandling.Ignore})
End Function

I got this exception: Error converting value "?" to type 'System.Boolean'. Path '[1].OLD_VALUE', line 10, position 20. I don't understand: why a boolean?

_"I don't understand: why a boolean?"_ - highly likely that because `[0].OLD_VALUE` is `false`. — Guru Stron, May 05 '23 at 11:05

score 1 · Accepted Answer · answered May 05 '23 at 11:26

Json.NET will use the first row to determine the datatype for column, so it will process OLD_VALUE as bool for this input json. Since you are already processing json manually you can try forcing all data being strings. Not fluent with VB but here is working snippet in C# to give you the idea (main idea is to use ToString on prop value when adding it to the new object - row.Add(prop.Name, prop.Value.ToString()), but there is some other stuff also):

var json = ...; // string from question
var jToken = JObject.Parse(json)["updated_datas"];
var jArray = new JArray();
foreach (var child in jToken.Children<JObject>())
{
    var row = new JObject();
    foreach (var prop in child.Children<JProperty>())
    {
        row.Add(prop.Name, prop.Value.ToString());
    }
    jArray.Add(row);
}

var table = jArray.ToObject<DataTable>();

Or you can create custom DataTableConverter which will infer correct type, like in this answer

score 0 · Answer 2 · answered May 05 '23 at 15:53

the easiest way to fix json would be to use a string replacement

 json = json.Replace("false","\"false\"").Replace("true","\"true\"")

also try this instead of your tonns of code

Dim dt As DataTable = JObject.Parse(json)("updated_datas").ToObject( Of DataTable)()

JSON to Datatable: Error converting Value

2 Answers2