[RESOLVED] Newtonsoft help

[nbrege]

I'm trying to get a value from a json string using Newtonsoft, but I can't seem to figure this one out.

Here is the json string:

{
  "error_message": "",
  "success": true,
  "metadata": [
    {
      "id": "COUNT(*)",
      "key": "COUNT(*)",
      "name": "COUNT(*)",
      "type": "number",
      "data": null,
      "table_id": "",
      "table_name": "",
      "base_id": ""
    }
  ],
  "is_join_stmt": false,
  "results": [
    {
      "COUNT(*)": 9129
    }
  ]
}

I'm trying to get the value in the results>COUNT(*) node. (9129 in this case)

I have tried the following code, but this gives me an error:

Dim jsonObject As JObject = JObject.Parse(jsonString)
resultsNode = jsonObject("results")
Dim count As String = resultsNode(0).SelectToken("COUNT(*)").ToString

Any help would be appreciated. Thanks...

[wes4dbt]

I don't know what's causing your error but I would suggest you tell us what the error is and what line throws the error.

[jdelano]

I used CoPilot to create the classes (why type that stuff?) screenshot: https://imgur.com/a/peAmuBD

    Public Class Metadata
        <JsonProperty("id")>
        Public Property Id As String
        <JsonProperty("key")>
        Public Property Key As String
        <JsonProperty("name")>
        Public Property Name As String
        <JsonProperty("type")>
        Public Property Type As String
        <JsonProperty("data")>
        Public Property Data As Object
        <JsonProperty("table_id")>
        Public Property TableId As String
        <JsonProperty("table_name")>
        Public Property TableName As String
        <JsonProperty("base_id")>
        Public Property BaseId As String
    End Class

    Public Class Result
        <JsonProperty("COUNT(*)")>
        Public Property Count As Integer
    End Class

    Public Class Root
        <JsonProperty("error_message")>
        Public Property ErrorMessage As String
        <JsonProperty("success")>
        Public Property Success As Boolean
        <JsonProperty("metadata")>
        Public Property Metadata As List(Of Metadata)
        <JsonProperty("is_join_stmt")>
        Public Property IsJoinStmt As Boolean
        <JsonProperty("results")>
        Public Property Results As List(Of Result)
    End Class


    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click

        Dim jsonString As String = "{""error_message"": """", ""success"": true, ""metadata"": [{""id"": ""COUNT(*)"", ""key"": ""COUNT(*)"", ""name"": ""COUNT(*)"", ""type"": ""number"", ""data"": null, ""table_id"": """", ""table_name"": """", ""base_id"": """"}], ""is_join_stmt"": false, ""results"": [{""COUNT(*)"": 9129}]}"
        Dim dsJSON As Root = JsonConvert.DeserializeObject(Of Root)(jsonString)
        Dim resultINT As Int16 = dsJSON.Results(0).Count

        MessageBox.Show("The count is: " & resultINT.ToString())

    End Sub

[dday9]

@jdelano - You don't even need CoPilot. Visual Studio offers something out of the box: Edit > Paste Special > Paste JSON as Classes. You would just need to change metadata and results from using parenthesis to being IEnumerables of their types.

Also, a slight change to your code for better error handling:

Dim firstResult As Result = dsJSON.Results.FirstOrDefault()
Dim resultINT As Integer = If(firstResult, New Result()).Count

[jdelano]

Indeed it does, I've actually used it before. I couldn't remember if VS2019 had that or not.

[nbrege]

I figured it out. I was actually pretty close in my original code snippet.

Here's the solution:

Dim count As Integer = CInt(resultsNode(0)("COUNT(*)").ToString)

[dday9]

nbrege, I would suggest that your solution is a bit short sighted. For example, what would happen if your JSON doesn't match the sample?

Also, keep in mind that VB.NET is designed to be an object-oriented programming language. The tools to convert your JSON literal to classes are built-in to Visual Studio, they do not require much effort on your part, and Newtonsoft supports converting JSON literals to classes out of the box.

No question, your solution will work, but I'd highly suggest taking jdelano's suggestion in post #3 and my improvement in post #4.

[nbrege]

dday9 ... thank you for the suggestion. I will have to try that some time. In my particular case the JSON will always match the sample.