Querying for nested value

[Liam_Wrigley]

Hi,

I have a bunch of Player’s that each have a list of History’s and I am struggling with a query to only retrieve any History that has a ts which is greater to an input timestamp.

"name": <string>,
"id": <string>,
"ts": <number>,
"history": [
    {
        "param1": <number>,
        "param2": <number>,
        "param3": <number>,
        "ts": <number>
    },
    {
        "param1": <number>,
        "param2": <number>,
        "param3": <number>,
        "ts": <number>
    }
]

The query I have been trying to use is db.players.find({"history.ts": {$gt: _ts}}) where _ts = 1679540463210. However this seems to return all records, where I would like to see only the Player and History records that match.

[turivishal (Vishal Turi)]

Hello @Liam_Wrigley, Welcome to the MongoDB Community Forum,

Your query should filter the player documents (main document), if it is not doing then there is some other issue in your data, you need to post some example documents and executed query in the shell,

To filter history you need to use a projection and $filter operator, where you need to pass the same match condition.

$filter (aggregation) — MongoDB Manual

Or you can use an aggregation query with the $addFields / $project stage as well.

[Liam_Wrigley]

Thanks for that, it was in the right direction. I am fairly new to Mongo and am still struggling a little bit.

I am now retrieving the correct History records, but am missing the parent document, Player.

db.players.aggregate([
  {
    $match: {"history.ts": {$gt: _ts}}
  },
  {
    $project: {
      players: {
        name: true,
        id: true,
        history: {        
          $filter: {
            input: "$history",
            as: "h",
            cond: {$gte: ["$$h.ts", _ts]}
          }
        }
      }
    }
  }
])

with results

[turivishal (Vishal Turi)]

@Liam_Wrigley

I am now retrieving the correct History records, but am missing the parent document, Player.

You can use the $addFields stage instead of $project,
In short, the difference is,

• $project: Passes along the documents with the requested fields to the next stage in the pipeline. The specified fields can be existing fields from the input documents or newly computed fields.
• $addFields: is equivalent to a $project stage that explicitly specifies all existing fields in the input documents and adds the new fields.

Your final query would be,

db.players.aggregate([
    { $match: { "history.ts": { $gt: _ts } } },
    {
        $addFields: {
            history: {        
                $filter: {
                    input: "$history",
                    as: "h",
                    cond: { $gte: ["$$h.ts", _ts] }
                }
            }
        }
    }
])