javascript - How to get an item from an inner array of a model?

Question

I have a schema:

const item = new mongoose.Schema({
  prices: {
    type: [
      {
        addOperation: {
          type: [
            {
              _id,
            },
          ],
        },
      },
    ],
  },
});

Basically, item has array of prices which are also objects, each price has a field (and other fields similar) called addOperation which is also an array, and each addOperation has an id (like prices and item, generated automatically).

My question is, what is the most efficient way to directly query the specific addOperation that I am interested in?

I have the item id, price id and addOperation id.

My "basic instinct" is to just find the item with its id and then loop on the prices and loop on add operations of each price, until I find what I am searching for.

Something like:

const foundItem = await StockItem.findOne({ _id: itemId }).exec();

foundItem._doc.prices.find(({ _id }) => _id === priceId)
              .addOperation.find(({ _id }) => _id === addOperationId);

It would work as there wont ever be that many items to be significant for the performance, but still, I want to do it the "right" way.

What is that "right and optimized" way?

I have tried with:

const addOp = await StockItem.findOne({ _id: id, prices: { _id: priceId }}).exec();

const addOp = await StockItem.findOne({ _id: id, "prices._id": priceId }).exec();

const addOp = await StockItem.findOne({ "prices._id": priceId }).exec();

const addOp= await StockItem.findOne({ _id: id }, { prices: { $elemMatch: { _id: priceId } } }).exec();

Neither worked, to at least give me the price. The first returned null, the rest returned the full StockItem document.

question from:https://stackoverflow.com/questions/65857345/how-to-get-an-item-from-an-inner-array-of-a-model

Answer 1

given data

db.dummy.insert([{"prices":{"id":"a","ops":[{"id":"a1"},{"id":"a2"}]}},{"prices":{"id":"b","ops":[{"id":"b2"},{"id":"a1"}]}},{"prices":[{"id":"c","ops":[]}]}])```
/*
{ ..."prices" : { "id" : "a", "ops" : [ { "id" : "a1" }, { "id" : "a2" } ] } }
{ ..."prices" : { "id" : "b", "ops" : [ { "id" : "b2" }, { "id" : "a1" } ] } }
{ ..."prices" : [ { "id" : "c", "ops" : [ ] } ] }
*/

To fetch your document, you may apply your query:

priceId='a'
opsId='a1'
db.dummy.find({ 'prices.id': priceId, 'prices.ops.id': opsId })
/*
{ ..."prices" : { "id" : "a", "ops" : [ { "id" : "a1" }, { "id" : "a2" } ] } }
*/

To restrict the returned fields of the document, use a projection In particular, the 'array.$' projection

query = { 'prices.id': priceId, 'prices.ops.id': opsId }
db.dummy.find(query, { 'prices.ops.$': 1 })
/*
{ "_id" : ObjectId("600d357fb9f5ffd060ed064e"), "prices" : { "ops" : [ { "id" : "a1" } ] } }
note: only the matching elem in ops is returned
*/

If you want to exclusively have the operation without having to care about traversing the tree (on js side), you may use an aggregate expr (from mongo 4.4)

db.dummy.find(query,{'addOp':{$filter: {input:'$prices.ops', as:'gro', cond:{$eq:['$$gro.id','a1']}}}})
/*
{ "_id" : ObjectId("600d357fb9f5ffd060ed064e"), "addOp" : [ { "id" : "a1" } ] }
note: we have "removed" the intermediary need to traverse "prices"
*/

Finally if we assume only one addOperation can match (which would be natural since we target an id)

$first can get the first elem of the array

filter = {$filter: {input:'$prices.ops', as:'addOp', cond:{$eq:['$$addOp.id','a1']}}}
db.dummy.find(query,{'addOp': {$first: filter}})
{ "_id" : ObjectId("600d357fb9f5ffd060ed064e"), "addOp" : { "id" : "a1" } }

some playground

---

edit: mongoose wise, probably stop yourself at the array.$ to respect the mongoose schema

const mongoose = require('mongoose')
mongoose.connect('mongodb://localhost:27017/dummy')

const Stock = mongoose.model('Stock', {
  prices:{
    id: String,
    ops:[{id:String}]
  }
}, 'dummy');

;(async()=>{
  mongoose.set('debug', true)
  const query = {'prices.ops.id':'a1', 'prices.id': 'a'}
  const v = await Stock.find(query).select('prices.ops.$')
  console.log('s : ', JSON.stringify(v.map(s => s.toJSON()), null, 2))
  mongoose.disconnect()
})()
/*
Mongoose: dummy.find({ 'prices.ops.id': 'a1', 'prices.id': 'a' }, { projection: { 'prices.ops.$': 1 } })
s :  [
  {
    "prices": {
      "ops": [
        {
          "id": "a1"
        }
      ]
    },
    "_id": "600d357fb9f5ffd060ed064e"
  }
]

*/