I have a collection with nested arrays as below:

[
   {
      "title":"Foo",
      "arr":[
         [
            {
               "value":"2021-11-13T00:00:00.000Z",
               "baz":10
            },
            {
               "value":"2021-11-12T00:00:00.000Z",
               "baz":0
            }
         ]
      ]
   },
   {
      "title":"Bar",
      "arr":[
         [
            {
               "value":"2021-12-03T00:00:00.000Z",
               "baz":10
            },
            {
               "value":"2021-12-07T00:00:00.000Z",
               "baz":0
            }
         ]
      ]
   }
]

I want to filter out the largest value (i.e., latest date) for each document such that the result is:

[
    {
        "title": "Foo",
        "value": "2021-11-13T00:00:00.000Z",
    },
    {
        "title": "Bar",
        "value": "2021-12-07T00:00:00.000Z",
    },
]

How can this query be written?

CodePudding user response：

Query1

• find the local max in the iner arrays
• find the global max in the outer arrays

Test code here

aggregate(
[{"$set": 
   {"value": 
     {"$max": {"$map": {"input": "$arr", "in": {"$max": "$$this.value"}}}}}},
 {"$unset": ["_id", "arr"]}])

Query2

• this flattens the array to not be nested, and takes the max after
• reduce and concatArrays to make the nested array one not nested array
• take the max for each array
• project to keep only those 2 fields like expected output

Test code here

aggregate(
[{"$set": 
    {"arr": 
      {"$reduce": 
        {"input": "$arr",
          "initialValue": [],
          "in": {"$concatArrays": ["$$value", "$$this"]}}}}},
  {"$set": {"value": {"$max": "$arr.value"}}},
  {"$project": {"_id": 0, "title": 1, "value": 1}}])

CodePudding user response：

Instead of $reduce you can also use $filter and $map:

db.collection.aggregate([
   {
      $set: {
         arr: {
            $map: {
               input: "$arr",
               as: "item",
               in: {
                  $filter: {
                     input: "$$item",
                     cond: { $eq: ["$$this.baz", { $max: "$$item.baz" }] }
                  }
               }
            }
         }
      }
   }
])

It is not clear why you have an array arr with just one element. If your collection has always just one element element in arr, then you can also bypass the $map

db.collection.aggregate([
   {
      $set: {
         arr: {
            $let: {
               vars: { item: { $first: "$arr" } },
               in: {
                  $filter: {
                     input: "$$item",
                     cond: { $eq: ["$$this.value", { $max: "$$item.value" }] }
                  }
               }
            }
         }
      }
   }
])