Distinct Query with Cloudant Connector using Loopback in API Connect/StrongLoop

Question

I am trying to get distinct values for a query using Loopback with a Cloudant Connector, but I haven't found anything about this in the documentation.

e.g. I need a query to turn this:

[
{
rating: "★★★★★"
},
{
rating: "★★★★★"
},
{
rating: "★★★★★"
},
{
rating: "★★★★★"
},
{
rating: "★★★☆☆"
},
{
rating: "★★★☆☆"
}
]

into this:

[
{
rating: "★★★★★"
},
{
rating: "★★★☆☆"
}
]

I'm using the REST API to query my Products model (above is a filtered view of just the rating field). If there is some sort of filter that I can use without modifying the server that I somehow just missed in the documentation, that would be the best choice.

Is there any way I can add a distinct field like:

/Products?filter[fields][rating]=true?distinct=true

or how can I go about solving this?

Also, I've seen another answer talking about adding a remote method to solve this (something like this for mySQL):

Locations.regions = function (cb) {
  var ds = Locations.app.datasources.myDS;
  var sql = "SELECT DISTINCT region FROM Locations ORDER BY region"; // here you write your sql query.

  ds.connector.execute(sql, [], function (err, regions) {

    if (err) {
      cb(err, null);
    } else {
      cb(null, regions);
    }

  });

};

Locations.remoteMethod(
  'regions', {
    http: {
      path: '/regions',
      verb: 'get'
    },
    returns: {
      root: true,
      type: 'object'
    }
  }
);

If this would work, how would I implement it with the Cloudant NoSQL DB connector?

Thanks!

Glynn Bird Glynn Bird · Accepted Answer · 2016-06-14T06:28:58

If your documents looked like this:

{
  "name": "Star Wars",
  "year": 1978,
  "rating": "*****"
 }

You can create a MapReduce view, which emits doc.rating as the key and uses the build-in _count reducer:

 function(doc) {
   emit(doc.rating,null);
 }

When you query this view with group=true, distinct values of rating will be presented with counts of their occurrence in the data set.

Distinct Query with Cloudant Connector using Loopback in API Connect/StrongLoop

1 Answers