Convert json to datatable vb

Question

Good morning, today i was trying to convert my json to datatable.

This is what i'm trying to do

    Dim webclient_server7 As New System.Net.WebClient
    Dim json_result As String = webclient_server7.DownloadString("http://myhost/api/mycontroller/GetQuery")
    Dim json_jsonstring = Newtonsoft.Json.JsonConvert.SerializeObject(json_result)

    Try
        Dim table As DataTable = JsonConvert.DeserializeObject(Of DataTable)(json_jsonstring)
    Catch ex As Exception
        MsgBox("An exception occured: " & ex.Message)
    End Try

And i get an exception saying:

An exception occured: Unexpected JSON token when reading DataTable. Expected StartArray, got String. Path '', line 1, position 9919.

i validated my json on json lint and it says my json is valid.
Is there anyone who can help me fixing this?

here's a copy of my raw json

"{\r\n \"Table\": [\r\n {\r\n \"IdOwner\": \"Davide\",\r\n \"tag_id\": 1,\r\n \"tag_type\": \"3\",\r\n \"tag_group\": \"Group_2\",\r\n \"tag_name\": \"Alfa\",\r\n \"tag_sequence\": 123458,\r\n \"tag_description\": \"Description_2\",\r\n \"tag_short_descritpion\": \"Desc_2\",\r\n \"tag_um\": \"kg\",\r\n \"tag_active\": true,\r\n \"tag_collecting\": false,\r\n \"tag_data_edit\": true,\r\n \"tag_source_name\": \"Alfaservice\",\r\n \"tag_source_index\": \"Undefined_index\",\r\n \"tag_source_tagtype\": \"Source_tag_type\",\r\n \"tag_source_lenght\": 50,\r\n \"tag_collect_frequency\": 200,\r\n \"tag_collect_unit\": \"ms\",\r\n \"tag_low_limit\": 100.0,\r\n \"tag_high_limit\": 370.0,\r\n \"tag_control_limit_active\": true,\r\n \"tag_calc\": \"useless_field\",\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": null\r\n },\r\n {\r\n \"IdOwner\": \"Giuseppe\",\r\n \"tag_id\": 3,\r\n \"tag_type\": \"Type_1\",\r\n \"tag_group\": \"Group_2\",\r\n \"tag_name\": \"Bemad\",\r\n \"tag_sequence\": 123456,\r\n \"tag_description\": \"Description_5\",\r\n \"tag_short_descritpion\": \"Desc_5\",\r\n \"tag_um\": \"Kg\",\r\n \"tag_active\": true,\r\n \"tag_collecting\": false,\r\n \"tag_data_edit\": true,\r\n \"tag_source_name\": \"Alfaservice\",\r\n \"tag_source_index\": \"Undefined_index\",\r\n \"tag_source_tagtype\": \"Source_tag_type\",\r\n \"tag_source_lenght\": 50,\r\n \"tag_collect_frequency\": 200,\r\n \"tag_collect_unit\": \"ms\",\r\n \"tag_low_limit\": 250.0,\r\n \"tag_high_limit\": 660.0,\r\n \"tag_control_limit_active\": true,\r\n \"tag_calc\": \"useless_field\",\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": null\r\n },\r\n {\r\n \"IdOwner\": \"Madalin\",\r\n \"tag_id\": 2,\r\n \"tag_type\": \"Type_2\",\r\n \"tag_group\": \"Group_1\",\r\n \"tag_name\": \"Bemad\",\r\n \"tag_sequence\": 123456,\r\n \"tag_description\": \"Description_1\",\r\n \"tag_short_descritpion\": \"Desc_1\",\r\n \"tag_um\": \"Kg\",\r\n \"tag_active\": true,\r\n \"tag_collecting\": false,\r\n \"tag_data_edit\": true,\r\n \"tag_source_name\": \"Alfaservice\",\r\n \"tag_source_index\": \"Undefined_index\",\r\n \"tag_source_tagtype\": \"Source_tag_type\",\r\n \"tag_source_lenght\": 50,\r\n \"tag_collect_frequency\": 200,\r\n \"tag_collect_unit\": \"ms\",\r\n \"tag_low_limit\": 150.0,\r\n \"tag_high_limit\": 350.0,\r\n \"tag_control_limit_active\": true,\r\n \"tag_calc\": \"useless_field\",\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": null\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 6,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": null\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 7,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": \"Tag Name=REW_SAX_BOWED_ActualVelocity,Address=REW_SAX_BOWED.ACTUALVELOCITY,Data Type=Float,Respect Data Type=0,Client Access=RO,Scan Rate=100,Scaling=,Raw Low=,Raw High=,Scaled Low=,Scaled High=,Scaled Data Type=,Clamp Low=,Clamp High=,Eng Units=,Description=,Negate Value=,\"\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 8,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": \"Tag Name=REW_SAX_BOWED_ActualVelocity,Address=REW_SAX_BOWED.ACTUALVELOCITY,Data Type=Float,Respect Data Type=0,Client Access=RO,Scan Rate=100,Scaling=,Raw Low=,Raw High=,Scaled Low=,Scaled High=,Scaled Data Type=,Clamp Low=,Clamp High=,Eng Units=,Description=,Negate Value=,\"\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 9,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": \"Tag Name=REW_SAX_BOWED_ActualVelocity,Address=REW_SAX_BOWED.ACTUALVELOCITY,Data Type=Float,Respect Data Type=0,Client Access=RO,Scan Rate=100,Scaling=,Raw Low=,Raw High=,Scaled Low=,Scaled High=,Scaled Data Type=,Clamp Low=,Clamp High=,Eng Units=,Description=,Negate Value=,\"\r\n },\r\n {\r\n \"IdOwner\": \"test\",\r\n \"tag_id\": 10,\r\n \"tag_type\": null,\r\n \"tag_group\": null,\r\n \"tag_name\": \"REW_SAX_BOWED_ActualVelocity\",\r\n \"tag_sequence\": 0,\r\n \"tag_description\": \"1\",\r\n \"tag_short_descritpion\": \"1\",\r\n \"tag_um\": null,\r\n \"tag_active\": true,\r\n \"tag_collecting\": true,\r\n \"tag_data_edit\": false,\r\n \"tag_source_name\": \"REW_SAX_BOWED.ACTUALVELOCITY\",\r\n \"tag_source_index\": \"0\",\r\n \"tag_source_tagtype\": \"Float\",\r\n \"tag_source_lenght\": 0,\r\n \"tag_collect_frequency\": 100,\r\n \"tag_collect_unit\": \"1\",\r\n \"tag_low_limit\": 1.0,\r\n \"tag_high_limit\": 1.0,\r\n \"tag_control_limit_active\": false,\r\n \"tag_calc\": null,\r\n \"tag_level\": 0,\r\n \"tag_origine_dati\": \"Tag Name=REW_SAX_BOWED_ActualVelocity,Address=REW_SAX_BOWED.ACTUALVELOCITY,Data Type=Float,Respect Data Type=0,Client Access=RO,Scan Rate=100,Scaling=,Raw Low=,Raw High=,Scaled Low=,Scaled High=,Scaled Data Type=,Clamp Low=,Clamp High=,Eng Units=,Description=,Negate Value=,\"\r\n }\r\n ]\r\n}"An exception occured: Unexpected JSON token when reading DataTable. Expected StartArray, got String. Path '', line 1, position 9919.

Answer 1

First you need to remove this line:

Dim json_jsonstring = Newtonsoft.Json.JsonConvert.SerializeObject(json_result)

The downloaded string is already JSON, if you serialize it again you will turn it into an escaped JSON string literal, whereupon deserializing it returns the string - and not a DataTable as you want. This explains the error Unexpected JSON token when reading DataTable. Expected StartArray, got String. Path '', line 1, position 9919: Json.NET parsed the entire json_jsonstring string as a single, escaped string literal.

Second, your JSON contains an outer root object like so:

{
  "Table": [
    {
      "IdOwner": "Davide", 
      // Additional properties
    },
    // Additional rows.
  ]
}

Thus you need to introduce a root object with the necessary Table property to deserialize the DataTable into. You can do it with the following generic root:

Class RootObject(Of T)
    Public Property Table As T
End Class

And then deserialize as follows:

Dim table = JsonConvert.DeserializeObject(Of RootObject(Of DataTable))(json_result).Table

Sample fiddle.

Or, if you don't care to create a root type, deserialize to a temporary Dictionary(Of string, DataTable):

Dim table = JsonConvert.DeserializeObject(Of Dictionary(Of string, DataTable))(json_result).Values.SingleOrDefault()

Sample fiddle #2:

You asked, I was wondering if making a sort of "substring" deleting the "{ Table:" part and the last } could work? Certainly it's possible but I would not recommend this. You would be manually duplicating some of the logic of a JSON parser. Let Json.NET do the work for you, it will handle whitespace and newlines correctly.

