CODEWITHSUNDEEP
rethinkdb
Barry G
Barry G

Reputation: 221

How can I descending sort a grouping based on the count of the reduction array in rethinkdb

Importing this dataset as a table:

https://data.cityofnewyork.us/Housing-Development/Registration-Contacts/feu5-w2e2#revert

I use the following query to perform an aggregation and then attempt to sort in descending order based on the reduction field. My intention is sort based on the count of that field or to have the aggregation create a second field called count and sort the grouping results in descending order of the reduction array count or length. How can this be done in rethinkdb?

query:

r.table("contacts").filter({"Type": "Agent","ContactDescription" : "CONDO"}).hasFields("CorporationName").group("CorporationName").ungroup().orderBy(r.desc('reduction'))

Upvotes: 0

Views: 299

Answers (2)

mlucy
mlucy

Reputation: 5289

I don't quite understand what you're going for, but does this do what you want? If not, what do you want to be different in the output?

r.table("contacts")
 .filter({"Type": "Agent","ContactDescription" : "CONDO"})
 .hasFields("CorporationName")
 .group("CorporationName")
 .ungroup()
 .merge(function(row){ return {count: row('reduction').count()}; })
 .orderBy(r.desc('count'))

Upvotes: 1

Codinfox
Codinfox

Reputation: 608

You are almost there:

r.table("contacts").filter({"Type": "Agent","ContactDescription" : "CONDO"}).hasFields("CorporationName").group("CorporationName").count().ungroup().orderBy(r.desc('reduction'))

See that .count()? That is a map-reduce operation to get the count of each group.

I haven't tested the query on your dataset. Please comment in case you had problems with it.

EDIT:

If you want to add a count field and preserve the original document, you need to use map and reduce. In your case, it should be something like:

r.table("contacts").filter({"Type": "Agent","ContactDescription" : "CONDO"})
    .hasFields("CorporationName")
    .group("CorporationName")
    .map(r.row.merge({count:1}))
    .reduce(function(left, right){
        return {
            count: left('count').add(right('count')),
            <YOUR_OTHER_FIELDS>: left('<YOUR_OTHER_FIELDS>'),
            ...
        };
    })
    .ungroup().orderBy(r.desc(r.row('reduction')('count')))

EDIT:

I am not sure if this can do the trick, but it is worth a try:

.reduce(function(left, right){
    return left.merge({count: left('count').add(right('count'))})
})

Upvotes: 0

Related Questions