[SERVER-18966] Allow exclusion in $project stage of aggregation pipeline Created: 13/Jun/15  Updated: 06/Apr/23  Resolved: 24/Jun/16

Status: Closed
Project: Core Server
Component/s: Aggregation Framework
Affects Version/s: None
Fix Version/s: 3.3.9

Type: Improvement Priority: Major - P3
Reporter: Asya Kamsky Assignee: Charlie Swanson
Resolution: Done Votes: 3
Labels: expression, stage, usability
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Depends
is depended on by SERVER-22436 Allow parsing of string literals in a... Closed
Documented
is documented by DOCS-8973 Allow exclusion in $project stage of ... Closed
Duplicate
duplicates SERVER-22787 Add $lookup.excludeLocalField to excl... Backlog
is duplicated by SERVER-23411 Inconsistent results when setting a n... Closed
is duplicated by SERVER-22436 Allow parsing of string literals in a... Closed
Related
related to SERVER-6714 Array in input doc schema incorrectly... Closed
related to SERVER-23411 Inconsistent results when setting a n... Closed
related to CSHARP-1598 allow exclusion in $project stage Closed
related to JAVA-2148 Ensure proper builder support for exc... Closed
related to SERVER-5781 Implement $addFields aggregation stag... Closed
is related to SERVER-30633 Large performance regression for larg... Closed
is related to DRIVERS-297 Aggregation Framework Support for 3.4 Closed
is related to SERVER-24921 No way to $project out just the _id f... Closed
Backwards Compatibility: Minor Change
Sprint: Query 11 (03/14/16), Query 12 (04/04/16), Query 13 (04/22/16), Query 14 (05/13/16), Query 15 (06/03/16), Query 16 (06/24/16)
Participants:

 Description   

There is currently no way to easily specify that project stage keep all fields except for a particular one (with exception of _id).

This can be helpful to limit the amount of data returned when some aggregation also needs to be done.

Examples

// Exclude a single field.
> db.example.insert([
  {_id: 0, a: 1}
]);
> db.example.aggregate([{$project: {a: 0}}])
{_id: 0}
> db.example.aggregate([{$project: {a: false}}])  // Means the same thing as above.
{_id: 0}
 
// Exclude a nested field.
> db.example.drop()
> db.example.insert([
  {_id: 0, a: {b: 1, c: 1}}
]);
> db.example.aggregate([{$project: {'a.b': 0}}])
{_id: 0, a: {c: 1}}
> db.example.aggregate([{$project: {a: {b: 0}}}])  // Means the same thing as above.
{_id: 0, a: {c: 1}}
 
// Exclude a nested field from an array.
> db.example.drop()
> db.example.insert([
  {_id: 0, a: [{b: 1, c: 1}, {b: 2, c: 2}]}
]);
> db.example.aggregate([{$project: {'a.b': 0}}])
{_id: 0, a: [{c: 1}, {c: 2}]}
> db.example.aggregate([{$project: {a: {b: 0}}}])  // Means the same thing as above.
{_id: 0, a: [{c: 1}, {c: 2}]}



 Comments   
Comment by tony kerz [ 11/Jan/17 ]

doh, yeah, invalid json, and SERVER-27614 would be great for this. thanks!

Comment by Charlie Swanson [ 09/Jan/17 ]

Hi tony-kerz,

I understand. Yes, it would be useful to do so. That syntax looks reasonable, but isn't valid JSON (there's no field name for the $ifNotNull object). I think the syntax proposed in SERVER-27614 would work nicely here, what do you think?

Comment by tony kerz [ 09/Jan/17 ]

thanks for the response charlie. your comments around the risk of your workaround make sense.

i was just trying to keep unnecessary bloat out of the resultant records, take the example where a field is non-null in only some small percentage of the records, it seems like a waste to populate all records with:

someFieldName: null

how would you feel about something like:

