Convert Json to Datatable when json has multiple levels

Question

In my program, it's the first time that I'm trying to parse JSON content.
The result I'm trying to get is a DataTable with columns like this:

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

The JSON is the following:

{
      "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
       }
    ]
 }

In the past, I have succesfully parsed complex XML using XPATH, but now I have struggled a lot using Newtonsoft.Json with no success.

What I have tried, is to first create 3 classes using online generators:

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

Any ideas how the deserialized commands should be, in order the get the data as a DataTable with the structure described?

Jimi · Accepted Answer

You have an almost working class model, some changes are necessary to make it work as intended.

This kind of syntax is misleading:

Public Property Groups() As Group

This is not a collection of objects, it's just a single object of Type Group.
Change all to:

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

To convert to DataTable with a specific selection of Columns, you need to iterate the Groups collection and, for each group, iterate the Items collection, to extract the values you need.
Here, I'm using a specialized class, GroupsHandler, that contains the class Model used to deserialize a compatible JSON and to convert to DataTable partial content of the resulting data structure.

The GroupsHandler class exposes two Public methods:

Deserialize(), used to convert to .Net classes the JSON content
ToDataTable(), used to create a DataTable from the deserialized content.

You can initialize the handler and call these method as:

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

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

The Group class name is changed in ItemsGroup, since Group is a language keyword.
The 64String property name changed in String64, since you cannot have a Property Name that begins with a number.

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)
        
        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
        
        Public Property ImageIndex As Long?
        
        Public Property String64 As String
    End Class
End Class

Convert Json to Datatable when json has multiple levels

Answers (1)

Related Questions