Count records in MongoDB by regex match

Question

I have records in database that contains URLs. For example, https://www.youtube.com/watch?v=blablabla.

I want to count URLs for each site. For example

[{
    site: 'youtube.com',
    count: 25
},
{
    site: 'facebook.com',
    count: 135
}]

I used this aggregation pipeline:

db.getCollection('records').aggregate([
    {'$match': {'url': /.*youtube\.com.*/}}, // youtube for example
    {'$group': {'_id': {'site': '$url', 'count': {'$sum': 1}}}},
    {'$project': {'_id': false, 'site': '$_id.site', 'count': '$_id.count'}}
]);

which outputs:

[{
    "site" : "youtube.com/blablabla1",
    "count" : 1.0
},
{
    "site" : "youtube.com",
    "count" : 1.0
},
{
    "site" : "www.youtube.com/blablabla2",
    "count" : 1.0
},
{
    "site" : "www.youtube.com/blablabla1",
    "count" : 1.0
}]

It won't even count identical strings correctly.

What is wrong with my approach?

Airece have you tried my answer? – Maciej Kozieja Feb 25 '17 at 11:54 — Maciej Kozieja, Feb 25 '17 at 11:54
@MaciejKozieja i'm trying to understand this :) – Airece Kotina Feb 28 '17 at 18:27 — Airece Kotina, Feb 28 '17 at 18:27
If it worked for you please accept the answer ;) – Maciej Kozieja Feb 28 '17 at 22:22 — Maciej Kozieja, Feb 28 '17 at 22:22
@MaciejKozieja of cource :) – Airece Kotina Mar 01 '17 at 10:48 — Airece Kotina, Mar 01 '17 at 10:48
So what is not working? – Maciej Kozieja Mar 01 '17 at 16:46 — Maciej Kozieja, Mar 01 '17 at 16:46

score 1 · Accepted Answer · edited Jun 25 '17 at 13:05

This will count all websites:

Website name is determinated by this regex:

const testData = ['https://www.youtube.com/watch?v=UbQgXeY_zi4&list=RDUbQgXeY_zi4&index=1', 'https://www.facebook.com/maciej.kozieja.9', 'http://example.com', 'http://www.example.com']

const sites = testData.map(site => (site + '/').match(/(?:https?:\/\/)?(?:www\.)?([\w.]+)(?=\/)/)[1])

console.log(sites)

Then we have to use mapReduce function on our colection:

db.collection('links').mapReduce(
    function () {
        emit((this.site + '/').match(/(?:https?:\/\/)?(?:www\.)?([\w.]+)(?=\/)/)[1], 1)
    },
    function (key, values) {
        return values.length
    }, { out: 'websiteLinksCount' }
)

then we can do something with it

.then(x => {
    x.find({}).toArray((error, x) => {
        console.log(x) // here you have array of [{_id: siteName, value: count}]
    })
})

sorry for my long delay, i has been switched to another task. it's working, but needs some regexp editions. for example, count as same `http://example.com` and `example.com`. is possible to select regex group in there? — Airece Kotina, Apr 16 '17 at 13:20

Count records in MongoDB by regex match

1 Answers1