Mongodb subfield select

Question

Hello I have the following db:

a busy cat

My question is how do I "SELECT name FROM posts Where thread=Sloan"

Because I'm trying into RockMongo shell : "db.users.find({"posts.thread":'Sloan'})" but it's return all the collection data

***** Using json-generator.com ********************

[
            '{{repeat(5, 7)}}',
            {
                id: '{{index}}',
                group: '{{surname}}',
                name: '{{firstName}} {{surname}}',

                posts: [
                    '{{repeat(25)}}',
                    {
                        thread:'{{surname}}',
                        sb:[
                            '{{repeat(100)}}',
                            {
                                id: '{{index}}',
                                name: '{{firstName}} {{surname}}'

                            }
                        ]
                    }
                ],

                users:[
                    '{{repeat(25)}}',
                    {
                        name:'{{firstName}}'
                    }]


            }
        ]

Can you post the sample structure of document containing posts and thread. — shakthydoss, Aug 17 '13 at 10:15
I don't follow what's wrong. Your "SQL" like request doesn't match with your hierarchical data structure. Could you post your desired outcome? — WiredPrairie, Aug 17 '13 at 11:48
http://stackoverflow.com/questions/3985214/mongodb-extract-only-the-selected-item-in-array — WiredPrairie, Aug 17 '13 at 13:31

score 3 · Accepted Answer · answered Aug 21 '13 at 11:26

To clarify thing a little... if you have a database looking like this:

enter image description here

Then if you do db.users.find({ posts: { $elemMatch: { thread: "Sloan" } } }), you will obtain this (every element posts are included):

enter image description here

And if you do db.users.find({"posts.thread":'Sloan'},{'posts.$':1}), you will obtain this (only the relevant post is included):

enter image description here

(By the way, it's faster to perform... and if you add an index on posts.thread, it will be even faster.)

One last thing, which I think can be misleading here. The db.users.find({ posts: { $elemMatch: { thread: "Sloan" } } }, { name: 1 }) projection will give the element name 'gloria sharpe', but from what I understand from the question, he would prefer to obtain the name which is post.thread.sb.name. That's why he wants to have only the relevant post as an answer and not all the posts of that element.

Darm · Answer 2 · 2013-08-17T17:09:04.757

2

You can use $elemMatch operator as follow to get what you need:

db.users.find({ posts: { $elemMatch: { thread: "Sloan" } } }, { name: 1 })

edited Aug 17 '13 at 17:09

answered Aug 17 '13 at 16:15

Darm

5,581
2
20
18

1

Returns same data as db.users.find() – Xtal Aug 17 '13 at 16:58
Try it again, it's definitely correct. See https://gist.github.com/cheald/6291848 – Chris Heald Aug 21 '13 at 08:38
no it doesn't. In your case the stored object is the array, so the elemMatch return only the post with the array containing what you're looking for. In his case, the stored object contains multiple arrays and he's trying to find the one containing his value. The elemMatch will return the whole object, containing the multiple arrays, because one of them contains the value he's looking for. – Sebastien C. Aug 21 '13 at 08:56
Selecting just the bits he's interested in is easily done with the second parameter to find(); it seemed from discussion that his problems was in getting the right records in the first place. Perhaps I misinterpreted. – Chris Heald Aug 21 '13 at 11:26

Sebastien C. · Answer 3 · 2013-08-21T11:28:58.017

I'm not sure that you can retrieve directly the name, but you sure can retrieve the post you're looking for with a projection:

db.users.find({"posts.thread":'Sloan'},{'posts.$':1})

If you want more explanations about the $ projection operator, look here: MongoDB doc

Unfortunately I think you can't do {'posts.$.name':1} so you will have to extract the name in the resulting callback. (depends on which name field you want to retrieve... see my other answer)

score 0 · Answer 4 · edited May 23 '17 at 10:30

0

db.users.aggregate(
    { $unwind:'$posts' }, 
    {$match: {'posts.thread': 'Sloan'} },
    {$group:{_id:'$name', posts:{$push:'$posts'} }});

Learning from this link of the topic comments, I followed the answer of @Stennie, and give the answer, which i think is correct.

$unwindcut array to independend documents and $group make them to an array again.

edited May 23 '17 at 10:30

Community

1
1

answered Aug 17 '13 at 10:35

Ever

1,164
1
9
13

@thefourtheye No, you can visit [http://docs.mongodb.org/manual/reference/aggregation/match/](http://docs.mongodb.org/manual/reference/aggregation/match/) to see the syntax. `'posts.thread'`here can be writen as `posts.thread` (without quotes) – Ever Aug 18 '13 at 00:19

score 0 · Answer 5 · answered Aug 21 '13 at 08:30

You can use $unwind aggregation operator as follow to get the flat JSON document to work on $match to check with each item's thread (i.e. posts.thread) :

var name_to_match = 'Sloan';
db.users.aggregate(
    {$unwind:'$posts'}, 
    {$match: {'posts.thread': name_to_match} },
    {$group:{_id:'$name', name: {$first:'$posts.thread'} }}
);

Now, the resulting output contains something like this:

{
   _id: '{{firstName}} {{surname}}',
   name: '{{surname}}'
}

Happy exploring MongoDB :)

score 0 · Answer 6 · answered Aug 23 '13 at 10:35

in the java you can fetch value from mongodb in like this:

MongoClient mongo=new MongoClient("localhost",27017);
DB db = mongo.getDB("webportal");
DBCollection coll=db.getCollection("userdb");
String useremail=session("user");
BasicDBObject doc1=new BasicDBObject("email",useremail);
DBCursor cours=coll.find(doc1);
if(cours.hasNext())
{
    Map userjasondata=cours.next().toMap();
    String userid=userjasondata.get("_id").toString();
    session("userid",userid);
    String username=userjasondata.get("username").toString();
    String password=userjasondata.get("password").toString();
    String useremail1=userjasondata.get("email").toString();
    return ok(userprofile.render(username, username, password, useremail1)); 
}

Mongodb subfield select

6 Answers6