Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Mongo - Array query, only find where all elements match

I'm trying to create a mongo query which will return results where all arrays have a specific element set to false.

An example data record :-

images: [
    {
        id: ObjectId("516bef7fc05e877b31000000"),
        primary: true
    },
    {
        id: ObjectId("516bef2ac05e879622000000"),
        primary: false
    },
    {
        id: ObjectId("516beeb7c05e879e2a000000"),
        primary: false
    }
],
name: "test",
etc: "etc"

I only wish to find documents where all primary fields are set to false however normally (using no query selectors or elemMatch) mongo will return this document because at least 1 of the array elements match.

How would I make mongo only return documents where they all match my search parameters?

Many thanks.

like image 687
user1954882 Avatar asked Aug 08 '13 10:08

user1954882


1 Answers

You can do this with the aggregation framework quite easily:

db.so.aggregate( [
    { $unwind: "$images" },
    { $group: { 
        _id: '$_id', 
        all: { $sum: 1 },
        all_primary: { $sum: { $cond: [ { $eq: [ '$images.primary', true ] }, 1, 0 ] } },
        images: { $push: '$images' },
        name: { $first: '$name' },
        etc: { $first: '$etc' },
    } },
    { $project: {
        _id: 1,
        images: 1,
        name: 1,
        etc: 1,
        same: { $cond: [ { $eq: [ '$all', '$all_primary' ] }, 1, 0 ] }
    } },
    { $match: { 'same' : 1 } } 
] );

With this as input:

{
    "_id" : ObjectId("5203730bf8eaa52a846ebc3e"),
    "images" : [
        {
            "id" : ObjectId("516bef7fc05e877b31000000"),
            "primary" : true
        },
        {
            "id" : ObjectId("516bef2ac05e879622010000"),
            "primary" : true
        },
        {
            "id" : ObjectId("516beeb7c05e879e2a000010"),
            "primary" : true
        }
    ],
    "name" : "Derick",
    "Etc" : true
}
{
    "_id" : ObjectId("52037315f8eaa52a846ebc3f"),
    "images" : [
        {
            "id" : ObjectId("516bef7fc05e877b31000000"),
            "primary" : true
        },
        {
            "id" : ObjectId("516bef2ac05e879622010000"),
            "primary" : true
        },
        {
            "id" : ObjectId("516beeb7c05e879e2a000020"),
            "primary" : false
        }
    ],
    "name" : "James",
    "Etc" : true
}
{
    "_id" : ObjectId("520373621a78238235b6ffbf"),
    "images" : [
        {
            "id" : ObjectId("516bef7fc05e877b31000000"),
            "primary" : true
        },
        {
            "id" : ObjectId("516bef2ac05e879622010000"),
            "primary" : true
        },
        {
            "id" : ObjectId("516beeb7c05e879e2a000020"),
            "primary" : false
        }
    ],
    "name" : "James",
    "etc" : true
}
{
    "_id" : ObjectId("5203736b1a78238235b6ffc0"),
    "images" : [
        {
            "id" : ObjectId("516bef7fc05e877b31000000"),
            "primary" : true
        },
        {
            "id" : ObjectId("516bef2ac05e879622010000"),
            "primary" : true
        },
        {
            "id" : ObjectId("516beeb7c05e879e2a000020"),
            "primary" : true
        }
    ],
    "name" : "James",
    "etc" : true
}

This outputs:

{
    "result" : [
        {
            "_id" : ObjectId("5203736b1a78238235b6ffc0"),
            "images" : [
                {
                    "id" : ObjectId("516bef7fc05e877b31000000"),
                    "primary" : true
                },
                {
                    "id" : ObjectId("516bef2ac05e879622010000"),
                    "primary" : true
                },
                {
                    "id" : ObjectId("516beeb7c05e879e2a000020"),
                    "primary" : true
                }
            ],
            "name" : "James",
            "etc" : true,
            "same" : 1
        },
        {
            "_id" : ObjectId("5203730bf8eaa52a846ebc3e"),
            "images" : [
                {
                    "id" : ObjectId("516bef7fc05e877b31000000"),
                    "primary" : true
                },
                {
                    "id" : ObjectId("516bef2ac05e879622010000"),
                    "primary" : true
                },
                {
                    "id" : ObjectId("516beeb7c05e879e2a000010"),
                    "primary" : true
                }
            ],
            "name" : "Derick",
            "etc" : null,
            "same" : 1
        }
    ],
    "ok" : 1
}
like image 100
Derick Avatar answered Oct 14 '22 01:10

Derick