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)

Andrew Morton · Accepted Answer · 2022-03-10T20:36:33.760

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

edited Mar 10 '22 at 20:36

answered Mar 10 '22 at 18:01

Andrew Morton

24,203
9
60
84

Thanks, this is however not the problem. Because even with string `Dim json As String = "[{""bathrooms"": """"}, {""bathrooms"": 2},{""bathrooms"": ""1""}]"`, I still can't seem to retrieve all possible values with my existing statements. It always results in error "Input string was not in a correct format". So what code do I need to extract the value of bathrooms even after I ran your code? – Adam Mar 10 '22 at 19:30
Please try the actual string, after my code, in a JSON validator such as [JSONLint](https://jsonlint.com/). You may have to make more corrections to the string to make it into actual JSON. (The external partner should be ashamed of themselves for providing invalid JSON.) – Andrew Morton Mar 10 '22 at 20:10
@Adam I added some more code - perhaps that gives you something better to work with. – Andrew Morton Mar 10 '22 at 20:37
Thank you. Let me check. What namespace is `Text` in? And what exactly does your regex do? Remove all spaces, but does it do more? – Adam Mar 10 '22 at 20:53
1

The regex was intended to fix the invalid `{""bathrooms"": }`. If you hover over the `Text` that VS is complaining about, it will suggest fixes for you - if you have [`Option Strict On`](https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/statements/option-strict-statement) as you should and Microsoft refuse to give a good reason for it being set to Off for the past 15 years or so. – Andrew Morton Mar 10 '22 at 21:01

Get value from JArray item with has inconsistent values

1 Answers1