Are there computed fields in MongoDB?

Question

In SQL I can do:

SELECT A+B AS C FROM MYTABLE WHERE C>10

Can I do something similar in MongoDB?

UPDATE

I did with projection:

db.segments.aggregate(
   [
      {
         $project: {
            "_id": 1,
            numberOfRestrictions: { $size: "$Speed Restrictions" }
         }
      }
   ]
)

and it works.

Unfortunately, further pipelining does not:

db.segments.aggregate(
   [
      {
         $project: {
            "_id": 1,
            numberOfRestrictions: { $size: "$Speed Restrictions" }
         }
      },
      {
        $match: {
            "numberOfRestrictions": {
                "$gt": 1
            }
        }
      }
   ]
)

Latter causes error

The argument to $size must be an Array, but was of type: EOO

After first query alone documents look like `{ "_id" : ObjectId("12345"), "numberOfRestrictions" : 1 }` — Suzan Cioc, Feb 15 '16 at 12:04
I meant some sample documents in the `segments` collection and the final result you are expecting with those sample documents. — chridam, Feb 15 '16 at 12:08

score 12 · Answer 1 · answered Feb 15 '16 at 11:48

Yes. It is called aggregation pipelines. Specifically, you need to use a $project stage to create the C field, and then use a $match stage to find all documents which match the criterion.

Example

Let's create some documents first:

for( var i = 1; i <=10; i++){
  db.agg.insert({a:i,b:i})
}

Which results in a collection looking like this:

> db.agg.find()
{ "_id" : ObjectId("56c1b5561a3b578f37a99d4d"), "a" : 1, "b" : 1 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d4e"), "a" : 2, "b" : 2 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d4f"), "a" : 3, "b" : 3 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d50"), "a" : 4, "b" : 4 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d51"), "a" : 5, "b" : 5 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d52"), "a" : 6, "b" : 6 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d53"), "a" : 7, "b" : 7 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d54"), "a" : 8, "b" : 8 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d55"), "a" : 9, "b" : 9 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d56"), "a" : 10, "b" : 10 }

Finding all documents for which C > 10

db.agg.aggregate([
    // You need to include all fields you want to have
    // in the resulting document within the $project stage
    { "$project":{ a:1, b:1, c:{ "$add": ["$a","$b"] }}},
    { "$match":{ c:{ "$gt":10 }}}
])

Returns the following result:

{ "_id" : ObjectId("56c1b5561a3b578f37a99d52"), "a" : 6, "b" : 6, "c" : 12 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d53"), "a" : 7, "b" : 7, "c" : 14 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d54"), "a" : 8, "b" : 8, "c" : 16 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d55"), "a" : 9, "b" : 9, "c" : 18 }
{ "_id" : ObjectId("56c1b5561a3b578f37a99d56"), "a" : 10, "b" : 10, "c" : 20 }

but will the computed field remain updated as the data changes in the match? — Ricky-U, Aug 18 '20 at 11:23

chridam · Answer 2 · 2019-08-23T07:42:48.280

There is an operator called $expr that enables you to use aggregation framework operator inside the find() query.

For instance, the SQL query

SELECT A+B AS C FROM MYTABLE WHERE C>10

can be translated to a mongo query as

db.segments.find({ 
    "$expr": {
        "$gt": [
            { "$add": [ "$A", "$B" ] },
            10
        ]
    }
})

And for checking an array length it's similar

db.segments.find({ 
    "$expr": {
        "$gt": [
            { "$size": "$SpeedRestrictions" },
            10
        ]
    }
})

With the aggregation framework it's also possible to use $expr within a $match pipeline step:

db.segments.aggregate([
    { "$match": { 
        "$expr": {
            { "$gt": [
                { "$size": "$SpeedRestrictions" },
                10
            ] }
        }
    } }
])

And if the $expr operator is not available, for backwards compatibility one can use $redact as

db.segments.aggregate([
    { "$redact": { 
        "$cond": [
            { "$gt": [
                { "$size": "$SpeedRestrictions" },
                10
            ] },
            "$$KEEP",
            "$$PRUNE"
        ]
    } }
])

The other approach is to use the $addFields pipeline operator for creating the computed fields and the $match operator for filtering documents based on that computed field:

db.collection.aggregate([
    { "$addFields": { "C": { "$add": [ "$A", "$B" ] } } },
    { "$match": { "C": { "$gt": 10 } } }
])

Are there computed fields in MongoDB?

2 Answers2

Example

Finding all documents for which C > 10