Let's say my products collection include products that each one has items of array as below.

 [
      {
        "_id": "1",
        "score": 200,
        "items": [
          {
            "_id": "1",
            "title": "title1",
            "category": "sport"
          },
          {
            "_id": "2",
            "title": "title2",
            "category": "sport"
          },
          {
            "_id": "3",
            "title": "title3",
            "category": "tv"
          },
          {
            "_id": "4",
            "title": "title4",
            "category": "movies"
          }
        ]
      },
      {
        "_id": "2",
        "score": 1000000000,
        "items": [
          {
            "_id": "9",
            "title": "titleBoo",
            "category": "food"
          },
          {
            "title": "title4",
            "category": "movies"
          },
          {
            "title": "titlexx",
            "category": "food"
          },
          {
            "title": "titl113",
            "category": "sport"
          }
        ]
      },
      {
        "_id": "3",
        "score": 500,
        "items": [
          {
            "title": "title3",
            "category": "movies"
          },
          {
            "title": "title3",
            "category": "food"
          },
          {
            "title": "title3",
            "category": "sport"
          },
          {
            "title": "title3",
            "category": "sport"
          }
        ]
      }
    ]

I want to return Single Item by category that has the highest score by category, and if no category matched just return random/first product that have max score.

Example for category "food", the result should be:

   {
     "_id" : "9",
      "title": "titleBoo",
      "category": "food"
    }

because it has the max score of 1000000000

and for other non exists category "Foo" the result should be some random from highest score product item let's say

{
      "title": "titlexx",
      "category": "food"
    },

Basically what I did using java spring data aggregation pipeline

Aggregation agg1 = newAggregation(
            unwind("items"),
            match(Criteria.where("items.category").is(category)),
            group().max("score").as("score")
    );

 BasicDBObject result =    mongoTemplate.aggregate(
            agg1, "products", BasicDBObject.class).getUniqueMappedResult();


 if (result empty) { // didn't find any matched category so without match step !

   Aggregation agg2 = newAggregation(
                unwind("items"),
                group().max("score").as("score")
        );

    // take some item inside max  "score"
     BasicDBObject res2 =    mongoTemplate.aggregate(
                agg2, "products", BasicDBObject.class).getUniqueMappedResult();

    System.out.print(res2);

}

This code not ideal as I need to perform "unwind" twice (if not matched) do another time .. I know there is $cond / switch function, I'm wondering if I can use after unwind some switch case operation like here:

 Aggregation agg = newAggregation(
                unwind("items"),
               // switch-case {
                      a. match(Criteria.where("items.category").is(category)),
                          if (result or size > 0) {
                               group().max("score").as("score") // max on matched result by category
                           } 
                 
                      b. group().max("score").as("score"). // max on random unwind score
                 }
        );

     BasicDBObject result =    mongoTemplate.aggregate(
                agg, "products", BasicDBObject.class).getUniqueMappedResult();

Any hints ?

CodePudding user response：

Following the advice by @user20042973, one option is using $setWindowFields and $facet. It can also be done without steps 1-3, but since $unwind is considered as a less-efficient step, and $facet is not using the index, adding steps 1-3 may reduce a large part of the documents before these operations, and leave you with only two documents. After the $match step, we only left one document with the best score and documents that contain the wanted category (if there are any), sorted by the score (from the $setWindowFields step). This means we only want the first document (best score) or the second document if exist, which is highest score that guaranteed to have the category in it. So we can limit the reset of our search to these 2 documents:

db.collection.aggregate([
  {$setWindowFields: {
      sortBy: {score: -1},
      output: {bestScore: {$max: "$score"}}
  }},
  {$match: {$expr: {
        $or: [
          {$eq: ["$score", "$bestScore"]}, 
          {$in: [category, "$items.category"]}
        ]
  }}},
  {$limit: 2},
  {$unwind: "$items"},
  {$facet: {
      category: [{$match: {"items.category": category}}, {$limit: 1}],
      other: [{$limit: 1}]
  }},
  {$replaceRoot: {newRoot: {
        $cond: [
          {$eq: [{$size: "$category"}, 1]},
          {$first: "$category"},
          {$first: "$other"}
        ]
  }}}
])

See how it works on the playground example.

You can also use $reduce to avoid the $unwind step altogether, but at this point it should have a minor effect.