Answer 2

After almost a year of practice with json i decided to answer my question. If you're actually trying to deserialize a json into a datatable we can assume you're using vb.net or c# (in other words visual studio).

To achive this task follow my list:

0) Download and install Newtonsoft.json from Nuget
1) Copy your raw json
2) create a new class in your solution and name it as you want. (example: MyDatatable.vb)
3) In the Mydatatable.vb page : Modify -> Paste Special -> Paste JSON as class (this is the key)

At this point visual studio will start creating a bunch of classes that will help newtonsoft to deserialize your json into the main object. In my case the main object was a class named Table like you can see below :

"{\r\n \"Table\": [\r\n {\r\n \"IdOwner\": \"Davide\",\r\n \"tag_id\": 1,\r\n \"tag_type\": \"3\",\r\n \"tag_group\": \"Group_2\",\r\n \"tag_name\": \"Alfa\",\r\n \"tag_sequence\": 123458,\r\n \"tag_description\": \"Description_2\",\r\n \"tag_short_descritpion\": \"Desc_2\",\r\n \"tag_um\": \"kg\",\r\n \"tag_active\": true,\r\n \"tag_colle

4) At this point you will have

• A class file named MyDatatable.vb containing many other classes like (*Table)
• Newtonsoft reference installed on your project

this will allow you to deserialize your json with 2 instructions which are:

Dim strMyJson as string = [your raw json here]
Dim tbFinalObject as Table = Newtonsoft.json.jsonconver.deserializeObject(of Table)(strMyJson)

So in your tbFinalObject Object you'll have your json deserialized. Note: if the second instruction gives you an error like 'expected some char at line x' this may be because your json needs to be deserialized into a string before to be deserialized into another object so you should have a set of instructions like below:

 Dim strMyJson as string = [your raw json here]
 Dim strMyJson as string = Newtonsoft.json.jsonconvert.deserializeObject(of string)([your raw json here])
 Dim tbFinalObject as Table = Newtonsoft.json.jsonconvert.deserializeObject(of Table)(strMyJson)

That's how i solve most of my issues with json and vb.net deserializations.

Further Notes: When you paste a json as class visual studio will automatically create object and create some property as arrays. I use to replace arrays with lists because i found that some old versions of newtonsoft have some problems deserializing json into arrays.