How to filter aggregate over the attributes of a subdocument?

Question

I am currently using MongoDB as returner backend on SaltStack and I need to query the database, for example to get the list of the steps of a job that failed, each jobReturn is an object like this (here is a part of it):

{
    "_id" : ObjectId("595d12c99fee8e5d23f344a8"),
    "fun" : "state.apply",
    "jid" : "20170705162344866073",
    "return" : {
        "svn_|-Template tideway should have the revision HEAD active for the staging environment (Live)_|-http://svn-svc-xxx-es/staging/xxx-template-tideway/trunk_|-latest" : {
            "comment" : "Checked out revision 456.",
            "name" : "http://svn.svc.xxx.es/staging/xxx-template-tideway/trunk",
            "start_time" : "18:24:13.939000",
            "result" : true,
            "duration" : 752.0,
            "__run_num__" : 35,
            "changes" : {
                "new" : "http://svn.svc.xxx.es/staging/xxx-template-tideway/trunk",
                "revision" : "456\r"
            },
            "__id__" : "Template tideway should have the revision HEAD active for the staging environment (Live)"
        },
        "win_dacl_|-The application user should have full access to the application directories_|-C:\\inetpub\\wwwroot_|-present" : {
            "comment" : "",
            "name" : "C:\\inetpub\\wwwroot",
            "start_time" : "18:24:39.668000",
            "result" : true,
            "duration" : 7.0,
            "__run_num__" : 61,
            "changes" : {},
            "__id__" : "The application user should have full access to the application directories"
        },
        "svn_|-Template capucine should have the revision HEAD active for the staging environment (Live)_|-http://svn-svc-xxx-es/staging/xxx-template-capucine/trunk_|-latest" : {
            "comment" : "Checked out revision 456.",
            "name" : "http://svn.svc.xxx.es/staging/xxx-template-capucine/trunk",
            "start_time" : "18:24:07.544000",
            "result" : true,
            "duration" : 673.0,
            "__run_num__" : 23,
            "changes" : {
                "new" : "http://svn.svc.xxx.es/staging/xxx-template-capucine/trunk",
                "revision" : "456\r"
            },
            "__id__" : "Template capucine should have the revision HEAD active for the staging environment (Live)"
        },
        .....
    }
}

Here for example, I need to get this object but with all the "return" objects that have "result": false

I tried to use $project but it's only working on list.

I can't modify the way the application is putting the data. I agree that a list of objects would have been a better solution in this schema.

What would be the best way to do this ?

See if this helps https://stackoverflow.com/questions/44882150/collect-distinct-field-names-at-nested-level-with-specific-condition/44883850#44883850 — s7vr, Jul 07 '17 at 14:53

score 1 · Accepted Answer · edited Jul 09 '17 at 23:59

1

I found a solution using the $objectToArray expression in MongoDB 3.4.4+:

db.saltReturns.aggregate([
    { $match: { "fun": { $eq: "state.apply" }}},
    { $project: {
        matches: {
            $filter: {
                input: { $objectToArray: "$return" },
                as: "return",
                cond: { $eq: ["$$return.v.result", true] }
            }
        }
    }}
])

edited Jul 09 '17 at 23:59

Stennie

63,885
14
149
175

answered Jul 07 '17 at 15:39

Kedare

1,289
16
31

2

You really should also learn to use `$objectToArray` as the argument to the `"input"` option of `$filter` here, instead of using a separate `$project` stage.The reason for this is what "actually happens" is a `$project` runs a pass through "all your data" in order to make the change. So you are basically running through all results twice. The other lesson is we are now "coercing to an array" to actually do something constructive with it. This is an indicator that your data should be structured like that in the first place. Which gives a performant query. – Neil Lunn Jul 09 '17 at 09:30

How to filter aggregate over the attributes of a subdocument?

1 Answers1