Parsing Data from JSON web service vb.net

Question

I am having a bit of an issue getting the various types of nested data from a 3rd party API.

I am calling a web service and getting the json stream back, but I can't seem to serialize the data effectively. I can get the datastream to load into my jobject, but as the structure has an integer that keeps changing I can't just refer to the path.

 Dim json As String = reader.ReadToEnd
    Dim o As JObject = JObject.Parse(json)

   Try
        'oForm.AppendtoLogFile("jSON: " & o.Item("tickets").ToString)
        oForm.AppendtoLogFile("_____________")


        Dim results As List(Of JToken) = o.Children.ToList



        For Each item As JProperty In results

            oForm.AppendtoLogFile("Item name: " & item.Name.ToString)


            Select Case item.Name

                Case "tickets"

                    'next value here is random integer, then array of items

                    Dim sbase = item.Value.ToString

                    'oForm.AppendtoLogFile("Tickets: item.value.tostring " & sbase)

                    item.CreateReader()
                    If item.Value.Type = JTokenType.Array Then
                        Dim results2 As List(Of JToken) = item.Value.ToList
                        For Each subitem As JObject In results2
                            Dim results3 As List(Of JToken) = subitem.Children().ToList
                            For Each temp2 As JProperty In results3
                                temp2.CreateReader()


                                MsgBox(temp2.Name)
                                MsgBox(temp2.Value)
                            Next
                        Next
                    End If








            End Select




        Next

I can dump all the values of the "tickets" section to text file, but each ticket has an integer before another lot of data. Sample json below. Note the 279 value is for each ticket in the stream.

