CODEWITHSUNDEEP
mongodbmongodb-query
Channa
Channa

Reputation: 3767

mongodb query inner array by property value which has object count greater than n value?

I have mongo documents as follows,

    {
    "_id" : ObjectId("5a5ee704a2032a61f3be28"),
    "companyID" : "somevalue",
    "Name" : "some name",
    "facebookEvents" : [ 
        {
            "type" : "like",
            "message" : "sdfdfdf",
            "link" : "http://www.facebook.com/140",
            "timeStamp" : 1431014457000.0,
        }, 
        {
            "type" : "comment",
            "message" : "jjjjmmmm",
            "link" : "http://www.facebook.com/140",
            "timeStamp" : 1431014457000.0
        }, 
        {
            "type" : "like",
            "message" : "Try our Android application",
            "link" : "http://www.facebook.com/140",
            "timeStamp" : 1431014457000.0
        },
, 
        {
            "type" : "like",
            "message" : "Try our Android application",
            "link" : "http://www.facebook.com/140",
            "timeStamp" : 1431014457000.0
        }
    ]
}

Is it possible to query documents that have more than two "type"="like" objects inside facebookEvents, for example, the above document is a valid result because it has more than two objects with "type"="like" inside `facebookEvents'. I tried the way as in this question explains, but it is not exactly what i want. Appriciate any help

UPDATE: mongo version: 3.4.4

I tried using aggregation as follows,

db.getCollection('customerData').aggregate( [
{ $project : { 'companyID' : 1, 'facebookEvents' : 1,'Name':1 } },
{ $unwind : "$facebookEvents" },
{ $match : { 'facebookEvents.type' : { $eq: 'like' } } },
{ $group : { _id : "$_id", likes : { $sum : 1} }},
{$match:{likes:{$gt:2}}}

] )

it returns correct objects but not with all the fields in the original object. Appriciate if someone can help me to retrieve object with all the fields(Name,facebookEvents,companyID)

Upvotes: 1

Views: 1760

Answers (3)

user9251303
user9251303

Reputation:

A simple summary. Just shows companyID with a "likes" total. Can be easily be extended to show more data as required.

db.collection.aggregate( [
    { $project : { 'companyID' : 1, 'facebookEvents' : 1, } },
    { $unwind : "$facebookEvents" },
    { $match : { 'facebookEvents.type' : { $eq: 'like' } } },
    { $group : { _id : {"companyID": "$companyID"}, likes : { $sum : 1} } },
    { $match : { 'likes' : { $gt : 2 } } }
] )

Example output:

"_id" : { "companyID" : "SomeOtherValue" }, "likes" : 2.0 }
"_id" : { "companyID" : "someValue"}, "likes" : 4.0

To show the output requested in your question, try this:

db.collection.aggregate( [
    { $unwind : "$facebookEvents" },
    { $match : { 'facebookEvents.type' : { $eq: 'like' } } },
    { $group : { _id : { companyID : "$companyID", Name : "$Name"}, 
        facebookEvents : {$push : "$facebookEvents"}, 
        TotalLikes : { $sum : 1} }},
    { $match : { likes : { $gt : 2 } } }
])

outputs:

{
    "_id" : {
        "companyID" : "someOthervalue",
        "Name" : "some name"
    },
    "facebookEvents" : [ 
        {
            "type" : "like",
            "message" : "sdfdfdf",
            "link" : "http://www.facebook.com/140",
            "timeStamp" : 1431014457000.0
        }, 
        {
            "type" : "like",
            "message" : "Try our Android application",
            "link" : "http://www.facebook.com/140",
            "timeStamp" : 1431014457000.0
        }, 
        {
            "type" : "like",
            "message" : "sdfdfdf",
            "link" : "http://www.facebook.com/140",
            "timeStamp" : 1431014457000.0
        }
    ],
    "TotalLikes" : 3.0
}

Upvotes: 2

Saravana
Saravana

Reputation: 12817

you can count number of likes in embedded document array and filter if the count is less than expected.

  1. $addFields to add field noOfLikes
  2. $map to map event to 1 or 0 based on $cond
  3. $sum to add mapped 1 or 0
  4. $match to filter all less than 2 noOfLikes

pipeline

db.fb.aggregate(
    [
        {
             $addFields : { noOfLikes : { $sum : { $map : { input : "$facebookEvents", as : "f", in : {$cond : [ { $eq : ["$$f.type" , "like"] }, 1, 0 ]} }}}}
        },
        {$match : {$expr : {$gte : ["noOfLikes" , 2]}}}
    ]
)

or using unwind/group/project as linked in the question

db.fb.aggregate(
    [
        {$unwind : "$facebookEvents"},
        {$group : {_id : {
                    _id : "$_id", 
                    companyID : "$companyID", 
                    Name : "$Name"
                    },              
                facebookEvents : {$push : "$facebookEvents"},
                noOfLikes : { $sum : {$cond : [ { $eq : ["$facebookEvents.type" , "like"] }, 1, 0 ]}}
            }
        },
        {$match : {$expr : {$gt : ["noOfLikes" , 2]}}},
        {$project : {_id : "$_id._id", companyID : "$_id.companyID", Name : "$_id.Name",facebookEvents:1  }}
    ]
)

Upvotes: 1

Alex Blex
Alex Blex

Reputation: 37108

Starting from v3.6 you can benefit from $expr operator to use aggregation expressions in find queries:

db.collection.find( { $expr: {
    $gte:[ 
        { $size: { 
            $filter: { 
                input: "$facebookEvents", 
                as: "event", 
                cond: { $eq: [ "$$event.type", "like" ] } 
            } 
        } },        
        2 
    ] 
} } )

For the earlier versions you can use aggregation pipeline to do the same in a $match stage.

Upvotes: 0

Related Questions