mongodb aggregation conditional adding field based on value in array

Viewed 15831

please excuse the title. could find a better description for what iam trying to do.

I have a collection of messages which stores the following information

  • code: a unique identification code of the message
  • from: phone number the message was sent from
  • to: phone number the message was sent to
  • message: the message text
  • readings: an array of ObjectIds. The ids reference documents in another collection names "users". if an ObjectId is here it means, that this message has been read by that particular user.

Example Data

{
    "_id" : ObjectId("59ba30c95869d32a803e4c4d"),
    "code" : "SM54c9366e9b8544e89bdcf2ee841adea7",
    "from" : "+49157xxxxxxx",
    "to" : "+49160xxxxxxxx",
    "message" : "xxxxxxxx",
    "createdAt" : ISODate("2017-09-14T07:33:39.000Z"),
    "lastModifiedAt" : ISODate("2017-09-14T07:33:32.324Z"),
    "status" : "delivered",
    "room" : ObjectId("59bfa293bd7717251cecfae7"),
    "readings" : [ 
        ObjectId("59c25751dcfdaf2944ee2fae"), 
        ObjectId("59c25751dcfdaf2944e32fae")
    ],
}

/* 2 */
{
    "_id" : ObjectId("59ba3270f53b7f2fb4fa807f"),
    "code" : "SM04585672d02644018e3ff466d73c571d",
    "from" : "+49xxxxxxx",
    "to" : "+49xxxxxxxx",
    "message" : "xxxxxxx",
    "createdAt" : ISODate("2017-09-14T07:40:42.000Z"),
    "lastModifiedAt" : ISODate("2017-09-14T07:40:34.338Z"),
    "status" : "delivered",
    "room" : ObjectId("59bfa293bd7717251cecfae7"),
    "readings" : [ 
        ObjectId("59c25751dcfdaf2944ee2fae")
    ],
}

Want i want to achieve is that a message gets an additional field "hasRead" if a specific user has read the message.

Here is the result i want to achieve

{
    "_id" : ObjectId("59ba30c95869d32a803e4c4d"),
    "code" : "SM54c9366e9b8544e89bdcf2ee841adea7",
    "to" : "+491606983534",
    "message" : "Schau mer mal",
    "createdAt" : ISODate("2017-09-14T07:33:39.000Z"),
    "lastModifiedAt" : ISODate("2017-09-14T07:33:32.324Z"),
    "status" : "delivered",
    "room" : ObjectId("59bfa293bd7717251cecfae7"),
    "hasRead" : true
}

/* 2 */
{
    "_id" : ObjectId("59ba3270f53b7f2fb4fa807f"),
    "code" : "SM04585672d02644018e3ff466d73c571d",
    "to" : "+491606983534",
    "message" : "Schau mer mal",
    "createdAt" : ISODate("2017-09-14T07:40:42.000Z"),
    "lastModifiedAt" : ISODate("2017-09-14T07:40:34.338Z"),
    "status" : "delivered",
    "room" : ObjectId("59bfa293bd7717251cecfae7"),
    "hasRead" : true
}

I constructed an aggregation with the following stages but it looks so BIG for such a simple task and i wonder if there is a more elegant, lighter way to do so ?

The stages are:

  • $addFields: Checks if the readings array is 0. if 0 it adds a dummy ObjectId, else it sets the readings array
  • $unwind: Unwind the readings array
  • $addFields: adds a field "hasRead" upon checking if a specific ObjectId matches the "readings" field. True if equal else false
  • $group: Group by all fields except the "hasRead" field, "hasRead" based in $max hasRead
  • $project: Constructing the result to make it a flat object.

And here is my code:

db.getCollection('sms').aggregate([
{ $addFields: {
    "readings": {
        "$cond": {
            if: { $or: [ { "$gt": [ {"$size": "$readings"},0] } ]} ,
            then: "$readings",
            else: [ObjectId("000000000000000000000000")]
        }
    }
}},
{ $unwind: "$readings" },
{ $addFields: { 
    "hasRead": { 
        $cond: { 
            if: { 
                $eq: ["$readings", ObjectId("59c25751dcfdaf2944ee2fae")] 
            }, 
            then: true, 
            else: false 
        }
    } 
  } 
},
{ $group: {
    _id: { 
        _id: "$_id",
        code: "$code",
        from: "$from",
        to: "$to",
        message: "$message",
        createdAt: "$createdAt",
        lastModifiedAt: "$lastModifiedAt",
        room: "$room"
    },
    hasRead: { $max: "$hasRead" }
}},

{ $project: {
    "_id": "$_id._id",
    "code": "$_id.code",
    "from": "$_id.from",
    "to": "$_id.to",
    "message": "$_id.message",
    "createdAt": "$_id.createdAt",
    "lastModifiedAt": "$_id.lastModifiedAt",
    "room": "$_id.room",
    "hasRead": "$hasRead"
}}
])

After browsing thru answers Neil (see comment) gave to another questioni could simplfiy the query to this:

db.getCollection('sms').aggregate([
    { "$addFields": {
        "hasRead" : {
            "$filter": {
                "input": { "$setUnion": [ "$readings", []] },
                "as": "o",
                "cond" : {
                    "$eq": [ "$$o",ObjectId("59c25751dcfdaf2944ee2fae")]
                    }
                }
            }
        }
    },
    { "$project": {
        "_id": 1,
        "code": 1,
        "to": 1,
        "message": 1,
        "createdAt": 1,
        "lastModifiedAt" : 1,
        "status": 1,
        "room": 1,
        "hasRead": { 
            "$cond": {
            if: { $or: [ { "$gt": [ {"$size": "$readings"},0] } ]} ,
            then: true,
            else: false
            }
        }
    }
}
])
1 Answers

Way too late for this, but you can simply write:

db.getCollection("sms").aggregate([
  {
    $project: {
      _id: 1,
      code: 1,
      to: 1,
      message: 1,
      createdAt: 1,
      lastModifiedAt: 1,
      status: 1,
      room: 1,
      hasRead: {
        $in: [ObjectId("59c25751dcfdaf2944ee2fae"), "$readings"],
      },
    },
  },
]);

often the simplest solution is the correct one :)

Related