mongodb - using join on a local variable

Question

I'm using node.js and mongodb, I have an array of objects which holds the names of an id. Let's say below is my array

let names = [ 
    { value: 1, text: 'One' },
    { value: 2, text: 'Two' },
    { value: 3, text: 'Three' },
    { value: 4, text: 'Gour' }
]

And this is my query result of a collection using $group which gives me the distinct values.

[ 
    { _id: { code: '1', number: 5 } },
    { _id: { code: '2', number: 5 } },
    { _id: { code: '3', number: 2 } },
    { _id: { code: '4', number: 22 } },
]

$lookup let's us to join the data from a different collection, but in my case I have an array which holds the text value for each of the codes which I got from the query.

Is there a way we can map the text from the array to the results from mongodb?

Any help will be much appreciated.

EDIT

MongoDB query which I was trying

db.collection.aggregate([
    {
        $match: {
            _Id: id
        }
    },
    {
        $lookup: {
            localField:         "code",
            from:               names,
            foreignField:       "value",
            as:                 "renderedNames"
        }
    },
    {
        "$group" : { 
            "_id": { 
                code:       "$code",
                number:  "$number"
            } 
        }
    }
]);

Is it a single document, or 4 documents in the first snippet? Which fields are looked up? It would be simpler to answer if you add an example of the lookup stage you are trying to write. — Alex Blex
– Alex Blex, Commented Oct 30, 2017 at 14:52
I'm trying to get the text from names array to the mongodb results which I have using value and code — Unknown User
– Unknown User, Commented Oct 30, 2017 at 14:55

Alex Blex · Accepted Answer · 2017-10-30 16:03:19Z

1

Local variable lives in nodejs app, and mongodb knows nothing about it.

It looks like it belongs to representation layer, where you want to show codes as meaningful names. The mapping should be done there. I believe find is the most suitable here:

names.find(name => name.code === doc._id.code).text

If the names are not truly variable but quite constant, you can move it to own collection, e.g. codeNames:

db.codeNames.insert([ 
    { _id: "1", text: 'One' },
    { _id: "2", text: 'Two' },
    { _id: "3", text: 'Three' },
    { _id: "4", text: 'Gour' }
]);

and use $lookup as following:

db.collection.aggregate([
    {
        $match: {
            _Id: id
        }
    },
    {
        "$group" : {
            "_id": {
                code:       "$code",
                number:  "$number"
            }
        }
    },
    {
        $lookup: {
            localField:         "_id.code",
            from:               "codeNames",
            foreignField:       "_id",
            as:                 "renderedNames"
        }
    }
]);

If none of the above suit your usecase, you can pass the names to the database in each request to map names db-side, but you must be really really sure you cannot use 2 previous options:

db.collection.aggregate([
    {
        $match: {
            _Id: id
        }
    },
    {
        "$group" : {
            "_id": {
                code:       "$code",
                number:  "$number"
            }
        }
    },
    {
        $project: {
            renderedNames: { $filter: { 
                input: [ 
                    { value: "1", text: 'One' },
                    { value: "2", text: 'Two' },
                    { value: "3", text: 'Three' },
                    { value: "4", text: 'Gour' }
                ], 
                as: "name", 
                cond: { $eq: [ "$$name.value", "$_id.code" ] } 
            }
         }
      }
    },
]);

As a side note, I find $match: {_Id: id} quite confusing, especially followed by $group. If _Id is _id, it is unique. You can have no more than 1 document after this stage, so there is not too much to group really.

answered Oct 30, 2017 at 16:03

Alex Blex

37.3k7 gold badges53 silver badges86 bronze badges

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

5 Comments

Unknown User Over a year ago

Hey, sorry about the _id, while masking the real code. I didn't notice that. The 3rd method is what I was looking for. It worked now for me, thanks a lot :) +1

Alex Blex Over a year ago

Glad it helped, but I feel like I need to emphasise that the 3rd one is the worst in terms of performance.

Unknown User Over a year ago

Oh, I just started on mongo db few days ago. If you can give me a better one than that. It'll really help to get more knowledge on mongo. Thanks in advance.

Alex Blex Over a year ago

It is not specific to mongo db. I am pretty sure the first option is what you are after. If you need to display fancy names instead of codes, such logic should live in representation layer. Some would go as far as moving it to css, but it might be a bit too extreme. I'd apply mapping serverside at application level.

Unknown User Over a year ago

It is returning me only one item in the array, so If I loop I just insert it to the existing. It works and efficient. Thank you :)

Collectives™ on Stack Overflow

mongodb - using join on a local variable

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related