Mongodb returning multiple sub array results and excluding other returned results

Question

I have the following collection with a node array:

{
"_id" : ObjectId("55acf6187d4c31475417fa62"),
"node" : [
    {
        "-id" : "29331496",
        "-uid" : "1899168",
        "-changeset" : "26313303",
        "-lat" : "-37.6104102",
        "-lon" : "144.9459817",
        "-timestamp" : "2014-10-25T03:36:51Z",
        "-user" : "heyitsstevo",
        "-visible" : "true",
        "-version" : "6"
    },
    {
        "-id" : "29331497",
        "-uid" : "1899168",
        "-version" : "2",
        "-lon" : "144.9451088",
        "-timestamp" : "2014-10-25T03:36:51Z",
        "-user" : "heyitsstevo",
        "-visible" : "true",
        "-changeset" : "26313303",
        "-lat" : "-37.601881"
    },
    {
        "-id" : "29331498",
        "-timestamp" : "2014-10-25T03:36:51Z",
        "-version" : "3",
        "-uid" : "1899168",
        "-user" : "heyitsstevo",
        "-visible" : "true",
        "-changeset" : "26313303",
        "-lat" : "-37.6011267",
        "-lon" : "144.9448575"
    },
    {
        "-lon" : "144.943302",
        "-id" : "29331499",
        "-timestamp" : "2011-11-23T03:21:40Z",
        "-user" : "melb_guy",
        "-version" : "9",
        "-uid" : "11111",
        "-visible" : "true",
        "-changeset" : "9916439",
        "-lat" : "-37.5983291"
    },
    {
        "-id" : "60648717",
        "-uid" : "46482",
        "-user" : "Zulu99",
        "-lat" : "-37.6796337",
        "-lon" : "144.9220639",
        "-timestamp" : "2009-12-12T21:29:36Z",
        "-visible" : "true",
        "-version" : "2",
        "-changeset" : "3358816"
    },
    {
        "-id" : "60648718",
        "-timestamp" : "2009-12-12T21:29:35Z",
        "-uid" : "46482",
        "-version" : "2",
        "-changeset" : "3358816",
        "-user" : "Zulu99",
        "-visible" : "true",
        "-lat" : "-37.6787103",
        "-lon" : "144.9224609"
    },
    {
        "-id" : "60648719",
        "-timestamp" : "2009-12-12T21:28:58Z",
        "-user" : "Leon K",
        "-version" : "2",
        "-changeset" : "3358816",
        "-uid" : "Zulu99",
        "-visible" : "true",
        "-lat" : "-37.677841",
        "-lon" : "144.9227344"
    }
]
}

How do I return all nodes that have the "-user" = "Zulu99" and exclude anything else?

I have tried the following query but it only returns the first node it finds with "Zulu99":

db.osm.find( { }, { node: {$elemMatch: {'-user': 'Zulu99'}}} )

possible duplicate of [Retrieve only the queried element in an object array in MongoDB collection](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) All answers mentioning aggregate with all possible examples. — Blakes Seven, Sep 01 '15 at 01:33

styvane · Answer 1 · 2015-12-11T20:27:42.973

What you need is "aggregation" and the $map and the $setDifference operators

db.collection.aggregate([ 
    { 
        "$match": { 
            "node.-user": "Zulu99", 
            "node.-lat": "-37.6787103" 
        }
    },
    { 
        "$project": { 
            "node": { 
                "$setDifference": [{ 
                    "$map": { 
                        "input": "$node",
                        "as": "n", 
                        "in": { 
                            "$cond": [
                                { "$eq": [ "$$n.-user", "Zulu99" ]}, 
                                "$$n", 
                                false 
                            ]
                        } 
                    }
                }, 
                [false]]
            }
        }
    }
])

From MongoDB 3.2 you can use the $filter operator

db.collection.aggregate([
    { 
        "$match":  { 
            "node.-user": "Zulu99",
            "node.-lat": "-37.6787103"
        }
    }, 
    { "$project": {
        "node": {
            "$filter": {
                "input": "$node",
                "as": "n",
                "cond": { "$eq": [ "$$node.n", "Zulu99" ] }
            }
        }
    }}
])

which yields:

{
        "_id" : ObjectId("55acf6187d4c31475417fa62"),
        "node" : [
                {
                        "-id" : "60648717",
                        "-uid" : "46482",
                        "-user" : "Zulu99",
                        "-lat" : "-37.6796337",
                        "-lon" : "144.9220639",
                        "-timestamp" : "2009-12-12T21:29:36Z",
                        "-visible" : "true",
                        "-version" : "2",
                        "-changeset" : "3358816"
                },
                {
                        "-id" : "60648718",
                        "-timestamp" : "2009-12-12T21:29:35Z",
                        "-uid" : "46482",
                        "-version" : "2",
                        "-changeset" : "3358816",
                        "-user" : "Zulu99",
                        "-visible" : "true",
                        "-lat" : "-37.6787103",
                        "-lon" : "144.9224609"
                }
        ]
}

