3

My application has a search field and to do an autocomplete, I first fetch the distinct() values, and immediately after, I send a count() query for each distinct value. There can be dozens of values to then count, that's a lot of queries.

Any idea how I could avoid this large number of queries using MongoDB's NodeJS module?

For now, the query is as such:

const baseQuery = {
    "organization": organization,
    "status": "processed"
}

let domains = []

// A. Query to get the disinct values
MongoDB.getMainCollection().distinct(`location.hostname`, { organization, status: "processed" })

    // B. Got the value, now creating a COUNT() query for each
    .then(list => {
        domains = list.map((host,idx) => Object.assign({}, { domain: host, count: 0, idx: idx }))
        const countingPromises = list.map(host => MongoDB.getMainCollection().count(Object.assign({}, baseQuery, { "location.hostname": host })))
        return Promise.all(countingPromises)
    })

    // C. Putting it all together
    .then(values => {

        values.forEach((count, idx) => {
            const domain = domains.find(d => d.idx === idx)
            if (domain) {
                domain.count = count
            }
        })

        domains.sort((a,b) => b.count - a.count)

        resolve(domains)
    })

    .catch(err => reject(new AppError(`Error listing hostnames for @${organization}.`, 500, err, payload)))

p.s. this works as intended and returns what I want -- just want to avoid so many queries and perhaps bundle them if possible?

Lazhar
  • 1,401
  • 16
  • 37

1 Answers1

3

You can get all the distinct values and their counts in a single aggregate query:

MongoDB.getMainCollection().aggregate([
    // Filter for the desired docs
    {$match: baseQuery},
    // Group the docs by location.hostname and get a count for each
    {$group: {_id: '$location.hostname', count: {$sum: 1}}}
])
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471