MongoDB aggregation conditional adding field based on value in array

Question

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:

{
    "_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" : "[phone]",
    "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" : "[phone]",
    "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
            }
        }
    }
}
])

Huai

The best way to describe something ( particularly when you are not sure what to describe ) is to 1. Show a small sample of data you have 2. Show the result you want to achieve. 3. Show the attempt and a little explanation of the process. You're missing 1 & 2 here, and you probably are aware that 3 may not be the clearest of descriptions. GIve us 1 & 2 and you might get your question answered. – Ecuador

So it looks like all you really want is a single $addFields and $in. But why you would not simply iterate the cursor results and use something like Array.includes() in client code is somewhat astounding. You've essentially found the most complicated way to approach a very simple thing. – Ecuador

Try this db.getCollection('sms').aggregate([{ "$addFields": {"hasRead" : {"$in":[ ObjectId("59c25751dcfdaf2944ee2fae"), "$readings"] } } }]) – Goebel

@veeram. Thank you very much – Huai

Answer 1

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 :)

Jaborandi

Answer 2

Although @Andrea's answer is correct but in latest versions of mongodb you don't need to use aggregate

use find query and project

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

Gorman