JSON.NET Return Null or Nothing value

Question

I am currently building JSON by using data stored in datatables. However what is occuring is where the data does not exist in a column for a data table it is returning an empty ("") string to my property.

This is then coming out on my JSON and then it fails to ingest due to the web service in question not validating it.

However if I set the value to "Nothing" it doesn't serialize and thus doesn't appear in the JSON. How can I get Nothing to return to all my string values where the string = "".

I could do this by writing a Function that tests for the string and returns Nothing howevever I feel there must be something wrong for me to have to do it this way.

Here is an example

Public Class Policy

<JsonProperty("policy_id", NullValueHandling:=NullValueHandling.Ignore)>
Public Property policy_id As String = Nothing
<JsonProperty("insurer_name", NullValueHandling:=NullValueHandling.Ignore)>
Public Property insurer_name As String = Nothing
<JsonProperty("policy_name", NullValueHandling:=NullValueHandling.Ignore)>
Public Property policy_name As String = Nothing
<JsonProperty("product_name", NullValueHandling:=NullValueHandling.Ignore)>
Public Property product_name As String = Nothing
<JsonProperty("sale_date", NullValueHandling:=NullValueHandling.Ignore)>
Public Property sale_date As DateTime = Nothing
<JsonProperty("start_date", NullValueHandling:=NullValueHandling.Ignore)>
Public Property start_date As DateTime = Nothing
<JsonProperty("end_date", NullValueHandling:=NullValueHandling.Ignore)>
Public Property end_date As DateTime = Nothing
<JsonProperty("status", NullValueHandling:=NullValueHandling.Ignore)>
Public Property status As String = Nothing
<JsonProperty("vehicles", NullValueHandling:=NullValueHandling.Ignore)>
Public Property vehicles As New List(Of Vehicle)
<JsonProperty("people", NullValueHandling:=NullValueHandling.Ignore)>
Public Property persons As New List(Of Person)

End Class

Private Function get_JSON(ByVal branch As String, ByVal policyref As String) As String

    For Each p As DataRow In dt_policies.Rows
      
        Dim oPolicy As New Policy() With {
            .policy_id = p("B@") & p("PolRef@"),
            .insurer_name = p("insurer_name"),
            .policy_name = p("policy_name"),
            .product_name = p("product_name"),
            .sale_date = p("sale_date"),
            .start_date = p("start_date"),
            .end_date = p("end_date"),
            .status = p("status"),
            .vehicles = get_vehicles(p("B@"), p("PolRef@")),
            .persons = get_persons(p("B@"), p("PolRef@"))
            }

        Dim json As String = JsonConvert.SerializeObject(oPolicy, NullValueHandling.Ignore)

        Return json
    Next

End Function

Private Function ReturnNothing(ByVal rstring As String) As String
    If rstring = "" Then
        Return Nothing
    Else
        Return rstring
    End If
End Function

Have you considered the answers to [Remove empty string properties from json serialized object](https://stackoverflow.com/questions/41287224/remove-empty-string-properties-from-json-serialized-object)? — Andrew Morton, Nov 18 '20 at 15:49
@AndrewMorton - The problem is the return from the DataTable is either a DBNull or an Empty String...so this overwrites the "Default Value". A DBNull causes a failure, and an Empty String means the element is shown, I have put the workaround I've created but there's clearly a better way of doing this. — Lynchie, Nov 18 '20 at 16:01
Instead of calling it a workaround, you could call it data normalization. — Andrew Morton, Nov 18 '20 at 16:08

score 0 · Answer 1 · answered Nov 18 '20 at 19:12

You could make an extension method to combine extracting and normalizing the data into one step so you don't have to think about it:

Imports System.Runtime.CompilerServices

Module DataTableExtensions
    <Extension>
    Function GetVal(row As DataRow, columnName As String) As Object
        Dim val As Object = row(columnName)
        If TypeOf val Is DBNull OrElse (TypeOf val Is String AndAlso val = "") Then
            Return Nothing
        End If
        Return val
    End Function
End Module

Then in your code, just use the extension method wherever you would use the row indexer:

Dim oPolicy As New Policy() With 
{
    .policy_id = p.GetVal("B@") & p.GetVal("PolRef@"),
    .insurer_name = p.GetVal("insurer_name"),
    .policy_name = p.GetVal("policy_name"),
    .product_name = p.GetVal("product_name"),
    .sale_date = p.GetVal("sale_date"),
    .start_date = p.GetVal("start_date"),
    .end_date = p.GetVal("end_date"),
    .status = p.GetVal("status"),
    .vehicles = get_vehicles(p.GetVal("B@"), p.GetVal("PolRef@")),
    .persons = get_persons(p.GetVal("B@"), p.GetVal("PolRef@"))
}

score 0 · Answer 2 · answered Nov 19 '20 at 10:42

Problem solved.

Public Class Policy
    <DefaultValue("")> <JsonProperty("policy_id")>
    Public Property policy_id As String = Nothing
    <DefaultValue("")> <JsonProperty("insurer_name")>
    Public Property insurer_name As String = Nothing
    <DefaultValue("")> <JsonProperty("policy_name")>
    Public Property policy_name As String = Nothing
    <DefaultValue("")> <JsonProperty("product_name")>
    Public Property product_name As String = Nothing
    <DefaultValue("")> <JsonProperty("sale_date")>
    Public Property sale_date As DateTime = Nothing
    <DefaultValue("")> <JsonProperty("start_date")>
    Public Property start_date As DateTime = Nothing
    <DefaultValue("")> <JsonProperty("end_date")>
    Public Property end_date As DateTime = Nothing
    <DefaultValue("")> <JsonProperty("status")>
    Public Property status As String = Nothing
    <DefaultValue("")> <JsonProperty("vehicles")>
    Public Property vehicles As New List(Of Vehicle)
    <DefaultValue("")> <JsonProperty("people")>
    Public Property persons As New List(Of Person)

End Class

Set a Default Value for each property and then changed the settings of the Serializer.

        Dim settings = New JsonSerializerSettings With {
            .NullValueHandling = NullValueHandling.Ignore,
            .DefaultValueHandling = DefaultValueHandling.Ignore,
            .Formatting = Formatting.Indented
        }

        Dim json As String = JsonConvert.SerializeObject(oPolicy, settings)

JSON.NET Return Null or Nothing value

2 Answers2