I have a database for stocks that is saved as below:

[
  {
    _id: ObjectId("637b826fcef6ee66c879d5f3"),
    ticker: 'AA',
    quotes: [
      {
        volume: 14092516,
        open: 64.71,
        close: 67.37,
        high: 68.58,
        low: 62.46,
        transactions: 123967,
        date: '2022-04-25'
      },
      {
        volume: 7984906,
        open: 67.15,
        close: 66.97,
        high: 69.03,
        low: 64.67,
        transactions: 81841,
        date: '2022-04-26'
      }
    ]
  },
  {
    _id: ObjectId("637b92270e5e92b99783dcf9"),
    ticker: 'AAPL',
    quotes: [
      {
        volume: 96017376,
        open: 161.12,
        close: 162.88,
        high: 163.17,
        low: 158.46,
        transactions: 925124,
        date: '2022-04-25'
      },
      {
        volume: 95595226,
        open: 162.25,
        close: 156.8,
        high: 162.34,
        low: 156.72,
        transactions: 899965,
        date: '2022-04-26'
      }
    ]
  }
]

How may I write a query to fetch the quote for a specific ticker for a specific date?

So for example, how may I write a find, or an aggregate query that will get me quote for AAPL for date 2022-04-25

CodePudding user response：

Probably the most succinct syntax you could use would be this:

db.collection.find({
  ticker: "AAPL",
  "quotes.date": "2022-04-25"
},
{
  ticker: 1,
  "quotes.$": 1
})

Playground example here

That said, here are a few additional considerations:

• The projection gets rid of all of the additional fields in the document, so you will have to manually add any in that you would like returned to the client (as I did here with ticker: 1).
• It is usually not a good practice to store values that represent dates as strings, consider modifying that.
• You would have more flexibility using other approaches, like $filter via $addFields in aggregation.

An example of the more verbose alternative in aggregation is:

db.collection.aggregate([
  {
    $match: {
      ticker: "AAPL",
      "quotes.date": "2022-04-25"
    }
  },
  {
    "$addFields": {
      quotes: {
        $filter: {
          input: "$quotes",
          cond: {
            $eq: [
              "$$this.date",
              "2022-04-25"
            ]
          }
        }
      }
    }
  }
])

Playground demonstration here