Convert ObjectID to String in mongo Aggregation

Question

I'm in this scenario right now: I have a collection X:

{
  _id:ObjectId('56edbb4d5f084a51131dd4c6'),
  userRef:ObjectId('56edbb4d5f084a51131dd4c6'),
  serialNumber:'A123123',
  ...
}

I need to aggregate all documents, grouping them by the userRef + serialNumber, so I'm trying to use concat like this:

$group: {
        _id: {
            '$concat': ['$userRef','-','$serialNumber']
        },
       ...

So basically in my aggregation in MongoDB, I need to group documents by the concatenation of a ObjectId and a string. However, It seems that $concat only accepts strings as parameters:

uncaught exception: aggregate failed: {

    "errmsg" : "exception: $concat only supports strings, not OID",
    "code" : 16702,
    "ok" : 0
}

Is there a way to convert an ObjectId to a String within an aggregation expression?

EDIT:

This question is related, but I the solution doesn't fit my problem. (Specially because I can't use ObjectId.toString() during the aggregation)

Indeed I couldn't find any ObjectId().toString() operation in Mongo's documentation, but I wonder if there's any tricky thing that can be done in this case.

Maybe `_id: [ $userRef, $serialNumber ]` works? It will not give you a string. — Knut Forkalsrud, Jun 19 '16 at 19:44

Ashh · Answer 1 · 2019-05-13T15:50:00.347

7

Now you can try with $toString aggregation which simply converts ObjectId to string

db.collection.aggregate([
    { "$addFields": {
        "userRef": { "$toString": "$userRef" }
    }},
    { "$group": {
      "_id": { "$concat": ["$userRef", "-", "$serialNumber"] }
    }}
])

You can check the output here

edited May 13 '19 at 15:50

answered Jul 08 '18 at 11:03

Ashh

36,647
11
71
96

Is this a new type of aggregation? – André Perazzi Jul 11 '18 at 18:35
1

Nice! I will test it out! Thanks for sharing! – André Perazzi Jul 11 '18 at 18:37
@AndréPerazzi I have posted the link as well... You can check it out... – Ashh Jul 11 '18 at 18:42
This is available from version 4 of MongoDB. So, if you are on some lower version, you won't be able to do this – Nenad Jovicic Oct 06 '20 at 13:44

score 6 · Accepted Answer · edited Feb 24 '17 at 06:40

6

I couldn't find a way to do what I wanted, so instead, I created a MapReduce function that, in the end, generated the keys the way I wanted to (concatenating other keys).

At the end, it looked something like this:

db.collection('myCollection').mapReduce(
    function() {
        emit(
            this.userRef.str + '-' + this.serialNumber , {
                count: 1,
                whateverValue1:this.value1,
                whateverValue2:this.value2,
                ...
            }
        )
    },
    function(key, values) {
       var reduce = {}
       .... my reduce function....
        return reduce
    }, {
        query: {
            ...filters_here....
        },
        out: 'name_of_output_collection'
    }
);

edited Feb 24 '17 at 06:40

Pang

8,605
144
77
113

answered Jun 21 '16 at 15:49

André Perazzi

999
1
10
26

1

can you please tell me what is the code you wrote ?? – Sudhanshu Gaur Dec 07 '16 at 00:34
2

@SudhanshuGaur see if this helps. I edited the comment – André Perazzi Dec 07 '16 at 21:52
Thank you for your answer actually i was reading about aggregation and map reduce and there i found that map reduce is a lot times slower then aggregation thatisy i thought about to tell you it would be better if you coulf do this in agregation. – Sudhanshu Gaur Dec 08 '16 at 12:07
1

Yeah I know... But I never found a solution using aggregation :/ – André Perazzi Jan 03 '17 at 16:57

score 2 · Answer 3 · answered Feb 22 '20 at 07:54

You can simply use $toString to apply $concat in aggregation on ObjectIDs in the following way -

$group: {
    '_id': {
        '$concat': [
            { '$toString' : '$userRef' },
            '-',
            { '$toString' : '$serialNumber'}
        ]
    },
}

score 0 · Answer 4 · answered Feb 24 '17 at 06:07

I think you may try to resolve it by using an Array which contains both fields:

{$project:{newkey:['$userRef','$serialNumber']},{$match:{newkey:{$in:filterArray}}}}

this may match the data with both fields to the filter. Please notice that the data in the newkey array should have the same data type with the filterArray elements.

score -2 · Answer 5 · edited Feb 24 '17 at 08:13

You can use $substr https://docs.mongodb.com/manual/reference/operator/aggregation/substr/#exp._S_substr to cast any object to string before $concat.

This is a sample of code that's working for me.

group_id_i['_id'] = {
    '$concat' => [
        { '$substr' => [ {'$year' => '$t'}, 0, -1] }, '-',
        { '$substr' => [ {'$month' => '$t'}, 0, -1] }, '-',
        { '$substr' => [ {'$dayOfMonth' => '$t'}, 0, -1] }
    ]
}

Where t is DateTime field, this aggregation returns data like so.

{
    "_id" => "28-9-2016",
    "i" => 2
}

Convert ObjectID to String in mongo Aggregation

5 Answers5

Linked