时间戳

首页 > 解决方案 > 当json有多个级别时将Json转换为Datatable

json - 当json有多个级别时将Json转换为Datatable

问题描述

在我的程序中，这是我第一次尝试解析 JSON 内容。
我试图得到的结果是一个带有如下列的 DataTable：

| Text of group | Text of Item | Command of Item |

JSON如下：

{
      "Groups": [
        {
          "Items": [
            {
              "Command": "Framework.Windows.Components.ESScrollerForm, ESGrid|SHOW|Χρήστες|ESGOUser|ESGOUser_def|||65535",
              "Key": "834888dd-c4d5-449a-96b7-67db5c3d2692",
              "Text": "Users",
              "ImageIndex": -1
            },
            {
              "Command": "Framework.Windows.Components.ESScrollerForm, ESGrid|SHOW|QuestionaireSurveyorQuery|ESTMTask|QuestionaireSurveyorQuery|||0",
              "Key": "b71de66d-2baf-4452-ada7-8fc67044876b",
              "Text": "QuestionaireSurveyorQuery"
            }
          ],
          "Expanded": true,
          "Tag": "",
          "Key": "b741e67a-a3cd-4b97-91cf-ae9c9d9db7d7",
          "Text": "Settings",
          "ImageIndex": -1
        },
        {
          "Items": [
            {
              "64String": "Soap",
              "Command": "cInvoke|Booked Requests Agent Booking|SHOW|ESMIS|BookedReqAgentBook||False",
              "Key": "bfbc3d4a-ef8a-49a0-918a-331813ba90fb",
              "Text": "Requests Agent Booking",
              "ImageIndex": -1
            },
            {
              "64String": "Jrse",
              "Command": "cInvoke|HHG SF Profit \u0026 Loss|SHOW|ESMIS|HHGFileProfitability||False",
              "Key": "cf1cbffc-aba9-4e0f-8d6c-ba7219932fb6",
              "Text": "HHG SF Profit \u0026\u0026 Loss",
              "ImageIndex": -1
            }
          ],
          "Tag": "..CSShortcuts\\HHGReporting.ebl",
          "Key": "eff0d713-a70e-4582-a103-b8cc5cecdad6",
          "Text": "HHGReporting",
          "ImageIndex": -1
       }
    ]
 }

过去，我使用 成功解析了复杂的 XML XPATH，但现在使用 Newtonsoft.Json 时遇到了很多困难，但没有成功。

我尝试过的是首先使用在线生成器创建 3 个类：

Public Class Rootobject
    Public Property Groups() As Group
End Class

Public Class Group
    Public Property Items() As Item
    Public Property Expanded As Boolean
    Public Property Tag As String
    Public Property Key As String
    Public Property Text As String
    Public Property ImageIndex As Integer
End Class

Public Class Item
    Public Property Command As String
    Public Property Key As String
    Public Property Text As String
    Public Property ImageIndex As Integer
    Public Property 64String As String
End Class

任何想法应该如何反序列化命令，以便将数据作为具有描述结构的 DataTable 获取？

标签： jsonvb.netdatatablejson.netdeserialization

解决方案

您有一个几乎是工人阶级的模型，需要进行一些更改才能使其按预期工作。

这种语法具有误导性：

Public Property Groups() As Group

这不是对象的集合，它只是 Type 的单个对象Group。
全部更改为：

Public Property Groups As Group()
'or 
Public Property Groups As List(Of Group)

要转换为具有特定列选择的 DataTable，您需要迭代 Groups 集合，并且对于每个组，迭代 Items 集合，以提取您需要的值。
在这里，我使用了一个专门的类，GroupsHandler它包含用于反序列化兼容 JSON 并将结果数据结构的部分内容转换为 DataTable 的类 Model。

该类GroupsHandler公开了两个公共方法：

  • Deserialize()，用于将 JSON 内容转换为 .Net 类
  • ToDataTable()，用于从反序列化的内容创建 DataTable。

您可以初始化处理程序并将这些方法称为：

Dim handler = New GroupsHandler(Json)
' Only deserialize  
Dim myGroups = handler.Deserialize()

' Deserialize and convert to DataTable
Dim dt = handler.ToDataTable()

类名在Group中更改ItemsGroup，因为Group是语言关键字。属性名称在 中更改
，因为您不能拥有以数字开头的属性名称。64StringString64

Imports Newtonsoft.Json

Public Class GroupsHandler
    Private root As GroupsRoot = Nothing
    Private m_json As String = String.Empty

    Public Sub New(json As String)
        m_json = json
    End Sub

    Public Function Deserialize() As List(Of ItemsGroup)
        root = JsonConvert.DeserializeObject(Of GroupsRoot)(m_json)
        Return root.Groups
    End Function

    Public Function ToDataTable() As DataTable
        If root Is Nothing Then
            If String.IsNullOrEmpty(m_json) Then Return Nothing
            Deserialize()
        End If

        Dim dt As New DataTable("Groups")
        dt.Columns.AddRange(New DataColumn() {
            New DataColumn("GroupText", GetType(String)),
            New DataColumn("ItemText", GetType(String)),
            New DataColumn("ItemCommand", GetType(String))
        })

        For Each grp In root.Groups
            For Each item In grp.Items
                dt.Rows.Add(New Object() {grp.Text, item.Text, item.Command})
            Next
        Next
        Return dt
    End Function

    Public Class GroupsRoot
        Public Property Groups As List(Of ItemsGroup)
    End Class

    Public Class ItemsGroup
        Public Property Items As List(Of Item)
        <JsonProperty("Expanded", NullValueHandling:=NullValueHandling.Ignore)>
        Public Property Expanded As Boolean?
        Public Property Tag As String
        Public Property Key As Guid
        Public Property Text As String
        Public Property ImageIndex As Long
    End Class

    Public Class Item
        Public Property Command As String
        Public Property Key As Guid
        Public Property Text As String
        <JsonProperty("ImageIndex", NullValueHandling:=NullValueHandling.Ignore)>
        Public Property ImageIndex As Long?
        <JsonProperty("64String", NullValueHandling:=NullValueHandling.Ignore)>
        Public Property String64 As String
    End Class
End Class

推荐阅读