Click here to Skip to main content
14,869,829 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hi, I'm fairly new to using JSON and I have a problem with deserialization of a json, I have a rest service that returns a JSON formatted like this:

VB
{
result: [
        {
        meta: [
                [
                "CODICE",
                "ftString",
                16
                ],
                [
                "DESCRIZIONE",
                "ftString",
                150
                ],
                [
                "CODCATEGORIA",
                "ftString",
                50
                ],
                [
                "PREZZO",
                "ftFloat"
                ],
                [
                "DESCRIZIONEBREVEWEB",
                "ftString",
                100
                ]
            ],
        data: [
                [
                "8871_TILAK07",
                "CALAMITA FERMASOLDI IN PELLE",
                "PELLE",
                "",
                "CALAMITA FERMASOLDI IN PELLE"
                ]
            ]
        }
    ]
}


What I have tried:

I tried to deserialize with Newtonsoft in .Net :
VB
      Dim articoli As List(Of JsonContract.Result) = JsonConvert.DeserializeObject(Of List(Of JsonContract.Result))(ResponseFromServer)
...
 Public Class Result
      Public Property Data As List(Of Data)

   End Class
   Public Class Data
      '[["CODICE","ftString",16],["DESCRIZIONE","ftString",150],["COMPOSIZIONE","ftString",100],["CODCATEGORIA","ftString",50],["PREZZO","ftFloat"],["PREZZOPROMO","ftFloat"],["SCONTOPROMO","ftFloat"],["PROMODADATA","ftTimeStamp"],["PROMOADATA","ftTimeStamp"],["CODMODELLO","ftString",16],["DESCRIZIONEBREVEWEB","ftString",100]]

      Public Property Codice As String
      Public Property Descrizione As String
      Public Property Composizione As String
      Public Property CodCategoria As String
....
   End Class


but "articoli" is always nothing.

How can I place the json received in a dataset / datatable
many thanks in advance
Posted
Updated 22-Feb-17 7:03am
Comments
Maciej Los 22-Feb-17 8:59am
   
A structure of json data look like:
Result
|---meta
|---data

Take a look at this CP article
Graeme_Grant 22-Feb-17 9:05am
   
C# might be a bit much for them...
Maciej Los 22-Feb-17 9:09am
   
C#, VB.NET - what's the difference? If Claudio feels better with VB.NET, let continue His job in that programming language...
Claudio Trenca 22-Feb-17 9:52am
   
I had already read the article, but the proposed solution is the same one that I used and the result is the following error :

Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'System.Collections.Generic.List`1[GenoveseSync.JsonContract+ResultArticoli]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly.
To fix this error either change the JSON to a JSON array (e.g. [1,2,3]) or change the deserialized type so that it is a normal .NET type (e.g. not a primitive type like integer, not a collection type like an array or List<t>) that can be deserialized from a JSON object. JsonObjectAttribute can also be added to the type to force it to deserialize from a JSON object.
Graeme_Grant 22-Feb-17 16:32pm
   
I have updated my solution so that you can keep the data structure that you have defined. All that you need to do is add an Attribute to the root class and the converter helper will do the mapping for you.
Graeme_Grant 22-Feb-17 16:49pm
   
If you don't mind, I would like to publish a CodeProject article discussing custom Json conversion using your problem as one example of several.
Claudio Trenca 23-Feb-17 14:17pm
   
no problem
Graeme_Grant 16-Aug-17 7:15am
   
If you are interested, I finally published the article on JSON deserialization: Working with JSON in C# & VB[^]
Claudio Trenca 17-Aug-17 5:21am
   
Thank you, I will read it as soon as possible

