Get value from JArray item with has inconsistent values

Question

I'm reading a JSON file from an external partner which is not totally consistent (and they are not changing it anytime soon).

I want to check the value of the field bathroom and store that in an integer. So here's a mapping of the potential values there are and what I would like to get:

"" = 0
 = 0 (here no value is present)
2 = 2
"2" = 2

But whatever I try (see below) I get the error:

Input string was not in a correct format.

    Dim json as string = "[{""bathrooms"": """"}, {""bathrooms"": }, {""bathrooms"": 2},{""bathrooms"": ""1""}]"
    Dim iBathrooms As Integer 
    Dim jsonArray As Newtonsoft.Json.Linq.JArray = JArray.Parse(json)

    For Each item In jsonArray
        iBathrooms= If(item.Value(Of Integer?)("bathrooms"), 0)
        iBathrooms = If(CType(item("bathrooms"), Integer?), 0)
        Int32.TryParse(item("bathrooms").Value(Of Integer).ToString, iBathrooms)        
    Next

I already checked here: Get value from JToken that may not exist (best practices)

Answer 1

If the problem with the JSON is consistently that it is missing the value, you could insert, say, an empty string:

Dim json As String = "[{""bathrooms"": """"}, {""bathrooms"": }, {""bathrooms"": 2},{""bathrooms"": ""1""}]"
Dim re = New Text.RegularExpressions.Regex(":\s*}")
Dim json2 = re.Replace(json, ": """"}")

Console.WriteLine(json2)

Outputs:

[{"bathrooms": ""}, {"bathrooms": ""}, {"bathrooms": 2},{"bathrooms": "1"}]

which is valid JSON.

Then you could check if the value can be parsed as an integer:

Dim json As String = "[{""bathrooms"": """"}, {""bathrooms"": """"}, {""bathrooms"": 2},{""bathrooms"": ""1""}]"

Dim re = New Text.RegularExpressions.Regex(":\s*}")
json = re.Replace(json, ": """"}")

Dim nBathrooms As Integer
Dim jsonArray As Newtonsoft.Json.Linq.JArray = JArray.Parse(json)

For Each item In jsonArray
    Dim q = item("bathrooms")
    If q IsNot Nothing AndAlso Integer.TryParse(q.Value(Of Object).ToString(), nBathrooms) Then
        Console.WriteLine(nBathrooms)
    Else
        Console.WriteLine("Not specified.")
    End If

Next