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.

Improve this question
1

5 Answers 5

Reset to default
11

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));
    }
);
Improve this answer
3
  • 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); Commented Apr 9, 2014 at 9:12
  • 1
    Time to vote this useful answer. Thank you Vadim!!! Commented Sep 16, 2015 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? Commented Dec 1, 2015 at 1:43
4

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 :)

Improve this answer
3
  • 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! Commented Jun 5, 2017 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! Commented Jun 5, 2017 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. Commented Dec 4, 2017 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.

Improve this answer
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")
Improve this answer
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.