Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    David Wright
    REST API - analyticsReportResults return nested JSON format
    Topic posted July 18, 2017 by David WrightBlue Ribbon: 750+ Points, tagged REST 
    492 Views, 9 Comments
    Title:
    REST API - analyticsReportResults return nested JSON format
    Content:

    Does anyone know if it is possible to change the format / nesting of the JSON as returned by the analyticsReportResults function of the REST api?

    Ideally we want something like:

    {
    "Report1":
    [
    "Column1":"value",
    "Column2":"value"

    "Subreport1":[
    "Column3":"value",
    "Column4":"value"
    ]
    }

    ]
    }

    If not possible using the Analytics report function would there be any other way to achieve it using the REST API?

    Thanks, David

    Comment

     

    • Luuk

      Isn't it possible to convert this yourself in the script that is calling the API?

      That would be the easiest way to fix this.

    • David Wright

      Hi Luuk - thanks for your comment. That sounds like the best way forward.

      Being somewhat of a novice in this area do you have any further suggestion on the best method/approach to take to convert the structure of the JSON within the script? Thanks. David

    • Rajan Davis

      Hi David,

      How are you calling the REST API?

      I made a Ruby wrapper around the REST API a while back that does what you are asking for.

      If you are interested, I can show you a basic example if you private message me; however, here is some Ruby code slightly altered from my library that converts the JSON response into a collection of objects/hashes:

      # Example from 
      # http://docs.oracle.com/cloud/latest/servicecs_gs/CXSVC/op-services-rest-connect-v1.3-queryResults-get.html
      require 'json'
      
      json_input = {
        "items"=> [
          {
            "tableName"=> "incidents",
            "count"=> 10,
            "columnNames"=> [
              "id",
              "subject"
            ],
            "rows"=> [
              [
                "1",
                "How do I access my voicemail outside of my home calling area?"
              ],
              [
                "2",
                "Power surge recovery"
              ],
              [
                "3",
                "The wrong phones were shipped to my office"
              ],
              [
                "4",
                "Health hazards of cell phone"
              ],
              [
                "5",
                "Caller ID problems"
              ],
              [
                "6",
                "Billing issues"
              ],
              [
                "7",
                "I need information about the Mundo recall"
              ],
              [
                "8",
                "I'm looking for a plan with unlimited minutes"
              ],
              [
                "9",
                "Switching to the pre-pay plan"
              ],
              [
                "10",
                "Available ring tones?"
              ]
            ]
          }
        ]
       # ...
      }
      
      # initialize an array to hold all of the objects
      final_hash = []
      
      # loop through the items from the returned JSON response
      json_input['items'].each do |item|
      
              # loop through rows
              item['rows'].each_with_index do |row,row_i|
      
                      # initialize a hash to create the object
                      obj_hash = {}
                      
                      # loop through the column names from the query
                      item['columnNames'].each_with_index do |column,i|
      
                              # set the object property to the value of the row
                              # where the index of the value within that row
                              # matches the index of the column name
                              obj_hash[column] = row[i]
                      end
      
          # push the hash into the array
                      final_hash.push(obj_hash)
      
              end
      
      end
      
      puts final_hash.to_json
      
      # Outputs as:
      #
      # [
      #   {
      #     "id": 1,
      #     "subject": "How do I access my voicemail outside of my home calling area?"
      #   },
      #   {
      #     "id": 2,
      #     "subject": "Power surge recovery"
      #   },
      #   {
      #     "id": 3,
      #     "subject": "The wrong phones were shipped to my office"
      #   },
      #   {
      #     "id": 4,
      #     "subject": "Health hazards of cell phone"
      #   },
      #   {
      #     "id": 5,
      #     "subject": "Caller ID problems"
      #   },
      #   {
      #     "id": 6,
      #     "subject": "Billing issues"
      #   },
      #   {
      #     "id": 7,
      #     "subject": "I need information about the Mundo recall"
      #   },
      #   {
      #     "id": 8,
      #     "subject": "I'm looking for a plan with unlimited minutes"
      #   },
      #   {
      #     "id": 9,
      #     "subject": "Switching to the pre-pay plan"
      #   },
      #   {
      #     "id": 10,
      #     "subject": "Available ring tones?"
      #   }
      # ]
      

      Where it doesn't work is when you have nested queries, but that's a whole different topic altogether... Hope this helps.

      Kind Regards,

      Rajan

    • David Wright

      Hi Rajan - thanks for your response. That certainly helps point me in the right direction. Thanks, David

    • Maheep Kaur

      Hi,

      I am facing the similar problem. I need to traverse the Rest API report result.

      Can you share a sample?

    • Rajan Davis

      RD said:


      Hi,



      I am facing the similar problem. I need to traverse the Rest API report result.



      Can you share a sample?


      View original


      I was looking through the source of what I had written for handling Analytics Report Results in my library and you have to parse the JSON in exactly the same way as the example above. Basically, the JSON response for ROQL will be very similar to the JSON response for Reports.

    • Maheep Kaur

      I am unfamiliar with Ruby.

      Is there any example in DotNet or Java? 

       

    • Pramod Vasudeva Murthy

      Well, I have this example in PHP for parsing XML responses. Will search in my repository for Java/C# code samples.

      ~VIP

    • Rajan Davis
      RD said:

      I am unfamiliar with Ruby.

      Is there any example in DotNet or Java? 

       

      View original

      Hey I was actually mistaken with the example that I provided, it's a little bit different than how you would parse though query Results but I started porting over my Ruby Library to C Sharp. 

      I am not sure how helpful this is by itself, but if you used the DLL and Newtonsoft.JSON libraries, you could do something like this for the analyticsReportsResults:

      using static System.Console;
      using System.Linq;
      using Newtonsoft.Json;
      using Newtonsoft.Json.Linq;
      using static OSCCSharp.Utils;
      using static System.Configuration.ConfigurationManager;
      
      namespace ConsoleApp
      {
          class Program
          {
              static void Main(string[] args)
              {
      
                  var rnClient = new OSCCSharp.Client(
                      username: AppSettings["OSC_ADMIN"],    
                      password: AppSettings["OSC_PASSWORD"], 
                      interfaceName: AppSettings["OSC_SITE"]
                  );
      
                  var arr = new OSCCSharp.AnalyticsReportResults(rnClient);
                  var arrResults = arr.Run(id: 167);
                  var arrObjects = JsonConvert.DeserializeObject<List<object>>(arrResults);
                  foreach (object arrResult in arrObjects)
                  {
                      var arrResultString = JsonConvert.SerializeObject(arrResult);
                      var token = JObject.Parse(arrResultString);
                      var keys = String.Join(", ", token.Properties().Select(p => p.Name).ToArray());
                      var values = String.Join(", ", token.Values());
                      WriteLine($"Columns: {keys}");
                      WriteLine($"Values: {values}");
                  }
              }
          }
      }
              
      

      There are a few things I need to add to this library such as exception handling, but if you look through the code, you should have enough to work with as far as how to parse the results.