Integrations and APIs for Service

Get Involved. Join the Conversation.

Topic

    Chris Rogers
    REST API: Sub collection fields
    Topic posted November 28, 2018 by Chris RogersBronze Medal: 1,250+ Points, tagged REST 
    124 Views, 5 Comments
    Title:
    REST API: Sub collection fields
    Summary:
    How to expand a sub collection?
    Content:

    Hello

    Does anyone know how to expand / get specific fields from a sub collection?

    Specifically I'd like to get all the columns for a report, without having to do a REST call for each column...

    EG

    /services/rest/connect/v1.4/analyticsReports

    {
      "items": [
        {
          "id": 1,
          "lookupName": "Answer Notifications by Answer",
          "createdTime": "2007-02-01T00:00:00.000Z",
          "updatedTime": "2007-02-01T00:00:00.000Z",
          "links": [
            {
              "rel": "canonical",
              "href": "https://service.elsevier.com/services/rest/connect/v1.4/analyticsReports/1"
            }
          ]
        },
    ...
    
    

    Gets all reports, yay

     

    /services/rest/connect/v1.4/analyticsReports?fields=name

    {
      "items": [
        {
          "id": 1,
          "lookupName": "Answer Notifications by Answer",
          "createdTime": "2007-02-01T00:00:00.000Z",
          "updatedTime": "2007-02-01T00:00:00.000Z",
          "name": "Answer Notifications by Answer",
          "links": [
            {
              "rel": "canonical",
              "href": "https://service.elsevier.com/services/rest/connect/v1.4/analyticsReports/1"
            }
          ]
        },
    ...
    

    Gets all reports, including the field 'name', yay.

     

    /services/rest/connect/v1.4/analyticsReports/1

    {
      "id": 1,
      "lookupName": "Answer Notifications by Answer",
      "createdTime": "2007-02-01T00:00:00.000Z",
      "updatedTime": "2007-02-01T00:00:00.000Z",
      "columns": {
        "links": [
          {
            "rel": "self",
            "href": "https://service.elsevier.com/services/rest/connect/v1.4/analyticsReports/1/columns"
          },
          {
            "rel": "full",
            "href": "https://service.elsevier.com/services/rest/connect/v1.4/analyticsReports/1/columns/{column_id}",
            "templated": true
          }
        ]
      },
    

    Gets a specific report, yay

    /services/rest/connect/v1.4/analyticsReports/1?expand=columns

    or /services/rest/connect/v1.4/analyticsReports/1/columns

    "columns": {
        "items": [
          {
            "rel": "canonical",
            "href": "https://service.elsevier.com/services/rest/connect/v1.4/analyticsReports/1/columns/0"
          },
          {
            "rel": "canonical",
            "href": "https://service.elsevier.com/services/rest/connect/v1.4/analyticsReports/1/columns/1"
          },
          {
            "rel": "canonical",
            "href": "https://service.elsevier.com/services/rest/connect/v1.4/analyticsReports/1/columns/2"
          }
        ],
    

    Just expands columns into links :(

     

    /services/rest/connect/v1.4/analyticsReports/1/columns?expand=all  - Nope :(

    /services/rest/connect/v1.4/analyticsReports/1/columns?fields=heading - Nope :(

     

    Anyone have any smart ideas that I've missed?

    I don't really want to have to do web requests for each column, eg

    /services/rest/connect/v1.4/analyticsReports/1/columns/0
    /services/rest/connect/v1.4/analyticsReports/1/columns/1
    /services/rest/connect/v1.4/analyticsReports/1/columns/2
    /services/rest/connect/v1.4/analyticsReports/1/columns/3
    /services/rest/connect/v1.4/analyticsReports/1/columns/.....
    

    Thanks

    Version:
    18A

    Comment

     

    • Rajan Davis

      You can add a /?expand parameter to the URL.

      My preference is use ROQL and get the fields that I want so that I can capture a more specific slice of the data that I am looking for.

      Either option should be sufficient.

      Hope this helps,

      Raj

      • Chris Rogers

        Hi Rajan

        As listed above, I have already tried /services/rest/connect/v1.4/analyticsReports/1/columns?expand=all 

        The expand parameter seems to work for 'normal' collections (eg analyticsReports), but not sub collections (eg analyticsReposts/columns)

         

        SELECT AnalyticsReport.Columns.* FROM AnalyticsReport WHERE ID = 1
        

        Also doesn't seem to work, unless I've made a mistake in the ROQL?

         

        • Rajan Davis

          Hi Chris,

          I apologize for glossing over that.

          Can you explain a bit more about what you are trying to accomplish with the getting the sub collections? If it's something specific to reports, the following will give you some options. If what you are trying to do is NOT specific to reports, then please let me know a bit more of what you are trying to do.

          I do not think the REST API allows you to get sub collections in the way that you are trying for analyticsReports.

          In addition, ROQL does not expose the columns for a given report. You can generally get sub collections for other objects so I was a little surprised that this was not possible with ROQL.

          Alternatively, you can try running a report with the REST API to get the columns names.

          For example, if you send a POST request to the analyticsReportResults endpoint with the following JSON data

          {
            "id": 1
          }
          

          You should get something like the following response:

          {
            "columnNames": [
              "Answer #",
              "Summary",
              "Notifications"
            ],
            "count": 0,
            "links": [
              {
                "href": "https://service.elsevier.com/services/rest/connect/v1.3/analyticsReportResults",
                "rel": "self"
              },
              {
                "href": "https://service.elsevier.com/services/rest/connect/v1.3/analyticsReportResults",
                "rel": "canonical"
              },
              {
                "href": "https://service.elsevier.com/services/rest/connect/v1.3/metadata-catalog/analyticsReportResults",
                "mediaType": "application/schema+json",
                "rel": "describedby"
              }
            ],
            "name": "Answer Notifications by Answer",
            "rows": []
          }
          

          Not sure if this answers your question, but if you can let me know more about what you are trying to accomplish, I can provide a better direction.

          Kind Regards,

          Rajan

    • Chris Rogers

      Hi Rajan, thanks for your suggestions so far smiley

      In this specific situation I am trying to get all the DataType for each column in the report, so that when I run the report I can turn the results into the correct format in the receiving code

       "dataType": {
          "id": 3,
          "lookupName": "INT"
        }
      

      However I would rather not have to do a web call for every column before actually running the report!

       

      I do note that expanding sub collections seems to be a feature missing from all sub collections, eg

      /services/rest/connect/v1.4/contacts/123?expand=emails

      /services/rest/connect/v1.4/contacts/123?fields=emails.address&expand=all

      Just gives you the item links, rather than the actual details

      "emails": {
          "items": [
            {
              "rel": "canonical",
              "href": "/services/rest/connect/v1.4/contacts/123/emails/0"
            }
          ],
          "links": [
            {
              "rel": "self",
              "href": "/services/rest/connect/v1.4/contacts/123/emails"
            },
            {
              "rel": "canonical",
              "href": "/services/rest/connect/v1.4/contacts/123/emails"
            },
            {
              "rel": "describedby",
              "href": "https://service.elsevier.com/services/rest/connect/v1.4/metadata-catalog/contacts/emails",
              "mediaType": "application/schema+json"
            }
          ]
        },
      

      However, like you suggest this could be achieved with ROQL, however there doesn't seem to be a way to 'autodiscover' ROQL, like there is the different endpoints. (The schema+json)

      Wonder if it might be worth me creating an idea in the ideas lab?

      • Rajan Davis

        It might not be the same as "autodiscover", but when I use ROQL, I will usually use a DESCRIBE statement and try to build queries iteratively.

        For example, using the "contacts" example above, if I wanted to get nested info for a given contact, I would run the following queries:

        DESCRIBE contacts.emails
        

        Which would return:

        [
          {
            "Name": "emailList",
            "Path": "contacts.emails.emailList",
            "Type": "SubTable"
          }
        ]
        

        I would then run 

        DESCRIBE contacts.emails.emailList
        

        Which would return:

        [
          {
            "Name": "addressType",
            "Path": "contacts.emails.emailList.addressType",
            "Type": "SubTable"
          },
          {
            "Name": "address",
            "Path": "",
            "Type": "String"
          },
          {
            "Name": "certificate",
            "Path": "",
            "Type": "String"
          },
          {
            "Name": "invalid",
            "Path": "",
            "Type": "Integer"
          }
        ]
        

        I could get deeper with

        DESCRIBE contacts.emails.emailList.addressType
        

        which returns

        [                         
          {                       
            "Name": "id",         
            "Path": "",           
            "Type": "Integer"     
          },                      
          {                       
            "Name": "name",       
            "Path": "",           
            "Type": "String"      
          },                      
          {                       
            "Name": "lookupName", 
            "Path": "",           
            "Type": "String"      
          }                       
        ]                         
        

        And then combine them together with a query such as

        SELECT id as c_id, lookupName as c_lookupName, contacts.emails.emailList.*, contacts.emails.emailList.addressType.* from contacts WHERE lookupName LIKE '%Raj Davis%'
        

        which returns:

        [
          {
            "address": "rajandavis@eventusg.com",
            "addressType": 0,
            "c_id": 1354,
            "c_lookupName": "Raj Davis",
            "certificate": "null",
            "id": 0,
            "invalid": 0,
            "lookupName": "Email - Primary",
            "name": "Email - Primary"
          },
          {
            "address": "null",
            "addressType": 1,
            "c_id": 1354,
            "c_lookupName": "Raj Davis",
            "certificate": "null",
            "id": 1,
            "invalid": 0,
            "lookupName": "Alternate Email 1",
            "name": "Alternate Email 1"
          },
          {
            "address": "null",
            "addressType": 2,
            "c_id": 1354,
            "c_lookupName": "Raj Davis",
            "certificate": "null",
            "id": 2,
            "invalid": 0,
            "lookupName": "Alternate Email 2",
            "name": "Alternate Email 2"
          }
        ]
        

        That's what I meant with using ROQL for getting sub collections.

        The only issue is that analyticsReports don't expose this in the same way with the columns...

        You still have to build up the queries, but once you have it figured out, they will typically transfer to whichever site you are using.