I am trying to write a backup program for a bunch of data in our CRM, because its native version lacks quite a bit of information we want secured. I'm needing to use vb.net (.Net Framework 4.8.1), and my end goal is mapping to a SQL server with a host of tables once I figure out exactly what values we're backing up. The call is an HTTP GET, and the response is in nested JSON. Beyond that I want to back up as much as I can, and I don't really care what methods/nuget packages (as long as reputable)/whatever I have to use to get there. I've never dealt with JSON before and I'm frankly in over my head.
The data is extremely variable and huge- there could be as many as 300 main properties, most of which keep version history, but not all. Blank values don't appear to be returned, so the response is a bit unpredictable in structure.
Basic structure heavily anonymized:
{
"vid": 12345,
"canonical-vid": 12345,
"merged-vids": [],
"portal-id": 12345678,
"is-contact": true,
"properties": {
"prop1": {
"value": "12345678",
"versions": [
{
"value": "12345678",
"source-type": "CALCULATED",
"source-id": "source1",
"source-label": null,
"updated-by-user-id": null,
"timestamp": 1717375078168,
"selected": false,
"data-sensitivity": null,
"is-encrypted": null
},
{
"value": "12345678;12345679",
"source-type": "CALCULATED",
"source-id": "source1",
"source-label": null,
"updated-by-user-id": null,
"timestamp": 1711381559120,
"selected": false,
"data-sensitivity": null,
"is-encrypted": null
},
{
"value": "12345679",
"source-type": "CALCULATED",
"source-id": "source1",
"source-label": null,
"updated-by-user-id": null,
"timestamp": 1711037348472,
"selected": false,
"data-sensitivity": null,
"is-encrypted": null
},
{
"value": "12345679;12345680",
"source-type": "CALCULATED",
"source-id": "source1",
"source-label": null,
"updated-by-user-id": null,
"timestamp": 1711027558111,
"selected": false,
"data-sensitivity": null,
"is-encrypted": null
},
{
"value": "12345681",
"source-type": "CALCULATED",
"source-id": "source1",
"source-label": null,
"updated-by-user-id": null,
"timestamp": 1710899316341,
"selected": false,
"data-sensitivity": null,
"is-encrypted": null
}
]
},
"prop2": {
"value": "https://myurl.com/contact/",
"versions": [
{
"value": "https://myurl.com/contact/",
"source-type": "ANALYTICS",
"source-id": "source2",
"source-label": null,
"updated-by-user-id": null,
"timestamp": 1685656187542,
"selected": false,
"data-sensitivity": null,
"is-encrypted": null
},
{
"value": "https://myurl.com/page 2/",
"source-type": "ANALYTICS",
"source-id": "source2",
"source-label": null,
"updated-by-user-id": null,
"timestamp": 1685656121699,
"selected": false,
"data-sensitivity": null,
"is-encrypted": null
}
]
},
'...continue like this for a variable amount of properties with a variable amount of nested versions each, though all the versions sections have the same properties, then finish off with the below...
},
"form-submissions": [
{
"conversion-id": "alphanumeric_string",
"timestamp": 1685656111147,
"form-id": "alphanumeric_string",
"portal-id": 12345678,
"page-url": "https://myurl.com/form page/",
"page-title": "My Page Title",
"title": "#gform_2 .form",
"form-type": "CAPTURED",
"meta-data": []
}
],
"list-memberships": [],
"identity-profiles": [
{
"vid": 12345,
"saved-at-timestamp": 1685656112544,
"deleted-changed-timestamp": 0,
"identities": [
{
"type": "EMAIL",
"value": "email@domain.com",
"timestamp": 1685656111147,
"is-primary": true
},
{
"type": "LEAD_GUID",
"value": "alphanumeric_string",
"timestamp": 1685656112534
}
]
}
],
"merge-audits": []
}
I'm thinking the version history sections are regular enough I can handle those by mapping to a SQL table with fields:
ID int identity no null,
ObjType varchar(32) no null,
ObjID int no null,
Prop varchar(128) no null,
value varchar no null,
sourceType varchar no null,
sourceId varchar null,
sourceLabel varchar null,
updatedByUserID varchar null,
timestamp varchar no null,
selected boolean no null,
dataSensitivity varchar null,
isEncrypted boolean null
I want to get to the point where I can say something like
Dim Fields as string = ""
Dim Values as string = ""
Dim VerHistory as List(Of VerHistoryCustomClass)
For Each prop as JsonMember in JsonObject
Fields &= prop.Name & ","
Values &= prop.Value & ","
If (some test for presence of version history) Then
dim hist as new VerHistoryCustomClass(variables...)
VerHistory.Add(hist)
End If
End For
SQLComm.CommandText = "INSERT INTO objTable (" & Fields & ")
VALUES (" & Values & ")"
SQLcon.Open()
SQLcomm.ExecuteNonQuery()
For Each ver as VerHistoryCustomClass in VerHistory
SQLcomm.CommandText = "INSERT INTO verTable VALUES (" & ver.toCommaDelimitedString & ")"
SQLcomm.ExecuteNonQuery()
End For
SQLcon.Close()
or its similar update version if the ID was already there.
I'm just really struggling getting from the returned JSON to something I can start assigning to variables. I also don't know how to necessarily deal with the last few properties that break the pattern and have nested structures of their own.
Here's some of my trying to get the JSON back and dealing with it:
IMPORTS RestSharp
Public Class Test_Area
Private ReadOnly HttpClientTest As New HttpClient
Friend ReadOnly EndptContactsAllData As String = "https://apiurl.com/endpt"
Private Async Sub btnTestGetSpecificContact_Click(sender As Object, e As EventArgs) Handles btnTestGetSpecificContact.Click
'Dim testclient As HttpClient = GetCrmClient() 'adds headers if not there
'Dim email As String = "person@domain.com"
'Dim fullUri = EndptContactsAllData & "/contact/email/" & email & "/profile"
'Dim response As HttpResponseMessage = Await testclient.GetAsync(fullUri)
'If response.IsSuccessStatusCode Then
' Dim pause = True
' Dim raw = Await response.Content.ReadAsStringAsync()
' 'Using jDoc As JsonDocument = JsonDocument.Parse(raw)
' ' Dim JRtElement As JsonElement = jDoc.RootElement
' ' pause = True
' 'End Using
'Else
'End If
Dim email As String = "person@domain.com"
Dim fullUri = EndptContactsAllData & "/contact/email/" & email & "/profile"
Dim testclient As New RestClient(fullUri)
Dim testReq As New RestRequest(Method.GET)
testReq.AddHeaders(GetCrmClientHeaders) 'GetCrmClientHeaders returns dictionary(Of string, string) for header values
Dim testResponse As IRestResponse = Await testclient.ExecuteAsync(testReq)
If testResponse.IsSuccessful Then
'handle returned JSON
Else
'handle error codes
End If
End Sub
Public Function GetCrmClient() As HttpClient
If HttpIsInitialized = False Then
HttpClientTest.DefaultRequestHeaders.Add("accept", "application/json")
HttpClientTest.DefaultRequestHeaders.Add("authorization", "Bearer " & AccessToken)
End If
Return HttpClientTest
End Function
Public Function GetCrmClientHeaders() As Dictionary(Of String, String)
Dim hdrDict As New Dictionary(Of String, String)
hdrDict.Add("accept", "application/json")
hdrDict.Add("authorization", "Bearer " & AccessToken)
Return hdrDict
End Function
END Class
I know this is a lot, I can use any suggestions of where to start that you can give.
Edit: Added .Net version (.Net Framework 4.8.1)
You should be able to convert all your JSON into objects using the JsonSerializer class. First you create classes that represent your JSON. Here are some partial classes I created to handle the JSON you posted in your question:
Public Class MainClass
<JsonPropertyName("vid")>
Public Property Vid As Integer?
<JsonPropertyName("canonical-vid")>
Public Property CanonicalVid As Integer?
<JsonPropertyName("merged-vids")>
Public Property MergedVids As List(Of Object)
<JsonPropertyName("is-contact")>
Public Property IsContact As Boolean?
<JsonPropertyName("properties")>
Public Property Properties As Dictionary(Of String, PropertyClass)
End Class
Public Class PropertyClass
<JsonPropertyName("value")>
Public Property Value As String
<JsonPropertyName("versions")>
Public Property Versions As List(Of VersionClass)
End Class
Public Class VersionClass
<JsonPropertyName("value")>
Public Property Value As String
<JsonPropertyName("source-type")>
Public Property SourceType As String
<JsonPropertyName("source-id")>
Public Property SourceId As String
<JsonPropertyName("timestamp")>
Public Property TimeStamp As Long?
End Class
These are not complete, you will want to add the remaining properties to each class, but they should get you on your way.
Once your classes are defined, you can use the deserializer to convert your JSON into a MainClass object:
Dim json As String
Using reader As New StreamReader("c:\temp\new 1.json")
json = reader.ReadToEnd
End Using
Dim main As MainClass = JsonSerializer.Deserialize(Of MainClass)(json)
You can then access all the data through the main object's properties.