Neo-coder · Accepted Answer · 2015-09-05T07:45:19.950

If your documents size more then aggregation $unwind creates Cartesian Product problem i.e every array object creates multiple documents so it creates slow down aggregation query, If you want avoid this problem use $redact as like below :

db.collectionName.aggregate({
  "$match": {
    "node.-user": "Zulu99"
  }
}, {
  "$redact": {
    "$cond": {
      "if": {
        "$eq": [{
          "$ifNull": ["$-user", "Zulu99"]
        }, "Zulu99"]
      },
      "then": "$$DESCEND",
      "else": "$$PRUNE"
    }
  }
}).pretty()

EDIT

If you want to multiple conditions then used $cond with $and like this

db.collectionName.aggregate({
  "$match": {
    "node.-user": "Zulu99",
    "node.-lat": "-37.6787103"
  }
}, {
  "$redact": {
    "$cond": {
      "if": {
        "$and": [{
          "$eq": [{
            "$ifNull": ["$-user", "Zulu99"]
          }, "Zulu99"]
        }, {
          "$eq": [{
            "$ifNull": ["$-lat", "-37.6787103"]
          }, "-37.6787103"]
        }]
      },
      "then": "$$DESCEND",
      "else": "$$PRUNE"
    }
  }
}).pretty()

In above query you get that lat check whether is equal to -37.6787103 or not, if you want to check -lat and -lon to $gt or $lt then first you should changed your data type of both lat and lon in your documents it looks like String so first changed data type String to number then used comparison operator.

Second things if you want only matched node array object then used group after readact like this :

db.collectionName.aggregate({
  "$match": {
    "node.-user": "Zulu99",
    "node.-lat": "-37.6787103"
  }
}, {
  "$redact": {
    "$cond": {
      "if": {
        "$and": [{
          "$eq": [{
            "$ifNull": ["$-user", "Zulu99"]
          }, "Zulu99"]
        }, {
          "$eq": [{
            "$ifNull": ["$-lat", "-37.6787103"]
          }, "-37.6787103"]
        }]
      },
      "then": "$$DESCEND",
      "else": "$$PRUNE"
    }
  }
}, {
  "$group": {
    "_id": "$_id",
    "node": {
      "$first": "$node" // use $first 
    }
  }
}).pretty()

New Edit

If you want to find out gte and lte condition then follow this aggregation :

db.collectionName.aggregate({
"$project": {
 "check": {
  "$setDifference": [{
      "$map": {
        "input": "$node",
        "as": "node1",
        "in": {
          "$cond": {
            "if": {
              "$and": [{
                "$and": [{
                  "$gte": ["$$node1.-lon", 100]
                }, {
                  "$lte": ["$$node1.-lon", 150]
                }]
              }, {
                "$and": [{
                  "$gte": ["$$node1.-lat", -50]
                }, {
                  "$lte": ["$$node1.-lat", -10]
                }]
              }]
            },
            "then": "$$node1",
            "else": false
          }
        }
      }
    },
    [false]
  ]
 }
}
}).pretty()

Thanks that works except I've got other arrays in the collection that also get returned. Is it possible to only return the node array and exclude everything else? Also will it be possible to search on multiple fields i.e. return all nodes that have "-lat" > X and "-lon" > Y ? — Kubba, Sep 01 '15 at 11:09
Thanks for the help. That works fine except I'm confused as to where I need to place the comparison operators. I've converted my data types to numbers like you mention. Now how can I modify the query to only return node that have "-lon" between 100 and 150 and "-lat" between -50 and -10 ? Forget about the user, just wana return nodes that are with in these latitude and longitude values. — Kubba, Sep 05 '15 at 06:08
@Kubba I added new aggregation query using `$map` in aggregation used this aggregation to find out `gt and lt` `lon and lat`. Very soon I will update it with `$redact` in aggregation. — Neo-coder, Sep 05 '15 at 07:46
I've been trying to work this out for weeks! Thank you so much, really appreciate your help. — Kubba, Sep 05 '15 at 09:43
@Kubba you should avoid [chameleon question](http://meta.stackexchange.com/questions/43478/exit-strategies-for-chameleon-questions) and ask new question. chameleon question can invalidate existing answer. — styvane, Sep 05 '15 at 12:58
@Kubba also the reason your asking this is because you didn't read my [answer](http://stackoverflow.com/a/32316586/3100115) and the possible [duplicate](http://stackoverflow.com/questions/3985214/retrieve-only-the-queried-element-in-an-object-array-in-mongodb-collection) to your question — styvane, Sep 05 '15 at 13:09
@user3100115 Thanks for that, I'm new to this. I actually read your answer first. However, it did not work for me. — Kubba, Sep 05 '15 at 14:55

Mongodb returning multiple sub array results and excluding other returned results

2 Answers2

Linked

Related