Solved How to parse Json received data into a parent and child tables in access (1 Viewer)

[nector]

I have a challenge to parse the Json data received from the server machine in both parent and child tables. Currently I'm only managing to parse into the parent table, the child table the issue is iterate the received Json which looks exactly below

Json Data received from the server

{
  "ESDTime": "20180101203450",
  "TerminalID": "30100000005",
  "InvoiceCode": "110001803014",
  "InvoiceNumber": "000070001",
  "FiscalCode": "11298303812903938000",
  "Operator": "Admin",
  "TaxItems": [
    {
      "TaxLabel": "A",
      "CategoryName": "Standard Rate",
      "Rate": 0.16,
      "TaxAmount": 20.98
    },
    {
      "TaxLabel": "B",
      "CategoryName": "MTV",
      "Rate": 0.12,
      "TaxAmount": 16.77
    }
  ],
  "TotalAmount": 289.12,
  "VerificationUrl": "www.nectorprime.Org"
}

Here is the VBA I used to iterate for the Parent table:

Dim lngStatus As Long
Dim strError  As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim json As Object
Dim strData   As String
Dim Details As Variant
Dim Z As Long
' Read maximum of 4896 bytes from serial port.
lngStatus = CommRead(intPortID, strData, 4896)
'Processing data from the string above
Set db = CurrentDb
Set rs = db.OpenRecordset("tblInvoiceHeaders", dbOpenDynaset)
Set json = JsonConverter.ParseJson(strData)
  
    'Process data.
    Z = 1
    For Each Details In json
        rs.AddNew
        rs![ESDTime] = Details("ESDTime")
        rs![TerminalID] = Details("TerminalID")
        rs![InvoiceCode] = Details("InvoiceCode")
        rs![InvoiceNumber] = Details("InvoiceNumber")
        rs![FiscalCode] = Details("FiscalCode")
        rs.Update
        Z = Z + 1
    Next

    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set json = Nothing
    Set Details = Nothing

The remainder json below is for Child table.

"TaxItems": [
    {
      "TaxLabel": "A",
      "CategoryName": "Standard Rate",
      "Rate": 0.16,
      "TaxAmount": 20.98
    },
    {
      "TaxLabel": "B",
      "CategoryName": "MTV",
      "Rate": 0.12,
      "TaxAmount": 16.77
    }
  ],

 

[theDBguy]

Should be the same code as for the parent table. How do they link? Autonumber field or something from json data?

 

[nector]

Should be the same code as for the parent table. How do they link? Autonumber field or something from json data?

Once the parent table receives the data it creates the Automatic PK number which should be used to link the child table as you can see from the Json code its only the header that goes into the parent table and the details which is many to one header goes into the child table. So in this the primary when it goes to the child table it becomes a foreign Key.

 

[Josef P.]

Could it be that the problem is not JSON at all, but the nested insertion of parent and child data?

dim ParentId as long

For Each Details In json
        rs.AddNew
        rs![ESDTime] = Details("ESDTime")
        rs![TerminalID] = Details("TerminalID")
        rs![InvoiceCode] = Details("InvoiceCode")
        rs![InvoiceNumber] = Details("InvoiceNumber")
        rs![FiscalCode] = Details("FiscalCode")
' if Access-BE:
'        ParentId = rs!AutoNumPkFieldName
        rs.Update

' if active DBMS (SQL-Server,...), will also work with Access-BE:
        rs.Bookmark = rs.LastModified
        ParentId = rs!AutoNumPkFieldName

        SaveTaxItems Details("TaxItems"), ParentId

        Z = Z + 1
    Next


private sub SaveTaxItems(byval TaxItems as Variant, byval ParentId as Long)
   ' insert code to save child elements (TaxItems)
end sub

/edit:
or open child recordset also in main procedure:

SaveTaxItems Details("TaxItems"), ParentId, TaxItemRecordset

private sub SaveTaxItems(byval TaxItems as Variant, byval ParentId as Long, byval TaxItemRecordset as DAO.Recordset)
   ' insert code to save child elements (TaxItems)
end sub

This avoids having to open a recordset of the child table for each main data record.

 

[nector]

Could it be that the problem is not JSON at all, but the nested insertion of parent and child data?

Very correct, that is where I have a challenge how to insert data in the child table, but if you check properly the record set with Json combines fields which are supposed to be inserted in the child table and those supposed to be inserted in the parent table.

The other issue is how to deal with nested Json and grab those required fields.

"TaxItems": [
    {
      "TaxLabel": "A",
      "CategoryName": "Standard Rate",
      "Rate": 0.16,
      "TaxAmount": 20.98
    },
    {
      "TaxLabel": "B",
      "CategoryName": "MTV",
      "Rate": 0.12,
      "TaxAmount": 16.77
    }
  ],

 

[theDBguy]

The other issue is how to deal with nested Json and grab those required fields.

The way I deal with that is I read the JSON data manually first and determine beforehand which fields are nested and what I want out of them. Then I code to that. So, if the JSON data structure changes, I have to do the manual part again. If you're looking for an automatic way to evaluate the JSON structure, you'll need a special tool for that, which is not free.

 

[ebs17]

When importing from an XML or JSON structure, it would usually be sufficient for me to produce an unnormalized overall table as a result. A tool for this can be Power Query in Excel.

The division of the data from this one table into several tables of an existing planned data model would be done via SQL => one append query per table, which is clear, relatively simple (because it is an everyday task for a database developer), and high-performance.

 

[theDBguy]

When importing from an XML or JSON structure, it would usually be sufficient for me to produce an unnormalized overall table as a result. A tool for this can be Power Query in Excel.

The division of the data from this one table into several tables of an existing planned data model would be done via SQL => one append query per table, which is clear, relatively simple (because it is an everyday task for a database developer), and high-performance.

That sounds like a good idea. Maybe I can use that. Using the OP's sample JSON data, can you provide a sample non-normalized table containing that JSON data? I am just curious how the "nested" (child) records would look like. Will there be blank columns or do you repeat the "parent" info for each child? Thanks!

 

[ebs17]

I have no experience with the JSON format and very little experience with Power Query. I'm just watching this so that I can take action in the event of a real task in this regard.

repeat the "parent" info for each child?

That should be it so that you can set the correct JOINs.

I checked: I still have Office 2010, and Power Query can't import very much there, not even from JSON. So I have to give an example in the current constellation.

 

[Josef P.]

Let's start from the beginning:
Example db from #1:

The tblInvoiceHeader is the child table of tblInvoicesDeatials?

 

[Josef P.]

I was curious and gave it a try ... result see attachment.

 

[arnelgp]

i also have mine version.

 

[nector]

Thank you so much arnelgp and Josef P for trying to help , may I have missed the point, in both cases even if I run the code I see nothing in the tables

 

[arnelgp]

this is the Original json you posted different from the "last one".
try to run your form.

 

[nector]

Thank you so much arnelgp you have sorted it out.

Regards

Chris