How to query embedded values in an object?

Question

How do I write a query that selects all objects in a collection where at least one of the keys in values has a positive value.

For example it selects this:

{
  "_id" : "kDi4C9sZpq782kKJf",
  "values": {
    "a": -1,
    "b": -1,
    "c": 1234
  }
}

and this

{
  "_id" : "kDi4C9sZpq782kKJf",
  "values": {
    "345jscf": -1,
    "6234gdr": 2341,
    "485hfls": -1
  }
}

but not

{
  "_id" : "kDi4C9sZpq782kKJf",
  "values": {
    "a134dsd": -1,
    "bdgssvs": -1,
    "c345scv": -1
  }
}

or

{
  "_id" : "kDi4C9sZpq782kKJf",
  "values": {
  }
}

Ideally I would like something along the lines of db.coll.find("values.*": {$gt: 0})

where * matches any key.

EDIT I've edited the values to be more descriptive of the problem.

Answer 1

You can use the .aggregate() method to do this:

db.collection.aggregate([
    { "$group": { 
        "_id": "$_id", 
        "valA": { "$push": "$values.a" }, 
        "valB": { "$push": "$values.b" }, 
        "valC": { "$push": "$values.c" }, 
        "values": { "$first": "$values" }
    }}, 
    { "$project": { 
        "values": 1, 
        "allvalues": { "$setUnion": [ "$valA", "$valB", "$valC" ] }
    }}, 
    { "$redact": { "$cond": [
        { "$anyElementTrue": [
            { "$map": {
                "input": "$allvalues", 
                "as": "av", 
                "in": { "$cond": [ 
                    { "$gt": [ "$$av", 0 ]}, 
                    true, 
                    false 
                ]}
            }}
        ]}, 
        "$$KEEP", 
        "$$PRUNE"
    ]}}
])

Which returns:

{
        "_id" : "kDi4C9sZpq782kKJf",
        "values" : {
                "a" : -1,
                "b" : -1,
                "c" : 1234
        },
        "allvalues" : [
                1234,
                -1
        ]
}

This query can still be simplify use the the $setUnion operator with $redact

db.collection.aggregate([
    { "$group": { 
        "_id": "$_id", 
        "valA": { "$push": "$values.a" }, 
        "valB": { "$push": "$values.b" }, 
        "valC": { "$push": "$values.c" }, 
        "values": { "$first": "$values" }
    }}, 
    { "$redact": { "$cond": [
        { "$anyElementTrue": [
            { "$map": {
                "input": { "$setUnion": [ "$valA", "$valB", "$valC" ] },
                "as": "av", 
                "in": { "$cond": [ 
                    { "$gt": [ "$$av", 0 ]}, 
                    true, 
                    false 
                ]}
            }}
        ]}, 
        "$$KEEP", 
        "$$PRUNE"
    ]}}
])

The best thing to do if you have if values keys are not fixed is change your documents structure using the "Bulk" operations for maximum efficiency.

var bulkOp = db.collection.initializeUnorderedBulkOp();
var count  = 0;
db.collection.find().forEach(function(doc) { 
    var values = doc.values; 
    var anotherValues = []; 
    for(var key in values) {       
        if(Object.prototype.hasOwnProperty.call(values, key)) {
            anotherValues.push({"name": key, "value": values[key]});     
        } 
    }
    bulkOp.find({ "_id": doc._id }).updateOne({ 
        "$set": { "values": anotherValues } 
    }); 
    count++; 
    if(count % 125 === 0) {     
        bulkOp.execute();     
        bulkOp = db.collection.initializeUnorderedBulkOp(); 
    } 
})

if(count > 0) { bulkOp.execute(); }

Now your documents look like this:

{
        "_id" : "kDi5C9sZpq782kKJf",
        "values" : [
                {
                        "name" : "a",
                        "value" : -1
                },
                {
                        "name" : "b",
                        "value" : -1
                },
                {
                        "name" : "c",
                        "value" : 1234
                }
        ]
}
{
        "_id" : "kDi4C9sZpq782kKJf",
        "values" : [
                {
                        "name" : "345jscf",
                        "value" : -1
                },
                {
                        "name" : "6234gdr",
                        "value" : 2341
                },
                {
                        "name" : "485hfls",
                        "value" : -1
                }
        ]
}

You can then use the .aggregate() method.

db.collection.aggregate([
    { "$redact": { 
         "$cond": [
             { "$anyElementTrue": [
                 { "$map": { 
                      "input": "$values", 
                      "as": "av", 
                      "in": { "$cond": [ 
                          { "$gt": ["$$av.value", 0] }, 
                          true, 
                          false
                      ]}
                 }}
             ]}, 
             "$$KEEP", 
             "$$PRUNE"
         ]
    }}
])

Answer 2

You can try like this

db.collection.find({$and:[{"values":{$ne:{}}},{$or:[{"values.a":{$gt:0}},{"values.b":{$gt:0}},{"values.c":{$gt:0}}]}]}).pretty()

In your document value is not an Array so you have to check individual element of values document.

Hope it will help