Help with Json creation code adding new COLLECTION

[Ira]

Running Latest version of MS Access 365 on Windows 11 Pro.

Need a little help finishing the code to generate a Json file to send to server.

The following code:

Dim addressesToValidate As Object
Set addressesToValidate = CreateObject("Scripting.Dictionary")

Dim FedExAddress As Object
Set FedExAddress = CreateObject("Scripting.Dictionary")

Dim streetLines As New Collection
streetLines.Add "7372 PARKRIDGE BLVD"
streetLines.Add "APT 286" 'streetLine
Set FedExAddress("streetLines") = streetLines

FedExAddress("city") = "CityText"
FedExAddress("stateOrProvinceCode") = "stateOrProvinceCodeText"
FedExAddress("postalCode") = "postalCodeText"
FedExAddress("countryCode") = "countryCodeText"
Set addressesToValidate("addressesToValidate") = FedExAddress

'debugging, write out json to a file
Dim postData As String
postData = JsonConverter.ConvertToJson(addressesToValidate)
SaveResult postData, "FedExAddressDetailsRestAPI.txt"
'debugging


Which yields this:

{
"addressesToValidate": {
"streetLines": [
"7372 PARKRIDGE BLVD",
"APT 286"


],
"city": "CityText",
"stateOrProvinceCode": "stateOrProvinceCodeText",
"postalCode": "postalCodeText",
"countryCode": "countryCodeText"
}
}


But, I need this:


{
"addressesToValidate": [
{
"address": {
"streetLines": [
"7372 PARKRIDGE BLVD",
"APT 286"
],
"city": "IRVING",
"stateOrProvinceCode": "TX",
"postalCode": "75063-8659",
"countryCode": "US"
}
}
]
}

I can't figure out how to add the second COLLECTION after addressesToValidate. It's kicking my butt. Can anyone point me in the right direction?

Thank you, Ira

[Edgar]

Sub formatJSON()
    
    ' the object you will send
    Dim json As Object
    Set json = CreateObject("scripting.dictionary")
    
    ' array of "address" containers
    Dim addressesArr As Collection
    Set addressesArr = New Collection
    
    ' the "address" container object that holds an "address" object
    Dim addressContainerObj As Object
    Set addressContainerObj = CreateObject("scripting.dictionary")
    
    ' the "address" object
    Dim addressObj As Object
    Set addressObj = CreateObject("scripting.dictionary")
    
    ' the "streenLines" property is an array of strings
    Dim streetLines As Collection
    Set streetLines = New Collection
    
    streetLines.Add "7372 PARKRIDGE BLVD"
    streetLines.Add "APT 286"
    
    addressObj.Add "streetLines", streetLines
    addressObj.Add "city", "IRVING"
    addressObj.Add "stateOrProvinceCode", "TX"
    addressObj.Add "postalCode", "75063-8659"
    addressObj.Add "countryCode", "US"
    
    addressContainerObj.Add "address", addressObj
    
    addressesArr.Add addressContainerObj
    
    json.Add "addressesToValidate", addressesArr
    
    Debug.Print JsonConverter.ConvertToJson(json, 2)
End Sub

[Ira]

OMG ! Works great !! Thank you so much!

[Ira]

Hi Edgar,

Hoping you can help me with one more. BTW: do you where I can go to learn more about this topic.
I am looking for this...
{
"masterTrackingNumberInfo": {
"trackingNumberInfo": {
"trackingNumber": "123456789012"
}
},
"associatedType": "STANDARD_MPS"
}

but I am generating this....
{
"masterTrackingNumberInfo": {
"TrackingNumberInfo": {
"trackingNumber": "123456789012"
},
"associatedType": "STANDARD_MPS"
}
}

This is my code:
Dim json As Object
Set json = CreateObject("scripting.dictionary")

' array of "address" containers
Dim masterTrackingNumberInfoObj As Object
Set masterTrackingNumberInfoObj = CreateObject("scripting.dictionary")


' the "TrackingNumberInfoObj" container object that holds the "trackingNumber" element
Dim TrackingNumberInfoObj As Object
Set TrackingNumberInfoObj = CreateObject("scripting.dictionary")
TrackingNumberInfoObj.Add "trackingNumber", "123456789012"
Set masterTrackingNumberInfoObj("TrackingNumberInfo") = TrackingNumberInfoObj


Set json("masterTrackingNumberInfo") = masterTrackingNumberInfoObj
masterTrackingNumberInfoObj.Add "associatedType", "STANDARD_MPS"

Dim postData As String
postData = JsonConverter.ConvertToJson(json)

Thank you, Ira

[Edgar]

