Create Spring Data Aggregation Query with Projection of Nested Array

Question

Here is how my document looks like:

{
"_id" : ObjectId("583cb6bcce047d1e68339b64"),
"variantDetails" : [ 
    {
        "variants" : {
            "_" : "_"
        },
        "sku" : "069563-59690"
    }, 
    {
        "variants" : {
            "size" : "35"
        },
        "sku" : "069563-59690-35",
        "barcode" : "809702246941"
    }, 
    {
        "variants" : {
            "size" : "36"
        },
        "sku" : "069563-59690-36",
        "barcode" : "809702246958"
    }
    ......
] }

And I would like to use a complex aggregation query like this:

db.getCollection('product').aggregate([
    { '$match': { 'variantDetails.sku': { '$in': ['069563-59690', '069563-59690-36', '069563-59690-37', '511534-01001'] } } },
    { '$project': {'_id': 1, 'variantDetails': 1, 'variantLength': { '$size': '$variantDetails' } } },
    { '$unwind': '$variantDetails' },
    { '$match': { 'variantDetails.sku': { '$in': ['069563-59690', '069563-59690-36', '069563-59690-37', '511534-01001'] } } },
    { '$match': { '$or': [
        {'variantLength': { '$ne': 1 }, 'variantDetails.variants._': { '$ne': '_' } },
        {'variantLength': 1 }
    ] } },
    { '$group': { '_id': '$_id', 'variantDetails': { '$push': '$variantDetails' } } },
    { '$project': {'_id': 1, 'variantDetails.sku': 1, 'variantDetails.barcode': 1} }
])

And here is my java code:

    final Aggregation agg = Aggregation.newAggregation(
            Aggregation.match(Criteria.where("variantDetails.sku").in(skus)),
            Aggregation.project("_id", "variantDetails").and("variantDetails").project("size").as("variantLength"),
            Aggregation.unwind("variantDetails"),
            Aggregation.match(Criteria.where("variantDetails.sku").in(skus)),
            Aggregation.match(new Criteria().orOperator(Criteria.where("variantLength").is(1), Criteria.where("variantLength").ne(1).and("variantDetails.variants._").is("_"))),
            Aggregation.group("_id").push("variantDetails").as("variantDetails"),
            Aggregation.project("_id", "variantDetails.sku", "variantDetails.barcode")
            );

    final AggregationResults<Product> result =  this.mongo.aggregate(agg, this.mongo.getCollectionName(Product.class), Product.class);
    return result.getMappedResults();

The problem is that spring translate

Aggregation.project("_id", "variantDetails.sku", "variantDetails.barcode")

To

{ "$project" : { "_id" : 1 , "sku" : "$variantDetails.sku" , "barcode" : "$variantDetails.barcode"}

But I'm expecting

{ '$project': {'_id': 1, 'variantDetails.sku': 1, 'variantDetails.barcode': 1} }

Could someone let me know how to make it right?

Virginia León · Accepted Answer · 2020-08-12 18:28:30Z

2

I had the same issue and this way works:

Aggregation.project("_id")
.andExpression("variantDetails.sku").as("variantDetails.sku")                
.andExpression("variantDetails.barcode").as("variantDetails.barcode"));

The projection will be:

{'$project': {'_id': 1, 'variantDetails.sku': '$variantDetails.sku', 
'variantDetails.barcode': '$variantDetails.barcode'} }

answered Aug 12, 2020 at 18:28

Virginia León

212 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2022-12-13 07:02:39Z

2

May be an old question, but I faced the same issue pointed by Sean.

If found that if you want the expected result

{ '$project': {'_id': 1, 'variantDetails.sku': 1, 'variantDetails.barcode': 1} }

a solution can be:

Aggregation.project("_id")
    .andExpression("1").as("variantDetails.sku")
    .andExpression("1").as("variantDetails.barcode")

Virginia León's answer was the starting point for finding this solution

edited Dec 13, 2022 at 7:02

CommunityBot

11 silver badge

answered Dec 2, 2022 at 22:14

Christophe TARET

211 bronze badge

1 Comment

Evgeny K Over a year ago

Please don't add "thank you" as an answer. Once you have sufficient reputation, you will be able to vote up questions and answers that you found helpful. - From Review

s7vr · Accepted Answer · 2016-12-27 01:56:13Z

1

You just need to specify the label as alias in the projection operation as the default that spring provides doesnt match. Use Spring 1.8.5 version

Aggregation.project("_id")
                   .and(context -> new BasicDBObject("$arrayElemAt", Arrays.asList("variantDetails.sku", 0))).as("variantDetails.sku")
                   .and(context -> new BasicDBObject("$arrayElemAt", Arrays.asList("variantDetails.barcode", 0))).as("variantDetails.barcode"));

edited Dec 27, 2016 at 1:56

answered Dec 1, 2016 at 17:14

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

5 Comments

Sean Over a year ago

then spring translate to

{ "$project" : { "_id" : 1 , "variantDetails.sku" : "$variantDetails.sku" , "variantDetails.barcode" : "$variantDetails.barcode"}

which return the sku and barcode in an array like {"variantDetails" : [ { "sku" : [ "069563-59690-36" ], "barcode": ...]}

s7vr Over a year ago

but thats exactly what you get when you run the query you provided in mongo shell. its an array and the output shows that. I dont know what I'm missing here

Sean Over a year ago

Noop. If I run the query in mongo shell, it return "variantDetails" : [ { "sku" : "069563-59690-36" , "barcode": ...]} rather than [ { "sku" : ["069563-59690-36"] , "barcode": ...]}. The sku is single value not an array.

s7vr Over a year ago

Updated to return single value.

Sean Over a year ago

Unfortunately, I've already migrated to MySQL for better transaction support, and I may not be able to verify your suggestion. But I think use BasicDBObject to write raw queries will definitely work. Thanks a lot for all your help:)

Collectives™ on Stack Overflow

Create Spring Data Aggregation Query with Projection of Nested Array

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related