{
  "page": 1,
  "per_page": 25,
  "total": 1,
  "cache_id": 1115,
  "tickets": {
    "279": {
      "id": 279,
      "ref": "FLUB-xxxx-xxxx",
      "auth": "G4xxxxXX",
      "sent_to_address": "",
      "email_account_address": "",
      "creation_system": "web.api",
      "creation_system_option": "",
      "ticket_hash": "none",
      "status": "awaiting_agent",
      "hidden_status": null,
      "validating": null,
      "is_hold": false,
      "urgency": 1,
      "count_agent_replies": 0,
      "count_user_replies": 1,
      "feedback_rating": null,
      "date_feedback_rating": null,
      "date_feedback_rating_ts": 0,
      "date_feedback_rating_ts_ms": 0,
      "date_created": "2016-07-16 01:54:40",
      "date_created_ts": 1468634080,
      "date_created_ts_ms": 1468634080000,
      "date_resolved": null,
      "date_resolved_ts": 0,
      "date_resolved_ts_ms": 0,
      "date_archived": null,
      "date_archived_ts": 0,
      "date_archived_ts_ms": 0,
      "date_first_agent_assign": "2016-07-16 01:56:12",
      "date_first_agent_assign_ts": 1468634172,
      "date_first_agent_assign_ts_ms": 1468634172000,
      "date_first_agent_reply": null,
      "date_first_agent_reply_ts": 0,
      "date_first_agent_reply_ts_ms": 0,
      "date_last_agent_reply": null,
      "date_last_agent_reply_ts": 0,
      "date_last_agent_reply_ts_ms": 0,
      "date_last_user_reply": "2016-07-16 01:54:41",
      "date_last_user_reply_ts": 1468634081,
      "date_last_user_reply_ts_ms": 1468634081000,
      "date_agent_waiting": null,
      "date_agent_waiting_ts": 0,
      "date_agent_waiting_ts_ms": 0,
      "date_user_waiting": "2016-07-16 01:54:40",
      "date_user_waiting_ts": 1468634080,
      "date_user_waiting_ts_ms": 1468634080000,
      "date_status": "2016-07-16 01:54:40",
      "date_status_ts": 1468634080,
      "date_status_ts_ms": 1468634080000,
      "total_user_waiting": 0,
      "total_to_first_reply": 0,
      "date_locked": null,
      "date_locked_ts": 0,
      "date_locked_ts_ms": 0,
      "has_attachments": true,
      "subject": "test more than 1 attach",
       "original_subject": "test more than 1 attach",
       -- rest removed for clarity

I am wanting to load each ticket section into a data grid, but need to be able to get data first

the code tries to find values in each sub section, but not really used JSON before and having issues

Would love some ideas. Thanks

---update---

I have created my classes

    Public Class Example
    Public Property page As Integer
    Public Property per_page As Integer
    Public Property total As Integer
    Public Property cache_id As Integer
    Public Property tickets As Dictionary(Of Integer, tickets)
    End Class

and my tickets class. After a bit of mucking around changing values, i can get my data into the deserialzeobject but can't get examples from the Tickets Dictionary.

-----Final Code --------- that works for me

     Dim jsonsettings = New JsonSerializerSettings
    jsonsettings.NullValueHandling = NullValueHandling.Ignore
    jsonsettings.MissingMemberHandling = MissingMemberHandling.Ignore



    Try
        Dim results As Example = JsonConvert.DeserializeObject(Of Example)(jsonResponseURL, jsonsettings)

        Try

            For Each s In results.tickets.Values


                AppendtoLogFile("Subject: " & s.subject)



            Next


            'get all ticket subject lines. Loop through dictionary.



        Catch ex As Exception

            AppendtoLogFile("Error: " & ex.Message)


        End Try

Shiva · Accepted Answer · 2016-07-17 04:02:49Z

1

You should use a Dictionary( Of Int, Ticket) for your Tickets data. Take your JSON string, and use a service like jsonutils.com to generate your VB.Net classes from your JSON string. It will likely give an invalid type for the Int base Json keys (which are your individual Ticket objects) like shown below.

Public Class Tickets
    Public Property 279 As 279
End Class

Public Class Example
    Public Property page As Integer
    Public Property per_page As Integer
    Public Property total As Integer
    Public Property cache_id As Integer
    Public Property tickets As Tickets
End Class

So take this generated code, delete the Tickets class it generates, rename the 279 class name to Ticket, and finally change the Public Property tickets As Tickets to Public Property tickets As Dictionary( Of Int, Ticket )

Use something like Newtonsoft JSON.Net to deserialize your JSON into your class instances neatly.

I took your partial JSON, and generate the VB.Net classes and fixed the Ticket object as shown below. What I did Something like this should work.

Public Class Example
    Public Property page As Integer
    Public Property per_page As Integer
    Public Property total As Integer
    Public Property cache_id As Integer
    Public Property tickets As Dictionary( Of Int, Ticket )
End Class

Public Class Ticket
    Public Property id As Integer
    Public Property ref As String
    Public Property auth As String
    Public Property sent_to_address As String
    Public Property email_account_address As String
    Public Property creation_system As String
    Public Property creation_system_option As String
    Public Property ticket_hash As String
    Public Property status As String
    Public Property hidden_status As Object
    Public Property validating As Object
    Public Property is_hold As Boolean
    Public Property urgency As Integer
    Public Property count_agent_replies As Integer
    Public Property count_user_replies As Integer
    Public Property feedback_rating As Object
    Public Property date_feedback_rating As Object
    Public Property date_feedback_rating_ts As Integer
    Public Property date_feedback_rating_ts_ms As Integer
    Public Property date_created As String
    Public Property date_created_ts As Integer
    Public Property date_created_ts_ms As Long
    Public Property date_resolved As Object
    Public Property date_resolved_ts As Integer
    Public Property date_resolved_ts_ms As Integer
    Public Property date_archived As Object
    Public Property date_archived_ts As Integer
    Public Property date_archived_ts_ms As Integer
    Public Property date_first_agent_assign As String
    Public Property date_first_agent_assign_ts As Integer
    Public Property date_first_agent_assign_ts_ms As Long
    Public Property date_first_agent_reply As Object
    Public Property date_first_agent_reply_ts As Integer
    Public Property date_first_agent_reply_ts_ms As Integer
    Public Property date_last_agent_reply As Object
    Public Property date_last_agent_reply_ts As Integer
    Public Property date_last_agent_reply_ts_ms As Integer
    Public Property date_last_user_reply As String
    Public Property date_last_user_reply_ts As Integer
    Public Property date_last_user_reply_ts_ms As Long
    Public Property date_agent_waiting As Object
    Public Property date_agent_waiting_ts As Integer
    Public Property date_agent_waiting_ts_ms As Integer
    Public Property date_user_waiting As String
    Public Property date_user_waiting_ts As Integer
    Public Property date_user_waiting_ts_ms As Long
    Public Property date_status As String
    Public Property date_status_ts As Integer
    Public Property date_status_ts_ms As Long
    Public Property total_user_waiting As Integer
    Public Property total_to_first_reply As Integer
    Public Property date_locked As Object
    Public Property date_locked_ts As Integer
    Public Property date_locked_ts_ms As Integer
    Public Property has_attachments As Boolean
    Public Property subject As String
    Public Property original_subject As String
End Class

answered Jul 17, 2016 at 4:02

Shiva

21.1k15 gold badges88 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

S Wells Over a year ago

Hi, Thanks for this, it is getting me on my way, I have created my classes as shown but now having issues getting it into code. Do I use Dim result As ticket = JsonConvert.DeserializeObject(Of ticket)(json)?

Shiva Over a year ago

It should be the Parent object that has Tickets as it's property. So in the example I have given, you would so something like Dim result As Example = JsonConvert.DeserializeObject(Of Example)(json) and once you do that, you can access the Tickets as a property within result like result.tickets

S Wells Over a year ago

Hi Shiva, Thanks for your help so far. I have updated my code to show my progress. i can't loop through the data in the class... tried a few different examples but not getting far.

Shiva Over a year ago

w.r.t. your updated code, results.tickets is a dictionary with the Ticket # as an int as the key, and the Ticket object instance as the value. So you can loop through it in 1 of 2 ways. You can do a For Each with a KeyValuePair and the value of each s inside the loop with have the ticket instance, or you can do For Each on result.tickets.Values in which case s inside the loop will have the actual individual ticket, and you can get subject as s.subject. See the 2 answers in this stackoverflow.com/questions/18628917 for examples of both approaches.

S Wells Over a year ago

YAY! That works. I had to filter back on some of the attributes in my class as some ticket values had nul which was causing the deserilize issues.

Collectives™ on Stack Overflow

Parsing Data from JSON web service vb.net

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related