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
– s7vr, Commented Jul 30, 2018 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
– user123, Commented Jul 30, 2018 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
– s7vr, Commented Jul 30, 2018 at 16:50
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
– s7vr, Commented Jul 31, 2018 at 2:04

The Student Soul · Accepted Answer · 2020-12-16 13:24:06Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related