2

Look the following documents in a contacts collection on MongoDB 3.4:

{
    "_id" : ObjectId("58f045526320ef24fc61fdb2"),
    "name" : "John Doe",
    "tags" : [
        {
            "name": "tagA", 
            "created_at": ISODate("2017-01-27T10:30:00Z")
        },
        {
            "name": "tagB", 
            "created_at": ISODate("2017-01-28T13:30:00Z")
        }
    ],
},
{
    "_id" : ObjectId("58f045526320ef24fc61fdb3"),
    "name" : "Johnny Doe",
    "tags" : [
        {
            "name": "tagA", 
            "created_at": ISODate("2016-12-21T19:30:00Z")
        },
        {
            "name": "tagC", 
            "created_at": ISODate("2017-01-28T13:30:00Z")
        }
    ],
}
.
. 
.

Is there any query using only find method (nor aggregate framework, nor $where) that can return:

  1. The contacts who have all the following tags: [tagA, tagC]
  2. The contacts who don't have all of these tags: [tagC, tagD]
  3. The contacts with all tags in 2017
BenMorel
  • 34,448
  • 50
  • 182
  • 322
Delmo
  • 2,188
  • 2
  • 20
  • 29
  • 1
    what you tried so far? and what is the error you are getting? – AshokGK Apr 18 '17 at 12:42
  • I just can't find a query to return the contacts that fulfill those conditions. Note that it is not the classic query that return for example contacts who have tagA or tagC. – Delmo Apr 18 '17 at 12:51
  • 1
    Take a look at http://stackoverflow.com/questions/23595023/check-if-every-element-in-array-matches-condition. It has similar examples that do an "exact match" - meaning that those answers show how to find contact who have an exact set of tags. For example to answer question 1, `db.collection.find({"tags": {"$not": {"$elemMatch": {"name": {"$nin": ["tagA", "tagC"]}}}}})` – Jeff J Apr 18 '17 at 14:12
  • Thanks @JeffJ I guess now I can write the three queries above using the key point you commented from the other stackoverflow post: **apply $not over the complement of a subset**. You can now go ahead to answer the question putting the query for every case so that I can accept the answer as the right one. – Delmo Apr 18 '17 at 16:17
  • 1
    I really don't have good data to test. You can try something like `db.collection.find({ "tags.name": { $all: [ "tagA" , "tagB" ], $nin: [ "tagC" , "tagD" ]},"tags.created_at":{$gte: ISODate("2017-01-01"),$lt:ISODate("2018-01-01")}});` – s7vr Apr 18 '17 at 20:36
  • Thanks @Veeram, your answers about the first two questions are really nice. They made me realize that the queries are easier than I thought. But the last answer about `created_at` field is not the right one because it will return the documents with **at least** one tag in 2017, not **all** the tags, see the @JeffJ's answer which I guess is the right one. So I encourage you to fix the last query and add a new answer so I can accept it as the correct answer. – Delmo Apr 21 '17 at 23:39

1 Answers1

2

I found a similar question at Check if every element in array matches condition.

To find the contacts who have all the following tags: [tagA, tagC], you can use a double negative to find the documents that do NOT have elements NOT in [tagA, tagC]

db.collection.find({"tags": {"$not": {"$elemMatch": {"name": {"$nin": ["tagA", "tagC"]}}}}})

To find the contacts who don't have all of these tags: [tagC, tagD]

db.collection.find({"tags": {"$elemMatch": {"name": {"$nin": ["tagC", "tagD"]}}}})

A similar query can be done for the date as well, although using $or

db.collection.find({"tags": {"$not": {"$elemMatch": {"$or": [ 
    {"createdAt": {"$lt": ISODate("2017-01-01T00:00:00.000Z")}},
    {"createdAt": {"$gt": ISODate("2017-12-31T23:59:59.999Z")}}]}}}})
Community
  • 1
  • 1
Jeff J
  • 553
  • 2
  • 9
  • I voted up your answer but the @Veeram solution for the two first queries are the best, IMHO. Note that they are simpler, therefore more efficient. That is why I will accept his answer as the right one when he adds the answer officially. However, thanks for the good point about the negation of the complement which is the right answer for the third query (in my opinion). – Delmo Apr 21 '17 at 23:46