Forum Discussion

_anomDiebolt_'s avatar
_anomDiebolt_
Qrew Elite
10 years ago

How To Get JSON From API_DoQuery?

We just had our Mas Que Nada QuickBase User Group planing meeting last night and several presenters were concerned that their presentations might be too advanced for the expected audience. So I was asked me to share some introductory materials that would serve as a foundation to some of the talks. So this post deals with bringing you up to speed on getting JSON from your API_DoQuery API calls.

As you probably know QuickBase's API returns XML. Unfortunately, XML lost the data interchange war to JSON and developers are stuck with the burdensome task of converting the XML returned by the QuickBase API to JSON before they can do anything productive with the data. You may be shocked by this opinion but it is overwhelmingly true and common knowledge among web developers:

JSON: The Fat-Free Alternative to XML
http://www.json.org/xml.html

So here is a simple technique to turn your XML into JSON transparently as part of your API_DoQuery requests. Here is a simple application with three records consisting of three field [Name], [Age], and [Email]:

API_DoQuery Return JSON - List All
https://haversineconsulting.quickbase.com/db/bjgzmpcgs?a=q&qid=1

Here is the XML Response (Flat) that API_DoQuery&qid=1 would return:

http://pastie.org/10264183


and this the the JSON Response we want to work with:

http://pastie.org/10264186


Here is some simple code that will effectively convert the Flat XML Response to a JSON Response as part of the AJAX request:

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=395

In a nutshell the dataFilter setting is used to define a utility function XMLFlatToObj  to convert the XML returned by API_DoQuery into JSON. See screenshot attached

If you master this simple concept you will be one step close to understating the some of the fantastic presentations at the Mas Que Nada QuickBase User Group Conference in Brazil.

APIs and custom code
Branding and app management
NotificationsEmail
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 years ago
    I added a complementary function XMLStructuredToObj to a new pastie to show how you can obtain a second JSON representaton from API_DoQuery based on returning the so-called Structured XML Response. The main difference is that the JSON returned by XMLFlatToObj will have lowercased and underscore mapped special characters key names (eg "record_id_") versus key names identical to the field label "Record ID#"

    Here is the XML Response (Structured) that API_DoQuery&qid=1 would return


    http://pastie.org/10268257


    and this the the JSON Response we want to work with:


    http://pastie.org/10268260


    Here is some simple code that will effectively convert the Structured XML Response to a JSON Response as part of the AJAX request:

    Pastie Database
    https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=403

    Screenshot attached.
    • GauravSharma3's avatar
      GauravSharma3
      Qrew Commander
      9 years ago
      It will very helpful.. Much appreciated your effort Dan!

      Thanks,
      Gaurav
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    10 years ago
    I've been using xml2json.js which is a very lightweight javascript plugin to handle the conversion which allows me to build apps with AngularJS or any other js framework.  But it would be nice not to have to build an XML document or send variables in the REST format for larger requests.  A JSON payload would work so much better especially as IE has so many quirks building documents :/ 

    Here is the repository: https://code.google.com/p/x2js/

    //fmt = false
    <script src="js/xml2json.min.js" type="text/javascript"></script>
    <script>
    var xml2str = new X2JS();
    var jsonObj = xml2str.xml_str2json(xml);
    var records = jsonObj.qdbapi.record;
    //Do whatever you want with your shiny new JSON data
    </script>
    //fmt = structured
    <script src="js/xml2json.min.js" type="text/javascript"></script>
    <script>
    var xml2str = new X2JS();
    var jsonObj = xml2str.xml_str2json(xml);
    var records = jsonObj.qdbapi.table.records;
    //Do whatever you want with your shiny new JSON data
    </script>
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 years ago
    I think I looked at this in the past. What happens when the field contains HTML? When the XML response is converted to JSON does the HTML get converted to JSON in a nested fashion? I don't think you would want this behavior if it does and that has been a concern with some other libraries I have looked at.

    The problem really has to be solved by QuickBase. The existing XML flat format has a lot of warty behavior (and XML structured contains too much query and field filler) - for example [%] and [-] both convert to <_> as XML and then what does the X2JS do with the duplicate _ property (it gets overwritten)? In general XML has greater expressiblity than JSON because XML is ordered and can contains duplicates while JSON is neither. And XML does have a benefit that it can be search with CSS selectors while JSON cannot be so easily searched (there is no selector language like XPath or CSS for JSON).

    It is great to see someone else looking at this with fresh eyes. I am going to take a second look at this library myself.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 years ago
    Here is the problem as I see it. This XML Flat Response contains a field with html allowed:

    http://pastie.org/pastes/10307124/text

    and X2JS converts to this JSON:

    http://pastie.org/pastes/10307123/text

    when I think is should convert to something like this to be useful for client side templating within QuickBase:

    http://pastie.org/pastes/10307128/text

    And I think there are other issues with attributes, character entities, and QuickBase's crazy lowercase and underscore conversion of fields to XML tags etc.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    10 years ago
    I can see that as being an issue if you're doing templating in that manner.  I go for the MVC approach where QB only handles the model portion.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    10 years ago
    It is definitely not an all purpose solution and the underscore naming structure has to be accounted for.  If someone else is controlling what the names of the fields are I will generally stick with the structured format so that the keys are uniform.    It would be great if they provided a JSON API out of the box because while it's a small amount of code it still has to be present on every Angular app I build :(  And the format structure is not ideal as it sends way too much info for what I normally need.  If I query a record for 13 fields I get a file with 450 lines and only ~20 of them are pertinent.  In addition if this is public facing client side app then there might be info in the response that I don't want to be out there.
  • _anomDiebolt_'s avatar
    _anomDiebolt_
    Qrew Elite
    10 years ago
    What we need is something like the lightweight response returned by API_GetRecordInfo but for multiple records and in JSON

    <field>
       <fid>8</fid>
       <name>URL</name>
       <type>URL</type>
       <value>www.intuit.com</value>;
    </field>

    {  
       "field":{  
          "fid":"8",
          "name":"URL",
          "type":"URL",
          "value":"www.intuit.com";
       }
    }

    The "type" property is useful as you can indicate if the field should be right (number) or left (text) justified in a client side template. But you also have to look at other field types that carry multiple pieces of information like files an addresses.