CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-frameworkdatabase
martskins
martskins

Reputation: 2940

Column from other two columns in mongodb

I have a mongodb collection with records similar to this one...

{
   id:'8345344',
   x:'-33.2315',
   y:'-53.53453'
}

I need to query my database but query fields that are a composition of the other ones. For example...

{
   id:'8345344',
   x:'-33.2315',
   y:'-53.53453',
   newField:['-33.2315', '-53.53453'] //Values of x and y of this record
}

This field should only be used for querying, it should not be saved.

I saw something about $project and aggregation in mongodb but from what I've read I don't think it will suit my needs.

Is there a way to do this?

Upvotes: 0

Views: 952

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151112

You can do this with $project but it's not as straight forward as you might think:

db.combine.aggregate([
    { "$project": {
        "x": 1,
        "y": 1,
        "tag": { "$cond": [ true, [ "x", "y" ], 0 ] }
    }},
    { "$unwind": "$tag" },
    { "$project": {
        "newField": { "$cond": [ 
            { "$eq": [ "$tag", "x" ] },
            "$x",
            "$y"
        ]}
    }},
    { "$group": {
        "_id": "$_id",
        "newField": { "$push": "$newField" }
    }}
])

With MongoDB 2.6 there is a new $literal operator that removes the obscure usage of $cond in that first project phase

db.combine.aggregate([
    { "$project": {
        "x": 1,
        "y": 1,
        "tag": { "$literal": [ "x", "y" ] }
    }},
    { "$unwind": "$tag" },
    { "$project": {
        "newField": { "$cond": [ 
            { "$eq": [ "$tag", "x" ] },
            "$x",
            "$y"
        ]}
    }},
    { "$group": {
        "_id": "$_id",
        "newField": { "$push": "$newField" }
    }}
])

The $literal actually sort of explains why you need this sort of process and cannot just define a "newField" as an array by itself, as the content is "literally" defined and does not substitute variables.

So while you cannot just substitute the values from other fields as elements you can play in this way.

Of course there is even a simpler way to do this with MongoDB 2.6 and up, using the $setUnion operator:

db.combine.aggregate([
    { "$group": {
        "_id": "$_id",
        "x": { "$push": "$x" },
        "y": { "$push": "$y" }
    }},
    { "$project": {
        "newField": { "$setUnion": [ "$y", "$x" ] }
    }}
])

So first that forms both the "x" and "y" fields as arrays in themselves then uses $setUnion to merge those contents into a single array, as that operator takes multiple arrays as it's arguments.

The catch here is I wouldn't rely on the ordering as this is considered not to "strictly" be an "array" and from the mouth of the CTO, "a set is not considered to be ordered in any way"

Upvotes: 1

Related Questions