Failing to use loaded JSON

  1. 4 days ago

    Louis D

    Jun 29 Testers, Xojo Pro QC, Canada

    Hello all,

    I am trying to use JSON for the first time, and I am experiencing some difficulty. This is a web application, using Xojo 2019 R3.1 on Win 10 . I need to validate addresses. After some research, I settled on Melissa.net. So far, I can connect to the service, send my partial address and obtain a JSON string that I output to a file. I can open that file and load it in a JSONItem (the variable shows the string in debug). It all goes bad when I try to use the data. OK, time to show data and some code. The returned SON string looks something like this (I doctored the values because it was a real address...)

    {"Version":"3.0.1.151","TransmissionReference":"993416484223","TransmissionResults":"","TotalRecords":"1","Records":[{"RecordID":"1","Results":"AC01,AC03,AV24,GS05","FormattedAddress":"61142 186e Av;Montréal QC  H9X 3S7","Organization":"","AddressLine1":"61142 186e Av","AddressLine2":"Montréal QC H9X 3S7","AddressLine3":"","AddressLine4":"","AddressLine5":"","AddressLine6":"","AddressLine7":"","AddressLine8":"","SubPremises:"","DoubleDependentLocality":"","DependentLocality":"","Locality":"Montréal","SubAdministrativeArea":"","AdministrativeArea":"QC","PostalCode":"H9X 3S7","AddressType":"1","AddressKey":"H9X3S7","SubNationalArea":"","CountryName":"Canada","CountryISO3166_1_Alpha2":"CA","CountryISO3166_1_Alpha3":"CAN","CountryISO3166_1_Numeric":"124","CountrySubdivisionCode":"CA-QC","Thoroughfare":"Av 186E","ThoroughfarePreDirection":"","ThoroughfareLeadingType":"Av","ThoroughfareName":"186E","ThoroughfareTrailingType":"","ThoroughfarePostDirection":"","DependentThoroughfare":"","DependentThoroughfarePreDirection":"","DependentThoroughfareLeadingType":"","DependentThoroughfareName":"","DependentThoroughfareTrailingType":"","DependentThoroughfarePostDirection":"","Building":"","PremisesType":"","PremisesNumber":"61142","SubPremisesType":"","SubPremisesNumber":"","PostBox":"","Latitude":"55.556938","Longitude":"-77.579201"}]}

    My code is this (G1 is a JSONItem):

    G1.Load(tempStr)
    
    'maintenant, on utilise les valeurs récupérées
    Dim A1 As Variant 
    Dim A2 As Variant
    Dim A2A As Variant 
    Dim A2B As Variant
    Dim A3 As Variant 
    Dim A4 As Variant 
    Dim A5 As Variant 
    Dim A6 As Variant 
    
    A1 = G1.lookup("PremisesNumber", "Erreur")
    A2B = G1.lookup("ThoroughfareLeadingType", "Erreur")
    A2A = G1.lookup("ThoroughfareName", "Erreur")
    A3 = G1.lookup("FormattedAddress", "Erreur")
    A5 = G1.lookup("Locality", "Erreur")
    A6 = G1.lookup("PostalCode", "Erreur")

    The A variables only get the error default value ("Erreur"), not the value contained in the JSON string for the key. What am I missing?

    I noticed that my JSON string appears to be an array of just one record. Does that make any difference in the context?

    Any help will be welcome!

    Answered by J A

    See post in context

    That's because G1 is the top-level dictionary, which doesn’t have those keys. You need to drill down to the record itself. That appears to mean getting the value for key “Records”, which is an array; then take its first (only) element, which is the record that has the keys you are looking for.

  2. J A

    Jun 29 Testers, Xojo Pro Answer

    That's because G1 is the top-level dictionary, which doesn’t have those keys. You need to drill down to the record itself. That appears to mean getting the value for key “Records”, which is an array; then take its first (only) element, which is the record that has the keys you are looking for.

  3. Kem T

    Jun 29 Testers, Xojo Pro, XDC Speakers, MVP Connecticut

    This make it a little easier to visualize what @J ALipscomb has described:

    {
      "Version": "3.0.1.151",
      "TransmissionReference": "993416484223",
      "TransmissionResults": "",
      "TotalRecords": "1",
      "Records": [
        {
          "RecordID": "1",
          "Results": "AC01,AC03,AV24,GS05",
          "FormattedAddress": "61142 186e Av;Montréal QC  H9X 3S7",
          "Organization": "",
          "AddressLine1": "61142 186e Av",
          "AddressLine2": "Montréal QC H9X 3S7",
          "AddressLine3": "",
          "AddressLine4": "",
          "AddressLine5": "",
          "AddressLine6": "",
          "AddressLine7": "",
          "AddressLine8": "",
          "SubPremises": "",
          "DoubleDependentLocality": "",
          "DependentLocality": "",
          "Locality": "Montréal",
          "SubAdministrativeArea": "",
          "AdministrativeArea": "QC",
          "PostalCode": "H9X 3S7",
          "AddressType": "1",
          "AddressKey": "H9X3S7",
          "SubNationalArea": "",
          "CountryName": "Canada",
          "CountryISO3166_1_Alpha2": "CA",
          "CountryISO3166_1_Alpha3": "CAN",
          "CountryISO3166_1_Numeric": "124",
          "CountrySubdivisionCode": "CA-QC",
          "Thoroughfare": "Av 186E",
          "ThoroughfarePreDirection": "",
          "ThoroughfareLeadingType": "Av",
          "ThoroughfareName": "186E",
          "ThoroughfareTrailingType": "",
          "ThoroughfarePostDirection": "",
          "DependentThoroughfare": "",
          "DependentThoroughfarePreDirection": "",
          "DependentThoroughfareLeadingType": "",
          "DependentThoroughfareName": "",
          "DependentThoroughfareTrailingType": "",
          "DependentThoroughfarePostDirection": "",
          "Building": "",
          "PremisesType": "",
          "PremisesNumber": "61142",
          "SubPremisesType": "",
          "SubPremisesNumber": "",
          "PostBox": "",
          "Latitude": "55.556938",
          "Longitude": "-77.579201"
        }
      ]
    }
  4. Kem T

    Jun 29 Testers, Xojo Pro, XDC Speakers, MVP Connecticut

    BTW, I had to fix that JSON near "Subpremises" (missing close quote) to make it valid. I assume that was some copy and paste error.

  5. Louis D

    Jun 29 Testers, Xojo Pro QC, Canada

    Thank you both! I would have marked both responses as answers if the forum allowed! I don't know if I used the best way forward, but my approach, following your solution, was to create two additional JSON variables (G2 and G3) and fill them as follows:

    G1.Load(tempStr)
    G2 = G1.lookup("Records", "Erreur")
    G3 = G2.ValueAt(0)

    I can lookup my address data in G3.

    Gentlemen, your help made my day! Thank you again!

  6. Kem T

    Jun 29 Testers, Xojo Pro, XDC Speakers, MVP Connecticut

    That approach is a fine one. (Except I do recommend more descriptive variable names.)

  7. Louis D

    Jun 29 Testers, Xojo Pro QC, Canada

    Indeed. My variables and control names are usually self-descriptive. Except when I do experimental stuff. TransmissionReference, Self.lblAddressPreview.Text etc.

or Sign Up to reply!