MongoDB Retrieve a subset of an array in a collection by specifying two fields which should match

Question

I'm using this structure to store conversations & messages:

{ "_id" : ObjectId( "4f2952d7ff4b3c36d700000d" ),
"messages" : [ 
  { "_id" : ObjectId( "4f2952d7ff4b3c36d700000c" ),
    "sender" : "4f02f16f0364c024678c0e5f",
    "receiver" : "4f02f16f0364c024678c0e61",
    "receiver_deleted" : "true",
    "sender_deleted" : "true",
    "body" : "MSG 1",
    "timestamp" : "2012-02-01T14:57:27Z" }, 
  { "_id" : ObjectId( "4f2952daff4b3c36d700000e" ),
    "sender" : "4f02f16f0364c024678c0e61",
    "receiver" : "4f02f16f0364c024678c0e5f",
    "body" : "MSG 2",
    "timestamp" : "2012-02-01T14:57:30Z" }, 
  { "_id" : ObjectId( "4f295305ff4b3c36d700000f" ),
    "sender" : "4f02f16f0364c024678c0e5f",
    "receiver" : "4f02f16f0364c024678c0e61",
    "body" : "TEST",
    "timestamp" : "2012-02-01T14:58:13Z" } ],
"participants" : [ 
  "4f02f16f0364c024678c0e5f", 
  "4f02f16f0364c024678c0e61" ],
"type" : "chat" }

When one of the sender or receiver does delete a specific message, receiver_deleted or sender_deleted gets added to the message (as you see in the first message).

Now how can I fetch a conversation with only the messages in it which haven't the sender/receiver deleted flag set?

First I tried like this:

db.conversations.find({
  "_id": ObjectId("4f2952d7ff4b3c36d700000d"),
  "participants": {"$in": ["4f02f16f0364c024678c0e5f"]},
  "$or": [
    {
      "$and": [{"messages.sender": "4f02f16f0364c024678c0e5f"}, {"messages.sender_deleted": {"$exists": false}}]
    },
    {
      "$and": [{"messages.receiver": "4f02f16f0364c024678c0e5f"}, {"messages.receiver_deleted": {"$exists": false}}]
    }
  ]
})

But this doesn't work. I also tried with $elemMatch like this:

db.conversations.find({
  "_id": ObjectId("4f2952d7ff4b3c36d700000d"),
  "participants": {"$in": ["4f02f16f0364c024678c0e5f"]},
  "$or": [
    {
      "messages": {
        "$elemMatch": {"sender": "4f02f16f0364c024678c0e5f", "sender_deleted": {"$exists": False}}
      }
    },
    {
      "messages": {
        "$elemMatch": {"receiver": "4f02f16f0364c024678c0e5f", "receiver_deleted": {"$exists": False}}
      }
    }
  ]
})

And a couple of other options with trying $and instead of $or etc. but it doesn't work.. Either it returns nothing or the whole conversation regardless of the receiver/sender deleted fields.

Thank you, Michael

score 2 · Accepted Answer · answered Feb 08 '12 at 13:52

2

It is not currently possible to retrieve a subset of an array with mongodb. You will always get the whole document back if there is a match, or nothing if there is no match. $slice allows you to return a subset, but that's based on a starting and stopping index (which is not what you want - as you want to return only the matching messages in the array).

The feature you are describing has been logged and requested here: https://jira.mongodb.org/browse/SERVER-828

answered Feb 08 '12 at 13:52

Lalit Kapoor

388
3
8

Thanks for your answer. I saw this issue already but I thought that there's maybe another way I didn't think of. – Michael Weibel Feb 08 '12 at 13:57

Didac Montero · Answer 2 · 2013-11-22T19:12:58.493

Since version 2.2 Aggregation Framework is available. You could perform your query like this:

db.expose.aggregate(    
//Find the Documents which contains the desired criteria (document level)
{
  $match: {
    $or: [
      {
        "messages.sender_deleted": "true"
      },
      {
        "messages.receiver_deleted": "true"
      }]}},
//Peels off the elements of messages array individually
{
  $unwind: "$messages"
},
//Perform the same find method, now in the embed collection level
{
  $match: {
    $or: [
      {
        "messages.sender_deleted": "true"
      },
      {
        "messages.receiver_deleted": "true"
      }]}},
//Select what to show as the result: in this case the Document id and the messages array
{
  $group: {
    _id: "$_id",
    messages: {
      $push: "$messages"
    }}});

The first match is not required, but is better to filter out as much as possible in the beginning.

MongoDB Retrieve a subset of an array in a collection by specifying two fields which should match

2 Answers2