How to project array element field in Spring Data Mongo DB Aggregation

Question

How to project embedded array element field in Spring Data MongoDB Aggregation with the document sample below, I tried:

project("customers.id")
project("customers.[].id")
project("customers.?.id")
project("$customers.id")

but didn't work.

Result document without projection:

{
    "id": "group1",
    "name": "Default Identity Management",
    "warningThreshold": 900000,
    "tariffId": "TR_0001",
    "active": false,
    "customers": [
        {
            "id": "1",
            "name": "David",
            "properties": [
                {
                    "name": "phone",
                    "value": "678"
                }
            ],
            "roles": [
                "dev"
            ]
        },
        {
            "id": "2",
            "name": "Peter",
            "properties": [
                {
                    "name": "phone",
                    "value": "770"
                }
            ],
            "roles": [
                "techsales",
                "dev"
            ]
        }
    ]
}

Expected document like this:

{
    "id" : "group1",
    "name" : "Group1",
    "tariffId" : "TR_0001",
    "warningThreshold" : 900000,
    "customers" : [
        {
            "id" : "1",
            "name" : "David",
            "properties" : [
                {
                    "name" : "phone",
                    "value" : "678"
                }
            ]
        },
        {
            "id" : "2",
            "name" : "Peter",
            "properties" : [
                {
                    "name" : "phone",
                    "value" : "770"
                }
            ]
        }
    ]
}

I would like to include customers[].id, customers[].name, customers[].properties.

Use projection with `project.andExclude("customers.roles"); `. It should work in 3.4 with spring mongo 2.x jar. — s7vr, Jul 30 '18 at 16:07
I ended up with the exception: `'Bad projection specification, cannot exclude fields other than '_id' in an inclusion projection` when trying with `project("id","customers").andExclude("customers.name")` — user123, Jul 30 '18 at 16:22
yeah. Couple of things. Version needed is 3.4 mongo server verisin and 2.0 spring mongo jar. You have to use extra project stage just for exclusion. Can't mix and match inclusion and exclusion in the single project. — s7vr, Jul 30 '18 at 16:50
Unfortunately, It didn't work like I expected. Nothing happend with ` project("id","customers"), project().andExclude("customers.roles")` — user123, Jul 30 '18 at 17:07
Please try to run the query in mongo shell first and see if it works. It should work. — s7vr, Jul 30 '18 at 18:18
I can query normally in Mongo shell. It's not complex. But the Spring Data query or aggregation module sometimes is a whole difference thing, at least for projection stuffs. "customers.roles" didn't work. But anyway, I known how to resolve my case. Thanks you for your help. — user123, Jul 31 '18 at 01:13
You are welcome. I have tried query - `db.colname.aggregate({$project:{"customers.roles":0}})` and it works as expected. — s7vr, Jul 31 '18 at 01:16
I known that, it of course works in mongo shell but not with Spring Data. I tried and confirm this is true. You think it's the same syntax? If It's easy like that I would haven't asked the question. — user123, Jul 31 '18 at 01:22
Sorry. It didn't occur to me projection with nested fields doesn't work in spring. You can try `AggregationOperation project = new AggregationOperation() { @Override public Document toDocument(AggregationOperationContext aggregationOperationContext) { return new Document("$project", new Document("customers.roles", 0)); } };`. With lambda you can reduce to `AggregationOperation project = aggregationOperationContext -> new Document("$project", new Document("customers.roles", 0));` — s7vr, Jul 31 '18 at 02:04
Thank you for your enthusiasm. Could you try it in this style please. Maybe the style is the matter? `TypedAggregation aggregation = newAggregation(CustomerGroup.class, match(where("type").is("Person")), lookup("assets", "id", "category", "customers"), project().andExclude("customers.roles") );` — user123, Jul 31 '18 at 03:01

The Student Soul · Answer 1 · 2020-12-16T13:24:06.207

I'd been trying to figure this out for a while now, but couldn't. And the other posts here on stackoverflow, and other places on the internet, didn't provide the solution I was looking for.

My problem was similar to the original author's: There's a document, which has a field which is an array of documents. I wanted to query all the top level fields in the document, and exclude a single field from the documents within the array.

s7vr's answer in the comments for the question did the job for me! Just re-posting that here since most people don't go through all the comments, and it is a really useful answer, that saved me from writing a lot of crappy code! :D

AggregationOperation project = new AggregationOperation() {
    @Override
    public Document toDocument(AggregationOperationContext aggregationOperationContext) {
        return new Document("$project", new Document("arrayField.nestedFieldToExclude", 0));
    }
};

With Lambda:

AggregationOperation project = aggregationOperationContext -> new Document("$project", new Document("arrayField.nestedFieldToExclude", 0));

Overall pipeline:

Aggregation aggregation = Aggregation.newAggregation(
    Aggregation.match(criteria),
    Aggregation.sort(Sort.Direction.DESC, "createdOn"),
    project);

I just wish there was a cleaner way to do this with the Spring MongoDB Data API directly, rather than using it this way with lambda functions.

Also, please note that the method AggregationOperation.toDocument(AggregationOperationContext aggregationOperationContext) has been deprecated as of spring-data-mongodb version 2.2.

How to project array element field in Spring Data Mongo DB Aggregation

1 Answers1

Linked