{$project: {a: 1, {$ifNotNull: '$b'}}

to handle that case?

Comment by Charlie Swanson [ 09/Jan/17 ]

Hi tony-kerz,

I don't know of any great way to do that. I'm curious why you want to do this though? Are you using 'b' later in the pipeline, or do you just want to avoid serializing it if you aren't going to be using it?

One weird workaround would be to do something like

{$project: {a: 1, b: {$ifNull: ['$b', '$nonExistentField']}}}

which would not include 'b' if it was null or missing. Note that this would have some ramifications earlier in the pipeline, since the dependency analysis would now think that a field called 'nonExistentField' was necessary to answer this pipeline. Essentially, this could mean that if your pipeline was previously using a covered index scan on 'a' and 'b', that index could no longer provide a covered projection, since we would need to fetch the documents to find the field 'nonExistentField'. So this is not ideal, but if your $project is after a $group or something like that, it wouldn't matter as much.

Comment by tony kerz [ 07/Jan/17 ]

thanks charlie, that makes sense.

what i was really after is a little bit of a spin on that theme where i wanted to exclude a key/value if the value was null,
so i was hoping to use something like:

{$project: {a: 1, b: {$ifNull: ['$b', 0]}}}

which would only project b if it has a non-null value...

do you know of any way to accomplish something like that?

Comment by Charlie Swanson [ 06/Jan/17 ]

Hi tony-kerz,

Your projection is mixing an inclusion (the {a: 1} part) and an exclusion (the {b: false} part). Mixing inclusion and exclusion is illegal, since it is ambiguous whether or not fields not named (such as "_id" in your case, or a field "c" if your documents had one) should be included or not. If you only want the field "a", just stick to {a: 1}. If you want everything except "b", just use {b: 0} or {b: false}.

Hope this helps.

Comment by tony kerz [ 06/Jan/17 ]

i'm currently running 3.4.1 and seeing this:

> db.x.find()
{ "_id" : ObjectId("58701737677e37cf35a28927"), "a" : "a1", "b" : "b1" }
{ "_id" : ObjectId("58701741677e37cf35a28928"), "a" : "a2", "b" : null }
> db.x.aggregate([{$project: {a: 1, b: false}}])
assert: command failed: {
	"ok" : 0,
	"errmsg" : "Bad projection specification, cannot exclude fields other than '_id' in an inclusion projection: { a: 1.0, b: false }",
	"code" : 40178,
	"codeName" : "Location40178"
} : aggregate failed

what am i missing?

Comment by Charlie Swanson [ 24/Jun/16 ]

The following changes might be considered "backwards breaking":

  • {$project: {a: {}}} is now an error
  • {$project: {"a.b": <any expression>}} will now add "a" to the dependencies, to ensure that the structure of the document is preserved (see SERVER-23411 for more details).
  • {$project: {"a": {"b": "$missingField"}}} now results in {a: {}}, not {}.

Additional changes in behavior:

  • {$project: {a: null}} is now interpreted the same as {$project: {a: {$literal: null}}} (previously was an error). Similarly for all non-object, non-numeric types, including strings (resolves SERVER-22436).
  • {$group: {_id: {a: 1, b: 1}}} is no longer an error (this used to be interpreted as an attempt at an inclusion of the field "a" and "b"), this will now group everything together and result in a single document with the _id = {a: 1, b: 1}. Similarly for other object literals, as in the expression {$setDifference: [[{a: 1, b: 1}], "$myFields"]}
Comment by Githook User [ 24/Jun/16 ]

Author:

{u'username': u'cswanson310', u'name': u'Charlie Swanson', u'email': u'charlie.swanson@mongodb.com'}

Message: SERVER-18966 Add the ability to exclude fields in $project stage
Branch: master
https://github.com/mongodb/mongo/commit/752eb0f448987b3e4183beacc622f4e95e7803be

Comment by Dissatisfied Former User [ 13/Jun/16 ]

While I agree that this would be a workaround for SERVER-22787, I don't think this ticket duplicates the other one. This ticket has far more broad implications than adding a special flag to the $lookup stage.

Comment by Jose Antonio Illescas Olmo [ 23/Apr/16 ]

This feature (remove fields on $projection stage) can be a workaround for SERVER-22787 ($lookup.excludeLocalField)

Comment by Jose Antonio Illescas Olmo [ 12/Apr/16 ]

I need this feature,

All my documents contains "lastModified" date exposed as Last-Modified http-header (coditional get and put) => this field must be removed from document/json content

Thank you

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