Programatically limit information returned in API requests?

Dann_Michelson

I'm working on API calls - mostly GetProjectionByCriteria. I don't want to limit the number of results I get, but I was wondering if there was a way to limit the information each result returns. Say I have json that returns similar to this:

[<br>  {<br>    "ClassTypeId": "aaaaa-aaaaa-aaaaaaaaaaa-aaaaa",<br>    "FullName": "FirstResultObject - 0001",<br>    "SomeLinkedEnumObject": {<br>      "Id": "bbbbb-bbbbb-bbbbbbbbbbb-bbbbb",<br>      "Name": "Active",<br>      "HierarchyLevel": 0<br>    },<br>    "AnotherLinkedEnumObject": {<br>      "Id": "ccccc-bbbbb-bbbbbbbbbbb-bbbbb",<br>      "Name": "Hidden",<br>      "HierarchyLevel": 0<br>    },<br>    "Target_12345_67890_1234567890_12345": {<br>      "ClassTypeId": "ddddd-ddddd-ddddddddddd-ddddd",<br>      "DisplayName": "SampleOperatingSystem",<br>      "FullName": "OS:SampleOperatingSystem"<br>    },<br>    "DisplayName": "First Result Object"<br>  },<br>  {<br>    "ClassTypeId": "eeeee-eeeee-aaaaaaaaaaa-aaaaa",<br>    "FullName": "SecnResultObject - 0001",<br>    "SomeLinkedEnumObject": {<br>      "Id": "bbbbb-ccccc-bbbbbbbbbbb-bbbbb",<br>      "Name": "Inactive",<br>      "HierarchyLevel": 0<br>    },<br>    "AnotherLinkedEnumObject": {<br>      "Id": "ccccc-ccccc-bbbbbbbbbbb-bbbbb",<br>      "Name": "Visible",<br>      "HierarchyLevel": 0<br>    },<br>    "Target_12345_67890_1234567890_12345": {<br>      "ClassTypeId": "ddddd-ddddd-ddddddddddd-ddddd",<br>      "DisplayName": "SampleOperatingSystem",<br>      "FullName": "OS:SampleOperatingSystem"<br>    },<br>    "DisplayName": "First Result Object"<br>  },<br>]

Real-world examples are much larger, and this can get pretty time-consuming to wait for. I really only need a few fields and child-fields. If I could limit my results, getting something like the example below, it would go much faster. In real world projections, sometimes I only need 5% of the data that is returned. Is there a way to request this limited result? (I've seen other APIs from other organizations that provide some sort of Result Data Limiter as a variable in their get/post requests that return datasets.)

[<br>  {<br>    "ClassTypeId": "aaaaa-aaaaa-aaaaaaaaaaa-aaaaa",<br>    "SomeLinkedEnumObject": {<br>      "Id": "bbbbb-bbbbb-bbbbbbbbbbb-bbbbb",<br>      "Name": "Active",<br>      "HierarchyLevel": 0<br>    },<br>    "AnotherLinkedEnumObject": {<br>      "Id": "ccccc-bbbbb-bbbbbbbbbbb-bbbbb",<br>      "Name": "Hidden",<br>      "HierarchyLevel": 0<br>    },<br>    "Target_12345_67890_1234567890_12345": {<br>      "DisplayName": "SampleOperatingSystem",<br>    },<br>    "DisplayName": "First Result Object"<br>  },<br>  {<br>    "ClassTypeId": "eeeee-eeeee-aaaaaaaaaaa-aaaaa",<br>    "AnotherLinkedEnumObject": {<br>      "Id": "ccccc-ccccc-bbbbbbbbbbb-bbbbb",<br>      "Name": "Visible",<br>      "HierarchyLevel": 0<br>    },<br>    "Target_12345_67890_1234567890_12345": {<br>      "DisplayName": "SampleOperatingSystem",<br>    },<br>    "DisplayName": "First Result Object"<br>  },<br>]

(Let me know if I categorized this improperly.)

Nicholas_Velich

As far as I know, there is not a way to limit the properties returned via our API within a particular call; however, each call does return different property sets. GetUser or GetConfigItemsByAbstractClass return fewer properties than GetProjectionByCriteria for example.

That being said, with GetProjectionByCriteria, properties aren't going to be the main factor impacting performance-- the relationships brought in via type projection are what will impact performance the most. I'm not sure what type projection you are using (or need) in the example above, but try to use the smallest projection possible when dealing with a large quantity of results.

This sounds like a promising feature request if you'd like to submit it.

Geoff_Ross

Hi Dann, 

From v6 onwards you can use the new SQL Dashboard Query API. 

/Dashboard/GetDashboardQueryData?query=SELECT+*+FROM+WorkItems

Replace the query with any query your SQL skills will allow and it will return JSON for only the columns you include in the SELECT statement. This will be run against ServiceManagement DB. If you need data from outside of that database, write a view inside the ServiceManagement DB that queries a different database and call that view in the API.

Geoff

Nicholas_Velich

As far as I know, there is not a way to limit the properties returned via our API within a particular call; however, each call does return different property sets. GetUser or GetConfigItemsByAbstractClass return fewer properties than GetProjectionByCriteria for example.

That being said, with GetProjectionByCriteria, properties aren't going to be the main factor impacting performance-- the relationships brought in via type projection are what will impact performance the most. I'm not sure what type projection you are using (or need) in the example above, but try to use the smallest projection possible when dealing with a large quantity of results.

This sounds like a promising feature request if you'd like to submit it.

Dann_Michelson

Thanks Nicholas. Would an example be helpful in the feature request? One API I've used recently that has this is Mailchimp's

Nicholas_Velich

As much info as possible in the feature request is always helpful. Examples are great in this regard.

Geoff_Ross

Hi Dann, 

From v6 onwards you can use the new SQL Dashboard Query API. 

/Dashboard/GetDashboardQueryData?query=SELECT+*+FROM+WorkItems

Replace the query with any query your SQL skills will allow and it will return JSON for only the columns you include in the SELECT statement. This will be run against ServiceManagement DB. If you need data from outside of that database, write a view inside the ServiceManagement DB that queries a different database and call that view in the API.

Geoff