Find mongoDB document with known child object value and dynamic parent

Question

We have data stored in mongodb by country code. Our document will look like the following,

{
    "_id" : ObjectId("5d82009cddf8d111b0f5c0f9"),
    "title" : "Keystone Korner",
    "CA" : {
        "dlp" : 21.99,
        "dlpCurrencyCode" : "CAD",
        "sales" : {
            "changeProceedsPriorDay" : 0.0,
            "changeUnitsPriorDay" : 0,
            "consumerPriceCurrencyCode" : null,
            "last30Proceeds" : 0.0,
            "last30Units" : 0,
            "last365Proceeds" : 4.22,
            "last365Units" : 4,
            "last60Proceeds" : 0.0,
            "last60Units" : 0,
            "last7Proceeds" : 0.0,
            "last7Units" : 0,
            "last90Proceeds" : 0.0,
            "last90Units" : 0,
            "lastAvgSalesPrice" : 0.0,
            "lastAvgSalesPricePrior" : 0.0,
            "lastProceeds" : 0.0,
            "lastProceedsPct" : 0.0,
            "lastProceedsPrior" : 0.0,
            "lastSalesDate" : "2021-08-23",
            "lastUnits" : 0,
            "lastUnitsPct" : 0.0,
            "lastUnitsPrior" : 0
        }
    },
    "US" : {
        "dlp" : 17.99,
        "dlpCurrencyCode" : "USD",
        "sales" : {
            "changeProceedsPriorDay" : 0.0,
            "changeUnitsPriorDay" : 0,
            "consumerPriceCurrencyCode" : null,
            "last30Proceeds" : 4.18,
            "last30Units" : 2,
            "last365Proceeds" : 51.66,
            "last365Units" : 19,
            "last60Proceeds" : 4.18,
            "last60Units" : 2,
            "last7Proceeds" : 0.0,
            "last7Units" : 0,
            "last90Proceeds" : 7.37,
            "last90Units" : 4,
            "lastAvgSalesPrice" : 0.0,
            "lastAvgSalesPricePrior" : 0.0,
            "lastProceeds" : 0.0,
            "lastProceedsPct" : 0.0,
            "lastProceedsPrior" : 0.0,
            "lastSalesDate" : "2021-08-25",
            "lastUnits" : 0,
            "lastUnitsPct" : 0.0,
            "lastUnitsPrior" : 0
        }
    }
}

I have provided only few country listings here but each document can contain almost 80-100 different country listings.

Our requirement is to identify the most recent lastSalesDate from sales parent under each country. So if we are able to list all lastSalesDate we may be able to sort and limit it by 1

How do we achieve this without knowing parents (Country codes like US, CA, GB, etc..)?

Thanks in advance.

Answer 1

• $project to show required fields
• $objectToArray to convert $$ROOT root document to an key-value format array of objects
• $filter to iterate loop of above root converted array and filter country object by checking sales property is object
• $sort by lastSalesDate in descending order
• $limit to get single and recent document
• $let to declare an inline variable for finding a maximum date from lastSalesDate
• $max to get maximum date from lastSalesDate
• $indexOfArray to get array index position from list of all country lastSalesDate
• $arrayElemAt to get recent country by above returned index position

db.collection.aggregate([
  {
    $project: {
      title: 1,
      country: {
        $filter: {
          input: { $objectToArray: "$$ROOT" },
          cond: { $eq: [{ $type: "$$this.v.sales" }, "object"] }
        }
      }
    }
  },
  { $sort: { "country.v.sales.lastSalesDate": -1 } },
  { $limit: 1 },
  {
    $addFields: {
      country: {
        $let: {
          vars: {
            max: { $max: "$country.v.sales.lastSalesDate" }
          },
          in: {
            $arrayElemAt: [
              "$country",
              { $indexOfArray: ["$country.v.sales.lastSalesDate", "$$max"] }
            ]
          }
        }
      }
    }
  }
])

Playground

Result would be:

[{
    "_id": ObjectId("5d82009cddf8d111b0f5c0f9"),
    "country": {
      "k": "US",
      "v": {
        "dlp": 17.99,
        "dlpCurrencyCode": "USD",
        "sales": {
          "changeProceedsPriorDay": 0,
          "changeUnitsPriorDay": 0,
          "consumerPriceCurrencyCode": null,
          "last30Proceeds": 4.18,
          "last30Units": 2,
          "last365Proceeds": 51.66,
          "last365Units": 19,
          "last60Proceeds": 4.18,
          "last60Units": 2,
          "last7Proceeds": 0,
          "last7Units": 0,
          "last90Proceeds": 7.37,
          "last90Units": 4,
          "lastAvgSalesPrice": 0,
          "lastAvgSalesPricePrior": 0,
          "lastProceeds": 0,
          "lastProceedsPct": 0,
          "lastProceedsPrior": 0,
          "lastSalesDate": "2021-08-25",
          "lastUnits": 0,
          "lastUnitsPct": 0,
          "lastUnitsPrior": 0
        }
      }
    },
    "title": "Keystone Korner"
}]

Answer 2

this aggregation

[
    {
        '$project': {
            't': {
                '$objectToArray': '$$ROOT'
            }
        }
    }, {
        '$project': {
            'g': {
                '$map': {
                    'input': {
                        '$filter': {
                            'input': '$t', 
                            'as': 'tt', 
                            'cond': {
                                '$and': [
                                    {
                                        '$ne': [
                                            '$$tt.k', '_id'
                                        ]
                                    }, {
                                        '$ne': [
                                            '$$tt.k', 'title'
                                        ]
                                    }
                                ]
                            }
                        }
                    }, 
                    'as': 'featuresF', 
                    'in': {
                        'countyCode': '$$featuresF.k', 
                        'lastSale': '$$featuresF.v.sales.lastSalesDate'
                    }
                }
            }
        }
    }
]

returns out put like

this :

{
_id:"12313131313131313",
[{
countyCode:"US",
lastSale:"2021-08-25"
},
{
countyCode:"CA",
lastSale:"2021-08-23"
}]
}

so if you want something else this, please provide some sample preferred output , after this stage we could query or sort or do etc with this data