MongoDB query show all value in array property when reference database

Question

I cannot show all`, if some value are in array property in mongo document. For example, I have some collection users, and such document:

This is collection friend

'list': [
{
  '_id': '123',
  'name':'Charlie',
  'age':50,
},
{
  '_id': '234',
  'name':'Micheal',
  'age':32,
},
{
  '_id': '345',
  'name':'Alice',
  'age':45,
}
]

This is collection seft

'name':'Paul',
  'age':43,
  'friends': 
    [
        {  '$ref': 'friend',
           '$id': 123},
        {  '$ref': 'friend',
           '$id': 234},
        {  '$ref': 'friend',
           '$id': 345}
    ],

Now, let's suppose that I want to query "show all friends of Paul" like:

{
  'name':'Paul',
  'age':43,
  'friendsIDs': 
  'friends': 
    [
        {  '_id': '123',
           'name':'Charlie',
           'age':50 },
        {  '_id': '234',
           'name':'Micheal',
           'age':32},
        {  '_id': '345',
           'name':'Alice',
           'age':45 }
    ]
}

This needs to be done in your application layer, MongoDB doesn't support join. — Satish Shinde
– Satish Shinde, Commented May 6, 2015 at 3:30

chridam · Accepted Answer · 2015-05-06 11:30:16Z

2

MongoDB doesn't support joins. You can read more about that here.

However, you can manually reproduce the same scenario you described above in MongoDB by using some native JavaScript functions to fill in the friends array with the references and save the modified data to another collection say self_friends.

Let's demonstrate this by using the above schema. Insert the sample data in two collections self and friends:

// Populate self collection
db.self.insert([
    {
        'name':'Paul',
        'age':43,
        'friends': 
        [
            {  '$ref': 'friend',
               '$id': 123},
            {  '$ref': 'friend',
               '$id': 234},
            {  '$ref': 'friend',
               '$id': 345}
        ]
    }    
]);

// Populate friends collection
db.friends.insert([
    {
      '_id': '123',
      'name':'Charlie',
      'age':50,
    },
    {
      '_id': '234',
      'name':'Micheal',
      'age':32,
    },
    {
      '_id': '345',
      'name':'Alice',
      'age':45,
    }
]);

The following operation will populate another collection self_friends with the desired result:

db.self.find({"name" : "Paul"}).forEach(function(doc){
        var friends = doc.friends.map(function(f){
            //var friend = db.friends.findOne({_id: f["$id"].toString()})
            // Or use @SylvainLeroux's brilliant suggestion 
            var friend = db.getCollection(f["$ref"]).findOne({_id: f["$id"].toString()})
            return friend;
        });
        doc.friends = friends;
        db.self_friends.save(doc);
})

Querying the db.self_friends collection with db.self_friends.find() will give the result:

/* 0 */
{
    "_id" : ObjectId("5549f2ff180e849972939047"),
    "name" : "Paul",
    "age" : 43,
    "friends" : [ 
        {
            "_id" : "123",
            "name" : "Charlie",
            "age" : 50
        }, 
        {
            "_id" : "234",
            "name" : "Micheal",
            "age" : 32
        }, 
        {
            "_id" : "345",
            "name" : "Alice",
            "age" : 45
        }
    ]
}

edited May 6, 2015 at 11:30

answered May 6, 2015 at 11:09

chridam

104k26 gold badges246 silver badges243 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sylvain Leroux Over a year ago

Given the OP document stores DBRef, maybe you should use something like db.getCollection(f["$ref"]).findOne({_id: f["$id"].toString()}) -- that being said, I don't think there is any benefit in using DBRef here instead of plain id. Or is it ?

chridam Over a year ago

@SylvainLeroux That's a great suggestion actually, would make much more sense to use the $ref key to get the collection with the value. I also would concur it wont be too beneficial to use DBRefs in the case above as the _ids are stored as strings in the reference collections.

Collectives™ on Stack Overflow

MongoDB query show all value in array property when reference database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related