I have a bunch of documents inside a mongodb collection with the following structure:

{
  "_id": {
    "$oid": "ddksdk"
  },
"date": {"$date": { "$numberLong": "1627862400000"}
},
"prices": [{"_id": {"$oid": "ddks11"}, "inventoryCode": "JFXS", "price": 52},
           {"_id": {"$oid": "ddks12"}, "inventoryCode": "USSW", "price": 102}]
}

I was looking forward to set an statement in order to obtain the document with a specifc date , and within that date I was looking forward to filter by an inventoryCode within the array of prices to obtain as an output the object filtered by date and inventoryCode.

The query I tried goes as follows:

{
  $and: [
    {
      date: { $eq: "2021-08-02T00:00:00.000 00:00") },
    },
    {
      prices: {
        $elemMatch: {
          inventoryCode: { $eq: "USSW" },
        },
      },
    },
  ],
}

with this statement I am actually filtering by date , obtaining the corresponding doc with that particular date but i am not filtering within the array by the specific inventoryCode . How could I achieve this?

The expected output i would like to receive is :

{"_id": {"$oid": "ddks12"}, "inventoryCode": "USSW", "price": 102}

CodePudding user response：

Query

• keeps the documents with the specific date
• filter to keep only those with inventoryCode= USSW
• keeps the documents where prices is not empty
• replace the root with the first member of prices (to get the expected output)
• to be fast add a index on date id you don't have

Playmongo

aggregate(
[{"$match":{"$expr":{"$eq":["$date", ISODate("2021-08-02T00:00:00Z")]}}},
 {"$set":
   {"prices":
     {"$filter":
       {"input":"$prices",
        "cond":{"$eq":["$$this.inventoryCode", "USSW"]}}}}},
 {"$match":{"$expr":{"$ne":["$prices", []]}}},
 {"$replaceRoot":{"newRoot":{"$first":"$prices"}}}])