I am deserializing JSON into a data table using the below statement in VB.NET.
Dim _dt As DataTable = JsonConvert.DeserializeObject(Of DataTable)(myRecords)
myRecords is a JSON string.
It is working fine, but myRecords has some number properties like {"PhoneNo":"123456789", "ID":"46541"} and after deserialization those properties are converted to a column with data type as string.
How can I deserialize them into a number? myRecords is getting dynamically populated, so I can't hard code.
The reason Json.NET creates a string-typed column for "PhoneNo" : "123456789" is that "123456789" is, in fact, a string literal according to the JSON standard. A numeric literal would look like this, without double-quotes around the value: 123456789. Are you certain that these properties will always be numeric strings? Not all phone numbers are numeric, e.g., so it seems unwise to hardcode them as such.
That being said, if you are sure that these properties will always be numeric strings and want Json.NET to create numeric DataTable columns for them, you need to tell it in advance the desired type for those columns. One option would be to create a typed DataTable from an appropriate schema. In that case, JsonConvert.DeserializeObject(Of TTypedDataTable)(myRecords) will create a DataTable subclass with the required column types.
Another option would be to create the DataTable manually with an appropriate set of columns, then populate the table from your JSON. Unfortunately JsonConvert.PopulateObject() will not work on a preallocated DataTable so you need to call DataTableConverter.ReadJson() directly. This could be done with the following extension method:
Public Module JsonExtensions
Public Sub PopulateDataTable(json As String, target As DataTable, Optional settings As JsonSerializerSettings = Nothing)
Using reader = New JsonTextReader(New StringReader(json))
Do
If reader.TokenType = JsonToken.StartArray Then
' Populate the table
Dim converter = New DataTableConverter()
converter.ReadJson(reader, target.GetType(), target, JsonSerializer.CreateDefault(settings))
End If
Loop While reader.Read()
End Using
End Sub
End Module
Then use it as follows:
Dim _dt = New DataTable()
_dt.Columns.Add("PhoneNo", GetType(Long))
_dt.Columns.Add("ID", GetType(Long))
JsonExtensions.PopulateDataTable(myRecords, _dt)
Example fiddle.
You also wrote, I can't hard code. If you really don't know in advance which columns with string values should actually be deserialized as numeric types, what you can do is to pre-process the JSON by loading it into a Jtoken, grouping all properties values by name, and for each group, checking that all the values in the group are strings that are convertible to numbers. If all are convertible, you can make the conversion. But if only some are convertible you should not make the conversion as this will break Json.NET's type inference algorithm. It can be done using the following extension methods:
Public Module JsonExtensions
Private ReadOnly NumberTypes = New JTokenType() {JTokenType.[Integer], JTokenType.Float, JTokenType.[String], JTokenType.Comment, JTokenType.Raw, JTokenType.[Boolean]}
Private Function ValidateToken(o As JToken, validTypes As JTokenType(), nullable As Boolean) As Boolean
Return (Array.IndexOf(validTypes, o.Type) <> -1) OrElse (nullable AndAlso (o.Type = JTokenType.Null OrElse o.Type = JTokenType.Undefined))
End Function
<System.Runtime.CompilerServices.Extension> _
Public Function CanConvertToNullableLong(token As JToken) As Boolean
' Reverse engineered from
' public static explicit operator long?(JToken value)
' https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Linq/JToken.cs#L1045
If token Is Nothing OrElse token.Type = JTokenType.Null OrElse token.Type = JTokenType.Boolean Then
Return True
End If
If Not ValidateToken(token, NumberTypes, True) Then
Return False
End If
Dim jValue = TryCast(token, JValue)
If jValue Is Nothing Then
Return False
End If
If TypeOf jValue.Value Is BigInteger Then
Dim i = CType(jValue.Value, BigInteger)
Return i <= Long.MaxValue AndAlso i >= Long.MinValue
End If
Dim s = CType(jValue, String)
Dim v As Long
Return Long.TryParse(s, NumberStyles.Number, NumberFormatInfo.InvariantInfo, v)
End Function
Public Sub TryConvertColumnsToNullableLong(root As JToken)
If TypeOf root Is JContainer Then
' If ALL columns values of a given name can be converted from string to long, then do so.
' Do not convert columns where some but not all are convertable.
For Each group In DirectCast(root, JContainer) _
.Descendants() _
.OfType(Of JProperty)() _
.GroupBy(Function(p) p.Name) _
.Where(Function(g) g.All(Function(p) (p.Value.Type = JTokenType.String Or p.Value.Type = JTokenType.Null) AndAlso p.Value.CanConvertToNullableLong()))
For Each p In group
p.Value = CType(p.Value, System.Nullable(Of Long))
Next
Next
End If
End Sub
End Module
Then preprocess and deserialize as follows:
Dim token = JToken.Parse(myRecords)
JsonExtensions.TryConvertColumnsToNullableLong(token)
Dim _dt = token.ToObject(Of DataTable)()
Example fiddle #2.
I'm not sure I would recommend this however. The fact that the JSON values are strings suggest that, in the database from which the JSON was generated, these values could be arbitrary strings. If so, hacking in a conversion to long could cause problems later when non-numeric values start getting entered into the database.
