S Wells S Wells - 4 months ago 129
Vb.net Question

Parsing Data from JSON web service vb.net

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

Answer

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