Here is a Converter Helper class (converted from C#) used in commercial applications. It will convert to/From POCOs <-> JSON.
VB
Imports Newtonsoft.Json

Public Class JsonConverter
    Public Shared Function FromClass(Of T)(data As T, Optional isEmptyToNull As Boolean = False, Optional jsonSettings As JsonSerializerSettings = Nothing) As String
        Dim response As String = String.Empty

        If (data IsNot Nothing) Then
            Try ' try..catch only required during debugging
                response = JsonConvert.SerializeObject(data, jsonSettings)
            Catch ex As Exception
                Debug.WriteLine(ex)
                Throw
            End Try
        End If
        Return If(isEmptyToNull, (If(response = "{}", "null", response)), response)
    End Function

    Public Shared Function ToClass(Of T)(data As String, Optional jsonSettings As JsonSerializerSettings = Nothing) As T
        Dim response As T = Nothing

        If Not String.IsNullOrEmpty(data) Then
            Try ' try..catch only required during debugging
                response = If(jsonSettings Is Nothing, JsonConvert.DeserializeObject(Of T)(data), JsonConvert.DeserializeObject(Of T)(data, jsonSettings))
            Catch ex As Exception
                Debug.WriteLine(ex)
                Throw
            End Try
        End If
        Return response
    End Function
End Class
To Use it:
VB
Dim raw As New Data() With
{
    .Codice = "Field 1",
    .Descrizione = "Field 2",
    .Composizione = "Field 3",
    .CodCategoria = "Field 4"
}

Dim json As String = JsonConverter.FromClass(raw)
Dim raw2 = JsonConverter.ToClass(Of Data)(json)


UPDATE

I must admit, I did not look closely at the JSON data until I looked at Richard's Solution.

So the following update uses the above JsonConverter helper class but also maintains the class structure that you want. The only thing that you need to do is add the custom ResultDataConverter attribute to the Result class.
VB
Imports System.IO
Imports Newtonsoft.Json.Linq
Imports Json = Newtonsoft.Json

Module Module1

    Sub Main()

        Dim filePath As String = Path.Combine("FilePath", "FileName")
        Dim data As String = FileHelper.TextRead(filePath)

        Dim result = JsonConverter.ToClass(Of Result)(data)
        Debugger.Break()

    End Sub

End Module

<Json.JsonConverter(GetType(ResultDataConverter))>
Public Class Result
    Public Property Data() As List(Of Data)
End Class

Public Class Data
    Public Property CODICE() As String
    Public Property DESCRIZIONE() As String
    Public Property COMPOSIZIONE() As String
    Public Property CODCATEGORIA() As String
    Public Property PREZZO() As Single
    Public Property PREZZOPROMO() As Single
    Public Property SCONTOPROMO() As Single
    Public Property PROMODADATA() As Date
    Public Property PROMOADATA() As Date
    Public Property CODMODELLO() As String
    Public Property DESCRIZIONEBREVEWEB() As String
End Class

Public Class ResultDataConverter
    Inherits Json.JsonConverter
    Public Overrides Function CanConvert(objectType As Type) As Boolean
        Return True
    End Function

    Public Overrides Function ReadJson(reader As Json.JsonReader, objectType As Type, existingValue As Object, serializer As Json.JsonSerializer) As Object
        Dim obj As Result = Nothing
        If reader.TokenType = Json.JsonToken.StartObject Then
            Dim jObject As JObject = JObject.Load(reader)
            If jObject IsNot Nothing AndAlso jObject.HasValues Then
                Dim result = jObject("result")
                If result IsNot Nothing Then
                    obj = New Result()
                    Dim root = serializer.Deserialize(Of IList(Of JObject))(result.CreateReader())
                    Dim jMeta = root.First().Children().Where(Function(x) x.Path = "meta")
                    Dim jData = root.First().Children().Where(Function(x) x.Path = "data")
                    Dim lookup = jMeta.Children() _
                                      .Children() _
                                      .Select(Function(x) x.First.ToString()) _
                                      .Select(Function(x, i) New With {.x = x, .i = i}) _
                                      .ToDictionary(Function(x) x.x, Function(x) x.i)
                    Dim dataElements = jData.Children().Children().ToList()
                    obj.Data = New List(Of Data)()
                    For i As Integer = 0 To dataElements.Count - 1
                        Dim element = New Data()
                        If lookup.Keys.Contains("CODICE") Then
                            element.CODICE = dataElements(i)(lookup("CODICE")).ToString()
                        End If
                        If lookup.Keys.Contains("DESCRIZIONE") Then
                            element.DESCRIZIONE = dataElements(i)(lookup("DESCRIZIONE")).ToString()
                        End If
                        ' do the same for all other data properties
                        obj.Data.Add(element)
                    Next
                End If
            End If
        End If
        Return obj
    End Function

    Public Overrides Sub WriteJson(writer As Json.JsonWriter, value As Object, serializer As Json.JsonSerializer)
        Throw New NotImplementedException()
    End Sub
End Class

Public Class FileHelper
    Public Shared Function TextRead(fileName As String) As String
        Return TextRead(Path.GetFileName(fileName), Path.GetDirectoryName(fileName))
    End Function

    Public Shared Function TextRead(fileName As String, filePath As String) As String
        Dim data As String = String.Empty
        Dim myFile As String = Path.Combine(filePath, fileName)

        If Directory.Exists(filePath) AndAlso File.Exists(myFile) Then
            Using reader = New StreamReader(myFile)
                data = reader.ReadToEnd()
            End Using
        End If
        Return data
    End Function
End Class
By encapsulating the property mapping in a custom converter, this will make it easier in future to update if the JSON meta fields change. Simply update the Data class properties, then adjust the mappings in the custom converter.

Note: The custom ResultDataConverter is incomplete. You will need to add the other properties where I left a comment and bullet-proof the conversion if there is incomplete JSON data.
   
v5
Comments
Karthik_Mahalingam 23-Feb-17 12:25pm
   
5
The problem you have is that the JSON you're trying to deserialize doesn't represent a List(Of JsonContract.Result). It actually represents a single object that looks something like this:
VB.NET
' { result: [...] }
Public Class ResultWrapper
    Public Property result As List(Of ResultSet)
End Class

' { meta: [...], data: [...] }
Public Class ResultSet
    Public Property meta As List(Of MetaField)
    Public Property data As List(Of DataRecord)
End Class

' ["name", ...]
Public Class MetaField : Inherits List(Of Object)
    Public Readonly Property FieldName As String
        Get
            Return DirectCast(Me(0), String)
        End Get
    End Property
End Class

' [field, ...]
Public Class DataRecord : Inherits List(Of Object)
End Class

...

Dim result As ResultWrapper = JsonConvert.DeserializeObject(Of ResultWrapper)(ResponseFromServer)

Once you've got a ResultWrapper, you'll need to find the first ResultSet, loop through the meta list to find the index of the properties you want to map, and finally loop through the data list and map each property.
VB.NET
Dim resultSet As ResultSet = result.result(0)

Dim iCodice As Integer = -1, iDescrizione As Integer = -1, iCodCategoria As Integer = -1, iComposizione As Integer = -1
For i As Integer = 0 To resultSet.meta.Count - 1
    Select Case meta(i).FieldName
        Case "CODICE"
            iCodice = i
        Case "DESCRIZIONE"
            iDescrizione = i
        Case "CODCATEGORIA"
            iCodCategoria = i
        Case "COMPOSIZIONE"
            iComposizione = i
    End Select
Next

Dim articoli As New List(Of Data)(resultSet.data.Count)
For Each record As DataRecord In resultSet.data
    Dim item As New Data()
    If iCodice <> -1 Then item.Codice = DirectCast(record(iCodice), String)
    If iDescrizione <> -1 Then item.Descrizione = DirectCast(record(iDescrizione), String)
    If iCodCategoria <> -1 Then item.CodCategoria = DirectCast(record(iCodCategoria), String)
    If iComposizione <> -1 Then item.Composizione = DirectCast(record(iComposizione), String)
    articoli.Add(item)
Next
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900