JSON-Result beginning with "[" throws an error

[Johannes]

Hey Bruce,

fist of all: thanks for all your work on this! It's obvious that you invested a lot of time into those libraries and in addition to that you're hanging out here giving support to us.
Much appreciated!

Now on to my case ;)
I'm trying to work with the JSON results of a services' API I regularly work with (Pinboard bookmarking service, www.pinboard.in).
A typical result to a query (show me all bookmarks with tag x) looks like this:

[{"href":"http:\/\/en.wikipedia.org\/wiki\/Exploratory_Data_Analysis","description":"Exploratory data analysis - Wikipedia, the free Encyclopedia","extended":"","meta":"576e8bbed7ed3646da28523cc4cf0f73","hash":"8114ff56779cbce0c8cdeaedd21edab2","time":"2011-08-12T02:07:00Z","shared":"no","toread":"no","tags":"da-extended DA-source"}]

There's just one result because I only have one bookmark with this tag, otherwise there would be more elements after the last "}".
My problem is that I thinkt I got the query right (tried with generalQuery and an entry in the module restLibrary and the direct way via restQuery) but I always run into the same error:

First msgbox:
---------------------------
got [ expected --({)-- Bad JSON at character 1 starting at [{"href":"http:\/\/en.wikipedia.org\/wiki\/Exploratory_Data_Analysis","description":"Exploratory data analysis - Wikipedia, the free encyclopedia","extended":"","meta":"576e8bbed7ed3646da28523cc4cf0f73","hash":"8114ff56779cbce0c8cdeaedd21edab2","time":"2011-08-12T02:07:00Z","shared":"no","toread":"no","tags":"da-extended DA-source"}]
---------------------------

Second msgbox:
---------------------------
Could find no data for query da-extended-  {
"_deserialization":""
 }
---------------------------

I tried to debug this and always end up at cJobject.dsProcess where badJSON gets called.
To be honest I could invest a couple of hours and maybe I could get to the bottom of this. If you don't have time I'm going to get back to my desk and fix this myself - I really appreciate your support over here but don't want to stretch it too far.
Directly below the call to badJSON is the Case "[" that should catch the first "[" of the API result.

Do you have any clue what's happening here?
Any more input from my side helpful?
Thank you very much!

Cheers,
Johannes

[Bruce Mcpherson]

Hi Johannes

I don't have authentication to that site so I cant test it, but jSon starting with and array and no label is kinda weird.. 

However you can get round it with a very simple change... 

in the cJobject class, just modify this line 

pWhatNext = "{"

to

pWhatNext = "{["

.. essentially you are just allowing [ as a starting character, and it should figure it all out.from there.

if it works out for you, then let me know and I'll consider changing it in the uploaded version...

...Here's a module to test it using the string in your email......

Private Sub messabout()

    Dim jo As New cJobject, job As cJobject

    Set job = jo.deSerialize( _

        "[{'href':'http:\/\/en.wikipedia.org\/wiki\/Exploratory_Data_Analysis'," & _

        "'description':'Exploratory data analysis - Wikipedia, the free Encyclopedia'," & _

        "'extended':'','meta':'576e8bbed7ed3646da28523cc4cf0f73','hash':'8114ff56779cbce0c8cdeaedd21edab2'," & _

        "'time':'2011-08-12T02:07:00Z','shared':'no','toread':'no','tags':'da-extended DA-source'}" _

        )

    Debug.Print job.Serialize(True)

End Sub

and here's the output

{

   "_deserialization":[      {

         "href":"http://en.wikipedia.org/wiki/Exploratory_Data_Analysis",

         "description":"Exploratory data analysis - Wikipedia, the free Encyclopedia",

         "extended":"",

         "meta":"576e8bbed7ed3646da28523cc4cf0f73",

         "hash":"8114ff56779cbce0c8cdeaedd21edab2",

         "time":"2011-08-12T02:07:00Z",

         "shared":"no",

         "toread":"no",

         "tags":"da-extended DA-source"

      }

    ]

 }

Johannes

--
You received this message because you are subscribed to the Google Groups "Excel Ramblings" group.
To view this discussion on the web visit https://groups.google.com/d/msg/excel-ramblings/-/jZ6IDo5gsosJ.
To post to this group, send email to excel-r...@googlegroups.com.
To unsubscribe from this group, send email to excel-ramblin...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/excel-ramblings?hl=en.

[Bruce Mcpherson]

btw - thats in the dsLoop method of the cJobject class - change

pWhatNext = "{[" 

bruce

[Bruce Mcpherson]

Hi Brent

The problem there is that what is being returned by this api is not actually JSON. To test whether json is valid, paste it in into jsonLint. Its actually returning the same kind of syntax that it uses in its google Wire protocol as used in http://ramblings.mcpher.com/Home/excelquirks/get-data-from-google-docs

i do have a simple way of decoding this though, and ive updated the latest cdataset.xlsm to incorporate your query and deal with this format.

here's how - the wire parameter indicates that the reponse is google wire format rather than real json

        With .add("googlecurrencyconverter")

            .add "restType", erRestType.erQueryPerRow

            .add "url", "http://www.google.com/ig/calculator?hl=en&q=1USD=?"

            .add "results", ""

            .add "treeSearch", False

            .add "ignore", vbNullString

            .add "wire", True

        End With

you'll need to download the latest cdataset,xlsm http://ramblings.mcpher.com/Home/excelquirks/downlable-items to get this.

bruce

On 29 August 2012 01:06, Brent <brento...@gmail.com> wrote:

Hi Bruce

I ran into the same error about the Bad JSON character starting at 1. Im trying to utilize googles rest based currency convertor:

here is the Json produced by the following URL http://www.google.com/ig/calculator?hl=en&q=1USD=?NZD

{lhs: "1 U.S. dollar",rhs: "1.23839009 New Zealand dollars",error: "",icc: true} 

As you can see the json is missing some of the quotes required for deserialization. I also have to seperate the decimal from the string?

Any idea how i can work with this format of json? I tried changing the cJobject class,back to

pWhatNext = "{"

But where would i address the fact that there are no "quotes " around "1hs" and "rhs"

Thanks for your help. I thought i was starting with the easy stuff

Hope others are able to utilize this rest library as well. Maybe i should learn how to do this before i try and communicate this info with servers.

Here are the additions to add to your library

restLibraryExamples module:

Public Sub testGoogleCurrencyConverter()

    generalQuery "googlefinance", "googlecurrencyconverter", _

        InputBox(prompt:="Enter your currency", _

             title:="Google API query:results to the google Finance worksheet")

End Sub

restLibrary module:

        With .add("googlecurrencyconverter")

            .add "restType", erSingleQuery 'a single column provides the query data for each row

            .add "url", "http://www.google.com/ig/calculator?hl=en&q=1USD=?"

            ' rhs is the name from http://www.google.com/ig/calculator?hl=en&q=1USD%3D%3FNZD

            .add "results", "rhs"

            .add "treeSearch", True

            .add "ignore", vbNullString

        End With

To view this discussion on the web, visit https://groups.google.com/d/msg/excel-ramblings/-/fvK86WTH32EJ.
To post to this group, send an email to excel-r...@googlegroups.com.

To unsubscribe from this group, send email to excel-ramblin...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/excel-ramblings?hl=en-GB.

[Brenton Nauer]

Hi Bruce, got it thanks! Your site is really helping me learn some of these different protocols, I appreciate all the help you've given me as well as all the examples to learn from. I should have remembered the wireformat from other google docs interaction. Thanks!