After several days of working through this problem using various different methodologies, I've finally realized that I may have bitten off more than I can chew. I've got a JSON extract that is structured as a collection of dictionaries. It's a fairly complex structure, but each dictionary is consistent throughout the JSON. What I'm attempting to do is recursively walk through the JSON, building and linking tables. I've come close, but I've got some issues with my recursion. For simplicity, all fields are strings.
The JSON is structured like this in a collection of dictionaries:
Dictionary 1.0
key/value pair 1a
key/value pair 1b
key/value pair 1c
subDictionary 1.1
key/value pair 1.1a
key/value pair 1.1b
key/value pair 1.1c
subDictionary 1.2
key/value pair 1.2a
key/value pair 1.2b
key/value pair 1.2c
Dictionary 2.0
key/value pair 2a
key/value pair 2b
key/value pair 2c
subDictionary 2.1
key/value pair 2.1a
key/value pair 2.1b
key/value pair 2.1c
subDictionary 2.2
key/value pair 2.2a
key/value pair 2.2b
key/value pair 2.2c
What I want to end up with is a parent/child relational database with two (or potentially more) tables linked by ID:
Parent Table
ID Field 1a Field 1b Field 1c
1.0 Value 1a Value 1b Value 1c
2.0 Value 2a Value 2b Value 2c
Sub Table
parentID ID key1.1a key1.1b key1.1c
1.0 1.1 Value 1.1a Value 1.1b Value 1.1c
1.0 1.2 Value 1.2a Value 1.2b Value 1.2c
2.0 2.1 Value 2.1a Value 2.1b Value 2.1c
2.0 2.2 Value 2.2a Value 2.2b Value 2.2c
My code so far can do the recursion, but I also need it to build the SQL statements to define the tables and populate the data. I know it is a huge ask, but I think that a lot of people would find value in this code for importing JSON into MS Access relational tables. I'm currently loading the data into a 2D array but my next step is to build multiple 2D arrays (one for each table) or simply move straight into building the SQL statements to build a new table and link it to a data element, and forget about building the array altogether.
From my main program I obtain the JSON string and parse it:
sub Main
Dim strResult As Variant
Set strResult = JsonConverter.ParseJson([add your json string here])
ReDim BuildArray(strResult.Count, 1) As String
ColNum = 0
RowNum = 1 'Row 0 is used to hold the field names
Call TraverseDictionary(strResult, RowNum, ColNum, BuildArray)
end sub
Private Sub TraverseDictionary(myDictionary As Variant, RowNum As Integer, ColNum As Integer, BuildArray As Variant)
Dim key As Variant
Dim myObject As Variant
Dim myElement As String
Select Case TypeName(myDictionary)
Case "Dictionary"
For Each key In myDictionary.Keys
If TypeName(key) <> "Dictionary" And TypeName(key) <> "Collection" Then
BuildArray(0, ColNum) = Trim(key)
ColNum = ColNum + 1
If ColNum > UBound(BuildArray, 2) Then
ReDim Preserve BuildArray(UBound(BuildArray, 1), ColNum)
End If
Else
Debug.Print "Table = ", myDictionary.Keys(1)
ArrFieldType.Add TypeName(key) & " - Case Dictionary"
End If
TraverseDictionary myDictionary(key), RowNum, ColNum, BuildArray
Next key
Case "Collection"
For Each key In myDictionary
If TypeName(key) <> "Dictionary" And TypeName(key) <> "Collection" Then
BuildArray(0, ColNum) = Trim(key)
ColNum = ColNum + 1
If ColNum > UBound(BuildArray, 2) Then
ReDim Preserve BuildArray(UBound(BuildArray, 1), ColNum)
End If
Else
myElement = CStr(key(1))
ArrFieldType.Add TypeName(key) & " - Case Collection"
End If
TraverseDictionary key, RowNum, ColNum, BuildArray, ArrFieldType
Next
If ColNum > 0 Then
RowNum = RowNum + 1
ColNum = 0
End If
Case Else
If IsNull(myDictionary) Then
myElement = ""
Else
myElement = CStr(myDictionary)
End If
BuildArray(RowNum, ColNum) = myElement
End Select
End Sub
Anyone care to offer some guidance? Thanks in advance. Once I get the recursion working, I'd like to implement the methodology described in
https://stackoverflow.com/questions/62437764/importing-json-file-to-ms-access-table
to create the SQL statements which will build and populate the tables, and create the indices.
The result will be a small function that will take on just about any piece of JSON and convert it into MS Access linked tables.
