2

currently, I am struggling with how the MongoDB document system works. I want to fetch array elements with an auto-generated id but how to fetch that specific data that I don't know.

my current schema is

const ItemPricesSchema = new mongoose.Schema({
    _id : {
        type: String
    },
    ItemsPrices: {
        type: [{
            barcode : {
                type: String
            },
            itemName : {
                type: String
            },
            price : {
                type: String
            }
        }]
    }
});

current data is stored in this way

{
    "_id": "sha@c.c",
    "ItemsPrices": [
        {
            "barcode": "345345",
            "itemName": "maggie",
            "price": "45",
            "_id": "620a971e11120abbde5f4c3a"
        },
        {
            "barcode": "356345",
            "itemName": "monster",
            "price": "70",
            "_id": "620a971e11120abbde5f4c3b"
        }
    ],
    "__v": 0
}

what I want to achieve is that I want to find array elements through ids

if I want a specific array element with id "620a971e11120abbde5f4c3b" what should I do?? I have tried $unwind , $in, $match...

the result should be like

{
    "_id": "sha@c.c",
    "ItemsPrices": [
        {
            "barcode": "356345",
            "itemName": "monster",
            "price": "70",
            "_id": "620a971e11120abbde5f4c3b"
        }
    ],
    "__v": 0
}

what I tried is like this from the answer

router.get('/filter/:id', async (req, res) => {
    try {
        const item = await ItemPricesSchema.aggregate([
            {$project: {
                "ItemsPrices": {
                    $filter: {
                        input: "$ItemsPrices",
                        as: "item",
                        cond: {
                            $eq: [
                                "$$item._id",
                                "620a8dd1c88ae3eb88a8107a"
                               
                            ]
                        }

                    }
    
                }
    
            }
            }
        ])
        res.json(item);
        console.log(item);
        
    } catch (error) {
        res.status(500).json({message: error.message});
        
    }
})

and returns something like this (Empty arrays)

[
  {
    "_id": "xvz@zyx.z",
    "ItemsPrices": []
  },
  {
    "_id": "zxc@xc.czx",
    "ItemsPrices: []
  },
  {
    "_id": "asd@asd.asd",
    "ItemsPrices": []
  },
  {
    "_id": "qwe@qwe.qwe",
    "ItemsPrices": []
  }
]

but If I search for price $$item.price


  cond: {
               $eq: [
                  "$$item.price",
                  "30"    
              ]
       }

it returns the perfect output

[
  {
    "_id": "xvz@zyx.z",
    "ItemsPrices": []
  },
  {
    "_id": "zxc@xc.czx",
    "ItemsPrices: []
  },
  {
    "_id": "asd@asd.asd",
    "ItemsPrices": []
  },
  {
    "_id": "qwe@qwe.qwe",
    "ItemsPrices": [
        {
           "barcode":"234456345",
           "price":"30",
           "itemName":"monster",
           "_id":"620a8dd1c88ae3eb88a8107a"
        }
     ]
  }
]
Shashank Mistry
  • 209
  • 1
  • 12
  • 1
    @FekiHamza can you explain in detail, I don't understand how this query will find the second element of an array – Shashank Mistry Feb 14 '22 at 19:08
  • I'm sorry, I misunderstood your question, does this help you ? https://stackoverflow.com/questions/43852393/mongodb-find-match-query-on-array-field-doesnt-work – Feki Hamza Feb 14 '22 at 19:11
  • 1
    @FekiHamza sorry mate, I am a beginner and this answer does not work for me, I tried but it just returns an empty array – Shashank Mistry Feb 14 '22 at 19:21

2 Answers2

3

You can do an aggregation with $project and apply $filter on the array part. In mongoose you can apply the aggregation query in a more or less similar way https://mongoosejs.com/docs/api/aggregate.html

db.collection.aggregate([
  {
    $project: {
      "ItemsPrices": {
        $filter: {
          input: "$ItemsPrices",
          as: "item",
          cond: {
            $eq: [
              "$$item._id",
              mongoose.Types.ObjectId("620a971e11120abbde5f4c3b")
            ]
          }
        }
      },
      "__v": 1  //when projecting 1 means in the final result this field appears
    }
  }
])

more examples

demo

cmgchess
  • 7,996
  • 37
  • 44
  • 62
  • 1
    Your approach works perfectly with all other strings in the array but not with _id. this is an auto-generated id and when we run a similar query it returns an empty array but if I search $$Item.price it works perfectly fine but if I write $$Item._id does not work. maybe because I am coding this in js or something Idk – Shashank Mistry Feb 15 '22 at 07:11
  • 1
    can you suggest the proper answer because I am new to mongo and don't know why this is the issue and how to solve this, you can see the main question that I changed above – Shashank Mistry Feb 15 '22 at 07:27
  • 1
    can you also try `mongoose.Types.ObjectId("620a971e11120abbde5f4c3b")` – cmgchess Feb 15 '22 at 07:45
  • @ShashankMistry did it solve it so I can update the answer – cmgchess Feb 15 '22 at 09:06
1

Option 1:

Use $filter in an aggregation query as explained by cmgchess

Option 2:

If you only want one object from array you can use $elemMatch like this:

db.collection.find({
  "ItemsPrices._id": "620a971e11120abbde5f4c3b"
},
{
  "ItemsPrices": {
    "$elemMatch": {
      "_id": "620a971e11120abbde5f4c3b"
    }
  }
})

Example here

But take care, using $elemMatch only the first element is returned. Check this other example where there are two objects with the desired _id but only returns one.

As said before, if you only one (or only exists one) maybe you can use find and $elemMatch to avoid a filter by the entire array. But if can be multiple values use $filter.

J.F.
  • 13,927
  • 9
  • 27
  • 65