MongoDB return child array with last item only

Question

I have 3 level nested array. I am trying an aggregation where I get only Extras that has any quotation (not empty).

The return must have only the last Quotation... If an Extra has 20 quotations, the return should be only the the array with one item (20th one).

I am trying the $slice function but is not working... what should I use?

In this example, should return only the item _id:1 with QuotationHistory with only one item inside (the last one)

I am using $slice or $lastN with no sucess. Should I unwind 3x to the level of the quotation and addFileds with another name using $slice?

Or should I user filter inside the array on the first stage and than do the other operations?

Playground

[
  {
    _id: 1,
    Extras: [
      {
        Name: "NO",
        QuotationHistory: []
      },
      {
        Name: "NO",
        QuotationHistory: null
      },
      {
        Name: "NO",
        NOQuotationHistory: true
      },
      {
        Name: "OK",
        QuotationHistory: [
          {
            Date: "2023-01-01",
            Items: [
              {
                Name: "ERROR",
                Price: 90
              }
            ]
          },
          {
            Date: "2023-01-02",
            Items: [
              {
                Name: "LAST 200",
                Price: 200
              }
            ]
          }
        ]
      }
    ]
  },
  {
    _id: 2,
    Extras: [
      {
        Name: "OK",
        QuotationHistory: [
          {
            Date: "2023-01-01",
            Items: [
              {
                Name: "ERROR",
                Price: 90
              }
            ]
          },
          {
            Date: "2023-01-02",
            Items: [
              {
                Name: "LAST 100",
                Price: 100
              }
            ]
          }
        ]
      },
      {
        Name: "OK",
        QuotationHistory: [
          {
            Date: "2023-01-01",
            Items: [
              {
                Name: "ERROR",
                Price: 100
              }
            ]
          },
          {
            Date: "2023-01-02",
            Items: [
              {
                Name: "LAST 300",
                Price: 300
              }
            ]
          }
        ]
      }
    ]
  },
  {
    _id: 4,
    Extras: [
      {
        Name: "OK",
        QuotationHistory: [
          {
            Date: "2023-01-01",
            Items: [
              {
                Name: "ERROR",
                Price: 90
              }
            ]
          },
          {
            Date: "2023-01-02",
            Items: [
              {
                Name: "LAST 500",
                Price: 500
              }
            ]
          }
        ]
      }
    ]
  },
  {
    _id: 5,
    Extras: [
      {
        Name: "NO",
        QuotationHistory: []
      }
    ]
  },
  {
    _id: 6,
    Extras: []
  },
  {
    _id: 7,
    WithNoExtrasProp: true
  }
]

ray · Accepted Answer · 2023-01-25 17:57:02Z

1

First $filter out Extras with no quotation history(i.e. $size is 0). Then, $match with non-empty Extras. Finally, use $mergeObjects with $last to update the Extras.QuotationHistory to its last entry.

db.collection.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          {
            $ne: [
              {
                $ifNull: [
                  "$Extras",
                  []
                ]
              },
              []
            ]
          },
          {
            $ne: [
              {
                $ifNull: [
                  "$Extras.QuotationHistory",
                  [
                    []
                  ]
                ]
              },
              [
                []
              ]
            ]
          }
        ]
      }
    }
  },
  {
    $set: {
      Extras: {
        QuotationHistory: {
          $last: {
            $last: "$Extras.QuotationHistory"
          }
        }
      }
    }
  }
])

Mongo Playground

edited Jan 25, 2023 at 17:57

answered Jan 24, 2023 at 19:26

ray

15.6k12 gold badges99 silver badges105 bronze badges

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

2 Comments

2Fast4YouBR Over a year ago

thanks for te reponse, but is not working. My example was missing empty and nulls quotations and multiple extras... see here the full example with your query. mongoplayground.net/p/0DRVwcggAyB . The problems that I found here: 1) in _id:1 is not returning the correct quoatation, should had return only the extra with quotation and returned all extras and none with quotations. 2) _id:2 returned 2 extras with quotations, but the quotation was the same with both and should be different

ray Over a year ago

@2Fast4YouBR updated the answer to use more array processing techinques

Collectives™ on Stack Overflow

MongoDB return child array with last item only

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related