The json you want to send should have two properties: masterTrackingNumberInfoObj and associatedType. But you're embedding associatedType in masterTrackingNumberInfoObj instead of embedding it to json, the fix is simple:

    Dim json As Object
    Set json = CreateObject("scripting.dictionary")
    
    ' array of "address" containers
    Dim masterTrackingNumberInfoObj As Object
    Set masterTrackingNumberInfoObj = CreateObject("scripting.dictionary")
    
    
    ' the "TrackingNumberInfoObj" container object that holds the "trackingNumber" element
    Dim TrackingNumberInfoObj As Object
    Set TrackingNumberInfoObj = CreateObject("scripting.dictionary")
    TrackingNumberInfoObj.Add "trackingNumber", "123456789012"
    Set masterTrackingNumberInfoObj("TrackingNumberInfo") = TrackingNumberInfoObj
    
    
    Set json("masterTrackingNumberInfo") = masterTrackingNumberInfoObj
'    masterTrackingNumberInfoObj.Add "associatedType", "STANDARD_MPS" <--- here's the problem
    json.Add "associatedType", "STANDARD_MPS"
    
    Dim postData As String
    postData = JsonConverter.ConvertToJson(json)

As for how to analyze these things, I suggest you to format your json. In JSON, values can be strings, numbers, booleans, objects or collections. Therefore, if you see [], that's a collection as value. If you see {}, that's an object as value, if you don't see either, then it's just a value. In the case of your example, you can break it down like this:

{                                           <--- object 0 json base object
  "masterTrackingNumberInfo": {             <--- object 1 masterTrackingNumberInfoObj object as value for object 0
    "trackingNumberInfo": {                 <--- object 2 trackingNumberInfoObj object as value for object 1
      "trackingNumber": "123456789012"      <--- string as value for object 2
    }                                       <--- /object 2
  },                                        <--- /object 1
  "associatedType": "STANDARD_MPS"          <--- string value for object 0
}                                           <--- /object 0

Basically, define your objects first, then, when you see a value that's neither an object or collection, use your defined objects and assign the values as necessary.

[Ira]

Hi Edgar,

Hoping you can help me with another one. I have been doing these and getting better, but this one is kicking my ass..
I am looking for this...
{
"trackDocumentDetail": {
"documentType": "SIGNATURE_PROOF_OF_DELIVERY",
"documentFormat": "PNG"
},
"trackDocumentSpecification": [
{
"trackingNumberInfo": {
"trackingNumber": "128XXXXX3726",
"carrierCode": "FDXE"
},
"shipDateBegin": "2020-03-29",
"shipDateEnd": "2020-04-01",
"accountNumber": "XXXXX3"
}
]
}
but i am getting this...
{
"trackDocumentDetail": {
"documentType": "SIGNATURE_PROOF_OF_DELIVERY",
"documentFormat": "PDF"
},
"trackDocumentSpecification": [
{
"trackingNumberInfo": {
"trackingNumber": "2787XXXXX",
"carrierCode": "FDXE"
} <---- extra
},
{ <--- extra
"shipDateBegin": "2024-08-21",
"shipDateEnd": "2024-11-24",
"accountNumber": "8XXXXXXX1"
}
]
}
my code:
Dim json As Object
Set json = CreateObject("scripting.dictionary")

' array of "trackDocumentSpecifications" containers and 3 additional values
Dim trackDocumentSpecificationArr As Collection
Set trackDocumentSpecificationArr = New Collection

Dim trackDocumentSpecificationObj As Object
Set trackDocumentSpecificationObj = CreateObject("scripting.dictionary")


Dim trackDocumentDetailObj As Object
Set trackDocumentDetailObj = CreateObject("scripting.dictionary")

Dim trackingNumberInfoObj As Object
Set trackingNumberInfoObj = CreateObject("scripting.dictionary")

trackDocumentDetailObj.Add "documentType", "SIGNATURE_PROOF_OF_DELIVERY"
trackDocumentDetailObj.Add "documentFormat", "PDF"
json.Add "trackDocumentDetail", trackDocumentDetailObj


trackingNumberInfoObj.Add "trackingNumber", TrackingNumber
trackingNumberInfoObj.Add "carrierCode", "FDXE"
Set trackDocumentSpecificationObj("trackingNumberInfo" ) = trackingNumberInfoObj

trackDocumentSpecificationArr.Add trackDocumentSpecificationObj

' the "AdditionalInfoObj" object hold 3 additional values for then trackDocumentSpecificationArr array
Dim AdditionalInfoObj As Object
Set AdditionalInfoObj = CreateObject("scripting.dictionary")

AdditionalInfoObj.Add "shipDateBegin", "2024-08-24"
AdditionalInfoObj.Add "shipDateEnd", "2024-08-22"
AdditionalInfoObj.Add "accountNumber", "8XXXXXX"

trackDocumentSpecificationArr.Add AdditionalInfoObj 'add the 3 values to the array

json.Add "trackDocumentSpecification", trackDocumentSpecificationArr

Dim postData As String
postData = JsonConverter.ConvertToJson(json)

Thank you SO MUCH again...