将JSON转换为DataTable（使用VB）

Question

早上好，今天我试图将我的JSON转换为数据表。

这是我想要做的事情。

    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

我收到了一个异常，消息是：“读取 DataTable 时出现异常：JSON 令牌不符预期。应为 StartArray，但为 String。路径‘’，行 1，位置 9919。”。

我在 json lint 上验证过我的 json ，它说我的 json 是有效的。有没有人能帮我解决这个问题？

以下是我的原始 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.

- Madalin Mad

---

非常感谢您的回答，它真的很有用。 我在想是否可以制作一种类似于“子字符串”的东西，删除“{Table:”部分和最后一个}是否可行？ 无论如何，还是非常感谢。 - Madalin Mad

答案已更新。 - dbc

Answer 1

首先需要删除这一行：

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

下载的字符串已经是 JSON 格式，如果你再序列化一次，就会变成一个被转义的 JSON 字符串文本。但这样反序列化返回的只是字符串，而不是所需的 DataTable。这也解释了错误信息：“Unexpected JSON token when reading DataTable. Expected StartArray, got String. Path '', line 1, position 9919”。Json.NET 将整个“json_jsonstring”字符串作为单个转义的字符串文本进行解析。

其次，你的 JSON 包含一个外部根对象，格式如下：

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

因此，您需要引入一个带有必要属性Table的根对象，以将DataTable反序列化为该对象。您可以使用以下通用根对象进行操作：

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

然后按以下方式反序列化：

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

示例 fiddle。

或者，如果您不想创建根类型，请反序列化为临时的Dictionary(Of string, DataTable)：

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

示例 Fiddle #2:

您问道：我想知道是否可以制作一种“子字符串”，删除“{Table：”部分和最后一个}是否可行？当然是可能的，但我不建议这样做。你将手动复制 JSON 解析器的一些逻辑。让 Json.NET 为您处理这项工作，它将正确处理空格和换行符。

- dbc

Answer 2

经过近一年的json实践，我决定回答我的问题。如果您确实想将json反序列化为datatable，我们可以假设您正在使用vb.net或c#（换句话说，是visual studio）。

要完成这个任务，请按照以下步骤：

0) 从Nuget下载并安装Newtonsoft.json
1) 复制您的原始json
2) 在解决方案中创建一个新类，并根据需要命名它。 （例如：MyDatatable.vb）
3) 在Mydatatable.vb页面上：修改->粘贴特殊->粘贴JSON作为类（这是关键）

此时，Visual Studio将开始创建许多辅助newtonsoft将您的json反序列化为主对象的类。在我的情况下，主对象是一个名为Table的类，如下所示：

"{\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)此时您将拥有：

• 一个名为MyDatatable.vb的类文件，其中包含许多其他类（例如*Table）
• 安装在项目中的Newtonsoft引用

这将使您能够使用两个指令反序列化您的json:

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

因此，在您的tbFinalObject对象中，您将拥有反序列化的JSON。 注意：如果第二条指令给出类似“在x行处预期一些字符”的错误，这可能是因为您的JSON需要在反序列化为另一个对象之前反序列化为字符串，因此您应该有以下一组指令：

 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)

这是我解决大部分json和vb.net反序列化问题的方法。

进一步说明：当您将json粘贴为类时，Visual Studio将自动创建对象并创建一些属性作为数组。我使用列表替换数组，因为我发现某些旧版本的newtonsoft在将json反序列化为数组时存在一些问题。

- Madalin Mad

Answer 3

下载 Newtonsoft Dll 文件https://www.newtonsoft.com/json

Imports Newtonsoft

Dim json As String = IO.File.ReadAllText(HttpContext.Current.Server.MapPath("~/Data.json"))
Dim dt As New System.Data.DataTable

dt = Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataTable)(json)

- Nisha Gupta