Invalid JSON response - Githubissues

cacapis commented 2 years ago

Hello!

I'm getting the error "Invalid JSON response" when I try to parse the JSON at the end. Could it be related to some of the fields having HTML code inside? IE (Parsed with jsonlint.com): "headword_full": "Einheit <-, -en><\/span> ['a?nha?t]<\/span> <acronym title=\"noun\">N<\/acronym><\/span> <acronym title=\"feminine\">f<\/acronym><\/span>",

Thank you!

[{"lang":"de","hits":[{"type":"entry","opendict":false,"roms":[{"headword":"Einheit","headword_full":"Einheit <-, -en><\/span> ['a?nha?t]<\/span> <acronym title=\"noun\">N<\/acronym><\/span> <acronym title=\"feminine\">f<\/acronym><\/span>","wordclass":"noun","arabs":[{"header":"1. Einheit <acronym title=\"auch\">a.<\/acronym> <acronym title=\"military\">MIL<\/acronym>, <acronym title=\"politics\">POL<\/acronym>, <acronym title=\"law\">LAW<\/acronym><\/span>:","translations":[{"source":"Einheit<\/strong>","target":"unidad <acronym title=\"feminine\">f<\/acronym><\/span>"},{"source":"Einheit<\/strong> der Rechtsordnung<\/span>","target":"unidad del ordenamiento jurídico"},{"source":"motorische Einheit<\/strong><\/span> <acronym title=\"medicine\">MED<\/acronym><\/span>","target":"unidad motora"},{"source":"untrennbare rechtliche Einheit<\/strong><\/span>","target":"unidad jurídica indivisible"},{"source":"die deutsche Einheit<\/strong><\/span>","target":"la unidad alemana"}]},{"header":"2. Einheit <acronym title=\"telecommunications\">TELEC<\/acronym><\/span>:","translations":[{"source":"Einheit<\/strong>","target":"paso <acronym title=\"masculine\">m<\/acronym><\/span> de contador"}]}]}]},{"type":"entry","opendict":false,"roms":[{"headword":"Ausgabe-Einheit","headword_full":"Ausgabe-Einheit <-, -en><\/span> <acronym title=\"noun\">N<\/acronym><\/span> <acronym title=\"feminine\">f<\/acronym><\/span> <acronym title=\"computing\">COMPUT<\/acronym><\/span>","wordclass":"noun","arabs":[{"header":"","translations":[{"source":"Ausgabe-Einheit<\/strong>","target":"unidad <acronym title=\"feminine\">f<\/acronym><\/span> de salida"}]}]}]},{"type":"entry","opendict":false,"roms":[{"headword":"Eingabe-Einheit","headword_full":"Eingabe-Einheit <-, -en><\/span> <acronym title=\"noun\">N<\/acronym><\/span> <acronym title=\"feminine\">f<\/acronym><\/span> <acronym title=\"computing\">COMPUT<\/acronym><\/span>","wordclass":"noun","arabs":[{"header":"","translations":[{"source":"Eingabe-Einheit<\/strong>","target":"unidad <acronym title=\"feminine\">f<\/acronym><\/span> de entrada"}]}]}]},{"type":"entry","opendict":false,"roms":[{"headword":"Hardware-Einheit","headword_full":"Hardware-Einheit <-, -en><\/span> <acronym title=\"noun\">N<\/acronym><\/span> <acronym title=\"feminine\">f<\/acronym><\/span> <acronym title=\"computing\">COMPUT<\/acronym><\/span>","wordclass":"noun","arabs":[{"header":"","translations":[{"source":"Hardware-Einheit<\/strong>","target":"unidad <acronym title=\"feminine\">f<\/acronym><\/span> de hardware"}]}]}]},{"type":"entry","opendict":false,"roms":[{"headword":"SI-Einheit","headword_full":"SI-Einheit <-, -en><\/span> <acronym title=\"noun\">N<\/acronym><\/span> <acronym title=\"feminine\">f<\/acronym><\/span>","wordclass":"noun","arabs":[{"header":"","translations":[{"source":"SI-Einheit<\/strong>","target":"unidad <acronym title=\"feminine\">f<\/acronym><\/span> SI"}]}]}]}]}]

omegastripes commented 2 years ago

@cacapis please share the code.

cacapis commented 2 years ago

Here is the code, I'm using JSON.bas without modifications. The debug Debug.Print sJSONString output is the JSON data I included in the first message. Apparently, the variable sState is equal to "array", so it triggers the line Case sState <> "Object"

Thank you.

` Sub Test()

Dim sJSONString As String
Dim vJSON
Dim sState As String
Dim vFlat

' Retrieve JSON response
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "https://api.pons.com/v1/dictionary?l=dees&q=Einheit&in=de", True
    .setRequestHeader "X-Secret", "xxxxxxxx"
    .Send
    Do Until .ReadyState = 4: DoEvents: Loop
    sJSONString = .ResponseText
End With
Debug.Print sJSONString

' Parse JSON response
JSON.Parse sJSONString, vJSON, sState
' Check response validity
Select Case True
    Case sState <> "Object"
        MsgBox "Invalid JSON response"
    Case Not vJSON.Exists("rows")
        MsgBox "JSON contains no rows"
    Case Else
        ' Convert JSON nested rows array to 2D Array and output to worksheet #1
        ThisWorkbook.Sheets(2).Cells.Clear
        Output ThisWorkbook.Sheets(2), vJSON("rows")
        ' Flatten JSON
        JSON.Flatten vJSON, vFlat
        ' Convert to 2D Array and output to worksheet #2
        ThisWorkbook.Sheets(3).Cells.Clear
        Output ThisWorkbook.Sheets(3), vFlat
        MsgBox "Completed"
End Select

End Sub `

omegastripes commented 2 years ago

Remove all lines after JSON.Parse sJSONString, vJSON, sState (which are intended to render the response from trirand.com and used as the example, thus they won't work for api.pons.com) and write your own code depending on your needs and response structure. You have to inspect the structure of JSON received from api.pons.com, plan how to retrieve the necessary data, and decide what should be done with JSON entity returned in vJSON variable after parsing.

cacapis commented 2 years ago

Ah perfect, thank you!

omegastripes / VBA-JSON-parser

Invalid JSON response #27