[SERVER-19172] Allow logical operators on non-top-level expressions in $pull operations Created: 27/Jun/15  Updated: 06/Dec/22  Resolved: 29/Jun/19

Status: Closed
Project: Core Server
Component/s: Write Ops
Affects Version/s: None
Fix Version/s: None

Type: Improvement Priority: Minor - P4
Reporter: Blakes Seven Assignee: Backlog - Query Team (Inactive)
Resolution: Won't Do Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Assigned Teams:
Query
Participants:

 Description   

It would be desirable to allow logical operators over non-top-level expressions in $pull operations. Currently, the following is an error, and there is no equivalent way to express it in one update:

db.foo.update({}, {$pull: {a: {$or: [{$lt: 1}, {$gt: 10}]}}})

Original description:
At present it is only possible to present exact conditions to the $pull operaor. Take the following document:

    {
        foo: {
            bar: [1, 2, 3, 3, 4, 5, 5]
       }
    }

Now let's say I want to $unset the first occurence of 5 in the array, so the you could do:

    db.collection.update({ "foo.bar": 5 },{ "$unset": { "foo.bar.$": 1 } })

Which is all fine as now the first of the values for 5 is null:

    {
        foo: {
            bar: [1, 2, 3, 3, 4, null, 5]
       }
    }

But now if you want to remove both "null" and values "less than" 4 you need to perform several updates:

    db.collection.update({ },{ "$pull": { "foo.bar": { "$lt": 4 } } })
    db.collection.update({ },{ "$pull": { "foo.bar": null } })

So the current syntax to $pull seems to consider that the immediate "key" identifier inside the $pull operation needs to be the identifier for the array from which the items would be pulled from.

It would be nicer if full query conditions could be set such as:

    db.collection.update(
        { },
        { "$pull": { 
            "$or": [
               { "foo.bar": { "$lt": 4 },
               { "foo.bar": null }
           ]
       }}

But there is of course an ambiguity on "which field" is set for the array within an $or condition. That does not error presently, but of course does not work either.

A possible alternate syntax could be:

    db.collection.update(
        { },
        { "$pull": { "foo.bar": { "$in": [{ "$lt": 4 },null] }} }
    )

Being a shorter version of $or via $in, but of course that does error due to $in not allowing such a contruct for evaluation.

On the other hand using $pullAll does not error here:

    db.collection.update(
        { },
        { "$pullAll": { "foo.bar": [{ "$lt": 4 },null ] }}
    )

But of course only removes "null" values and ignores the conditional statement as a literal.

There must be some way to work the syntax in for multiple conditions without performing multiple update operations. Even the first $or example would be fine if there were some check to make sure you were not operating a condition on a different document element within the $pull operation, so that both elements had to be "foo.bar" in this case.

Allowing this but producing an error where the field conditions did not match would seem a valid approach.



 Comments   
Comment by Asya Kamsky [ 29/Jun/19 ]

SERVER-40381 implemented support for aggregation expressions to specify update for 4.2. 

You can see some examples here.

This can be done by setting the array to its new value using aggregation array expression $filter which takes arbitrarily complex conditional.

Comment by Asya Kamsky [ 29/Dec/17 ]

On the other hand, this syntax:

$pull: { 'foo.bar': { $or: [ { $lt: 4 }, { $eq: null } ] } }

does not work.

Comment by Asya Kamsky [ 29/Dec/17 ]

Of the original examples:

db.foo.update({}, {$pull: {a: {$not: {$eq: 1}}}})
// works, correct syntax:
db.foo.update({}, {$pull: {a: {$ne: 1}}})
 
db.foo.update({}, {$pull: {a: {$and: [{$gt: 1}, {$lt: 10}]}}})
// works correct syntax:
db.foo.update({}, {$pull: {a: {$gt: 1, $lt: 10}}})
 
db.foo.update({}, {$pull: {a: {$or: [{$lt: 1}, {$gt: 10}]}}})
// does not work and there is no alternate syntax to do it in a single update

Comment by Benjamin M [ 28/Jun/15 ]

With an array of documents this works pretty well:

$pull: { 'foo.bar': { 'value': {$not: { $gte: 4 } } } }

Here you can also use $or and everything else.

Though I'd suggest that this should be the right syntax for a simple array of values:

$pull: { 'foo.bar': { $not: { $gte: 4 } } }
$pull: { 'foo.bar': { $or: [ { $lt: 4 }, { $eq: null } ] } }

Because this already works:

$pull: { 'foo.bar': { $lt: 4 } }

(see here: http://docs.mongodb.org/manual/reference/operator/update/pull/#remove-all-items-that-match-a-specified-pull-condition )

Generated at Thu Feb 08 03:50:03 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.