6

Hi I have a list with two fields "Regions" & "Countries" I am using a rest api to retrieve the list of just regions like so: http:mysitename/_api/web/lists/getByTitle('TaxTool')/items?$select=Region

In the results there are lots of duplicates and I was wondering how to return unique values.

10

According to Use OData query operations in SharePoint REST requests, Grouping is not supported.

The solution, is to apply grouping for the JSON results returned from REST endpoint.

Example

Suppose the following function is used for getting list items via SharePoint REST API:

function getListItems(url, listname, query, complete, failure) {
    $.ajax({
        url: url + "/_api/web/lists/getbytitle('" + listname + "')/items" + query,
        method: "GET",
        headers: { "Accept": "application/json; odata=verbose" },
        success: function (data) {
            complete(data.d); 
        },
        error: function (data) {
            failure(data);
        }
    });
}

Then you could retrieve unique values from JSON array as demonstrated in answer Get unique results from JSON array using jQuery:

function groupBy(items,propertyName)
{
    var result = [];
    $.each(items, function(index, item) {
       if ($.inArray(item[propertyName], result)==-1) {
          result.push(item[propertyName]);
       }
    });
    return result;
}

The usage

getListItems('https://tenant.sharepoint.com/project','Tasks','?select=Title',
    function(items){    
       var taskNames = groupBy(items,'Title');
       console.log(taskNames);
    },
    function(error){
       console.log(JSON.stringify(error));
    }
);
  • 1
    Thank you so much for your help, this was way more information than I was expecting & it worked a treat once I changed a line of code in the getListItems function from: complete(data.d); to complete(data.d.results); – perrycoke Apr 9 '14 at 9:12
  • 1
    Time to vote this useful answer. Thank you Vadim!!! – lex Sep 16 '15 at 15:26
  • Thanks Vadim it worked like a charm after changing the data.d.results as perrycoke suggested. Though I have one more issue the field I want to group is a person field.How can I use this your solution to achieve this? – naijacoder Dec 1 '15 at 1:43
3

I know it's a relatively old post but, for anyone who's still looking for an answer on this, querying for both grouping and aggregations(Totals) is supported in REST. Following are the two links for both the operations: -

You can always pull the entire data and then apply your custom logic but there will be following issues: -

  • Applying custom grouping logic will make your application slower. If SharePoint is directly providing the grouped result then why do it yourself?
  • While aggregation functions like SUM,AVG,etc may be easy to implement (will still be slower) but, doing your custom calculation for Std Dev and Variance can be really complicated!

All in all, nothing can beat the performance of directly getting grouped result from SharePoint :)

  • Again a neg tick for no reason. This is really discouraging and refrain people from trying to come out to help. The answer given here is might have been correct at that time but people are still referencing it! Saying that SharePoint does not support this feature is COMPLETELY WRONG. When I tried to help, got this neg mark. DISAPPOINTING! – Piyush Jun 5 '17 at 15:07
  • Hi @Piyush, we fully appreciate your tries to help, but you should be aware of downvote is usually provided from community members whose see your answer as unhelpful or very low quality, so I suggest to check this link sharepoint.stackexchange.com/help/how-to-answer) to know how to provide a good answer that matches OP requirement,Also please try to add some codes/detail from your own link to be valuable for future users then add your link as a reference once you do that the community members should worth and upvote your answer in case it helpful! Thanks again for your contributions! – Mohamed El-Qassas MVP Jun 5 '17 at 22:11
  • 1
    I am not the downvoter, this is just my opinion: Whilst I think your answer is useful and adds to the conversation with regard to grouping. I also think this is not an answer to to OP question which is how do you select distinct using REST. The sad answer is actually that you will have to wastefully retrieve the entire recordset and then filter to distinct at the client. Which is a clear deficiency. – Joe Johnston Dec 4 '17 at 22:28
1

There isn't a REST filter that I know of that will handle getting distinct or unique values. You'll need to do that in your success callback. Typically I do this by creating an array and looping through the results. I'll check if the item is in the array, if not I push it in, if so I don't do anything with it.

1

if you are using any JS Library (jQuery, in this case) to group, I recommend you to use underscore.js or agile.js.

underscore.js (http://underscorejs.org/#groupBy)

//group by an enumerated property
_.groupBy(response,"enumerablePropertyYouWantToGroup")

//group by a nested property
var groupped = _.groupBy( response, 
                   function(group){ 
                      return group["enumerableProperty"]["propertyYouWantToGroupBy"]
                    });

agile.js (https://github.com/a8m/agile#groupby)

var groupped = _.groupBy(response,"propertyYouWantToGroupBy")
var groupped = _.groupBy(response,"enumerableProperty.propertyYouWantToGroupBy")
0

Developing a custom OData service (using WCF Data Services for example), you can use my product AdaptiveLINQ that allows server-side grouping.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.