MongoDB query returning no results

Question

I'm new to mongo and am trying to do a very simple query in this collection:

{
    "_id" : ObjectId("gdrgrdgrdgdr"),
    "administrators" : {
        "-HGFsfes" : {
            "name" : "Jose",
            "phone" : NumberLong(124324)
        },
        "-HGFsfqs" : {
            "name" : "Peter",
            "phone" : "+43242342"
        }
    },
    "countries" : {
        "-dgfgrdg : {
            "lang" : "en",
            "name" : "Canada"
        },
        "-grdgrdg" : {
            "lang" : "en",
            "name" : "USA"
        }
    }
}

How do I make a query that returns the results of administrators with name like "%Jos%" for example.

What I did until now is this: db.getCollection('coll').find({ "administrators.name": /Jos/});

And variations of this. But every thing I tried returns zero results.

What am I doing wrong?

Thanks in advance!

Your query is omitting the `"-HGFsfes"` part of the key to search. You have to include that. So `.find({ "administrators.-HGFsfes.name": /Jos/})` — JohnnyHK, Jul 05 '18 at 19:53
But how do I search all of the objects inside "administrators"? — WilsonPena, Jul 05 '18 at 19:53
It's messy. You should probably make `administrators` an array instead. — JohnnyHK, Jul 05 '18 at 19:56
Then your question is a dupe of https://stackoverflow.com/questions/19802502/mongodb-query-help-query-on-values-of-any-key-in-a-sub-object — JohnnyHK, Jul 05 '18 at 20:00

Yogen Rai · Answer 1 · 2018-07-05T20:23:39.167

1

You need to use query like this:

db.collection_name.find({})

So if your collection name is coll, then it would be:

db.coll.find({"administrators.-HGFsfes.name": /Jos/});

Look this for like query in mongo.

Also, try with regex pattern like this:

db.coll.find({"administrators..-HGFsfes.name": {"$regex":"Jos", "$options":"i"}}});

It will give you only one result because your data is not an array as below in screenshot:

If you want multiple results, then you need to restructure your data.

edited Jul 05 '18 at 20:23

answered Jul 05 '18 at 19:36

Yogen Rai

2,961
3
25
37

You helped me a bit, but now I'm getting every single result every time. In both queries you posted I get the same result. – WilsonPena Jul 05 '18 at 19:40
Updated to return like query result!! Please have a look – Yogen Rai Jul 05 '18 at 19:41
Now I don't get any results for some reason – WilsonPena Jul 05 '18 at 19:42
Look for the link i have attached for like query in mongo – Yogen Rai Jul 05 '18 at 19:47
Still zero results – WilsonPena Jul 05 '18 at 19:52
i am getting result @WilsonPena,, would you try again? may be column mismatched – Yogen Rai Jul 05 '18 at 20:03
I'm trying and still no results – WilsonPena Jul 05 '18 at 20:04
this is working man!! `db.coll.find({"administrators.name": /Jos/});` – Yogen Rai Jul 05 '18 at 20:09
This is returning zero results. I'm testing in robomongo. maybe is the quotes or something? I'm pretty lost here. – WilsonPena Jul 05 '18 at 20:11
Hey @WilsonPena, please look into updated answer for explantion – Yogen Rai Jul 05 '18 at 20:24

score 1 · Answer 2 · answered Jul 05 '18 at 19:58

1

Your mistake is that administrators is not an array, but an object with fields that are themselves objects with name field. Right query will be

{ "administrators.-HGFsfes.name": /Jos/}

Unfortunatelly this way you're only querying -HGFsfes name field, not other administrator name field.

To achieve what you want, the only thing to do is to replace administrators object by an array, so your document will look like this :

    { 
    "administrators" : [
        {
            "id" : "-HGFsfes", 
            "name" : "Jose", 
            "phone" : 124324
        }, 
        {
            "id" : "-HGFsfqs", 
            "name" : "Peter", 
            "phone" : "+43242342"
        }
    ], 
 countries : ...
}

This way your query will work.

BUT it will return documents where at least one entry in administrators array has the matching name field. To return only administrator matching element, and not whole document, check this question and my answer for unwind/match/group aggregation pipeline.

answered Jul 05 '18 at 19:58

matthPen

4,253
1
16
16

But I did the query you wrote, to return the specific administrator, and robomongo returned me the whole collection. Am I looking at this wrong? – WilsonPena Jul 05 '18 at 20:01
which query? on which data schema? – matthPen Jul 05 '18 at 21:28
I changed the schema to make administrators an array and it didn't seem to work – WilsonPena Jul 09 '18 at 10:30
can you provide your new schema? if it's identical to this one, your query db.getCollection('coll').find({ "administrators.name": /Jos/}); must work – matthPen Jul 10 '18 at 10:54

score 1 · Answer 3 · answered Jul 05 '18 at 21:53

Ok, think i've found a better solution for you, with aggregation framework. Run the following query on your current collection, will return you all administrators with name "LIKE" jos (case insensitive with i option) :

db.test1.aggregate(
    [
        {
            $project: {
              administrators:{    $objectToArray: "$administrators"}
            }
        },
        {
            $unwind: {
                path : "$administrators"
            }
        },
        {
            $replaceRoot: {
            newRoot:"$administrators"
            }
        },
        {
            $match: {
            "v.name":/jos/i
            }
        },
    ]
);

Output

{ 
    "k" : "-HGFsfes", 
    "v" : {
        "name" : "Jose", 
        "phone" : NumberLong(124324)
    }
}

"k" and "v" are coming from "$objectToArray" operator, you can add a $project stage to rename them (or discard if k value doesn't matter)

Not sure for Robomongo testing but in Studio 3T, formerly Robomongo, you can either copy/paste this query in Intellishell console, or copy/import in aggregation tab, (small icon 'paste from the clipboard').

Hope it helps.

This query is the only one that seems to actually work. But I don't really understand it. But thanks! — WilsonPena, Jul 06 '18 at 11:55

MongoDB query returning no results

3 Answers3