MongoDB Nested Array Intersection Request

Question

and in advance for your help. I have a mongoDB database structured as follows:

{
  '_id' : objectID(...),

  'userID' : id,

  'movies' : [{

       'movieID' : movieID,

       'rating' : rating
   }]
 }

My question is:

I want to find a specific user that has a 'userID': 3, for example, get all the movies, then I want to get all the other users who have at least 15 or more movies with the same 'movieID', then with this group I want to select only those users who have 15 films in the likeness and there is one additional 'movieID' that I choose.

I already tried aggregation, but could not, and if I make individual requests, such as receiving all user films from a user, looping through each user film and comparing it takes a lot of time.

Any ideas?

thank

Answer 1

,

, :

{
    "_id" : ObjectId("538181738d6bd23253654690"),
    "movies": [
        { "_id": 1, "rating": 5 },
        { "_id": 2, "rating": 6 },
        { "_id": 3, "rating": 7 }
    ]
},
{
    "_id" : ObjectId("538181738d6bd23253654691"),
    "movies": [
        { "_id": 1, "rating": 5 },
        { "_id": 4, "rating": 6 },
        { "_id": 2, "rating": 7 }
    ]
},
{
    "_id" : ObjectId("538181738d6bd23253654692"),
    "movies": [
        { "_id": 2, "rating": 5 },
        { "_id": 5, "rating": 6 },
        { "_id": 6, "rating": 7 }
    ]
}

"" , , .

MongoDB 2.6 $setIntersection $size :

db.users.aggregate([

    // Match the possible documents to reduce the working set
    { "$match": {
        "_id": { "$ne": ObjectId("538181738d6bd23253654690") },
        "movies._id": { "$in": [ 1, 2, 3 ] },
        "$and": [
            { "movies": { "$not": { "$size": 1 } } }
        ]
    }},

    // Project a copy of the document if you want to keep more than `_id`
    { "$project": {
        "_id": {
            "_id": "$_id",
            "movies": "$movies"
        },
        "movies": 1,
    }},

    // Unwind the array
    { "$unwind": "$movies" },

    // Build the array back with just `_id` values
    { "$group": {
        "_id": "$_id",
        "movies": { "$push": "$movies._id" }
    }},

    // Find the "set intersection" of the two arrays
    { "$project": {
        "movies": {
            "$size": {
                "$setIntersection": [
                   [ 1, 2, 3 ],
                   "$movies"
                ]
            }
        }
    }},

    // Filter the results to those that actually match
    { "$match": { "movies": { "$gte": 2 } } }

])

MongoDB, , :

db.users.aggregate([

    // Match the possible documents to reduce the working set
    { "$match": {
        "_id": { "$ne": ObjectId("538181738d6bd23253654690") },
        "movies._id": { "$in": [ 1, 2, 3 ] },
        "$and": [
            { "movies": { "$not": { "$size": 1 } } }
        ]
    }},

    // Project a copy of the document along with the "set" to match
    { "$project": {
        "_id": {
            "_id": "$_id",
            "movies": "$movies"
        },
        "movies": 1,
        "set": { "$cond": [ 1, [ 1, 2, 3 ], 0 ] }
    }},

    // Unwind both those arrays
    { "$unwind": "$movies" },
    { "$unwind": "$set" },

    // Group back the count where both `_id` values are equal
    { "$group": {
        "_id": "$_id",
        "movies": {
           "$sum": {
               "$cond":[
                   { "$eq": [ "$movies._id", "$set" ] },
                   1,
                   0
               ]
           }
        } 
    }},

    // Filter the results to those that actually match
    { "$match": { "movies": { "$gte": 2 } } }
])

---

, , , , .

$match. , , , , , , . , "", , , "".

, , - , n, , "movies" , , n-1, . $and , 4, :

        "$and": [
            { "movies": { "$not": { "$size": 1 } } },
            { "movies": { "$not": { "$size": 2 } } },
            { "movies": { "$not": { "$size": 3 } } }
        ]

, "" , , , , n. , $size $size . , , $gt, , "". , , .

$project. , MongoDB, . -, , , _id, . "movies" .

, 2.6, , _id . $cond - "" . , MongoDB 2.6 , $literal, $cond.

$unind. - , , _id , ". , pre 2.6 " " , .

$group: MongoDB 2.6 , _id "".

Pre 2.6, " " ( ), , , . true, $cond, 1 0, false. $sum, "".

$project. MongoDB 2.6 , , , "movies" _id, , $setIntersection, . , , $size, , .

$: , , , .

---

, . 2.6 clunkier - , , , - .

, , n , , , n. n-1 "" "".