[SOLVED] Get value from JArray item with has inconsistent values

Issue

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)

Solution

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

Answered By – Andrew Morton

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *