Aggregation query operator excluding values with repeated key MongoDB

Question

I have implemented a system where are two type of Prices:

Prices specific for determinated Clients (clientId, productId)
Prices that are common for many Clients (feeId, productId)

My schema is flexible, that is that there are prices that have clientId (1) and prices that has feeId (2). But all of them must have a productId.

There could be only one Price with the same productId and the same clientId.

var schema = new Schema({
    feeId: {type: Schema.Types.ObjectId, ref: 'Fee'},    
    clientId: {type: Schema.Types.ObjectId, ref: 'Client'},
    productId: {type: Schema.Types.ObjectId, ref: 'Product', required:true},
    price: {type: Number, required: true}
});

My goal is to obtain the prices for a specific client and product family, and the query should return the prices of the client (price matching clientId), in case it has, and the prices of the products that belongs to that family (price with feeId and without clientId, matching familyId).

Right now i have this:

  Price.aggregate([
    {
      $lookup:
        {
          'from': 'products',
          'localField': 'productId',
          'foreignField': '_id',
          'as': 'product'
        }
    },
    {
      $unwind: '$product'
    },
    {
      $match: {
        $and: [
          {
            "product.family": new mongoose.Types.ObjectId(req.params.familyId)
          },
          {
            $or: [
              {"clientId": new mongoose.Types.ObjectId(req.params.clientId)},
              {"feeId": {$exists: true, $ne: null}}
            ]
          }

        ]
      }
    },
  ])

With that query I obtain the following results:

    {
  "title": "Precio",
  "body": [
    {
      "_id": "5a0974c7347eff02c784e5bd",
      "price": 8,
      "productId": "5a0970f9347eff02c784e5b1",
      "clientId": "59f350dfb8634f659680299e",
      "product": {
        "_id": "5a0970f9347eff02c784e5b1",
        "erpId": "12345",
        "name": "Entrecot Entero",
        "description": "Pieza entera",
        "weight": null,
        "photoURL": "https://okelan.s3.amazonaws.com/x14-FILETES-ENTRECOT-250gr_PREMIUM-e1491499466438-n6oowu8z2p0d877vgp4eioezbckou4u636uwvc35mo.jpg.pagespeed.ic.l38ZGRUwf6.jpg",
        "familyId": "59e721e8b8634f65967eabf4"
      },
      "client": {
        "_id": "59f350dfb8634f659680299e",
        "contactName": "Pablo 2",
        "NIF": "12345678Z",
        "email": "[email protected]",
        "password": "$2a$10$gXn.G/q4ar3wbjSyBCu0XOVud0HL5l3d.2WXNab4cCfB3uToncQLm",
        "orders": [],
        "erpId": "14123",
        "IBAN": "ES6621000418401234567891",
        "photoURL": "https://okelan.s3.amazonaws.com/logo.png",
        "company": "Cárnicas Paco",
        "deliveryMode": "Prueba",
        "paymentMode": "Prueba",
        "active": true,
        "feeId": "59e78d76b8634f65967ec1b3",
        "phone": "620859192",
        "fee": "59e78d76b8634f65967ec1b3",
        "shippingAddresses": [
          {
            "province": "Asturias",
            "city": "Grado",
            "postalCode": 33820,
            "street": "Calle Asturias, 14 3°DCHA",
            "_id": "5a097de74c468c17af16d18e"
          },
          {
            "province": "adfasd",
            "city": "adsfasd",
            "postalCode": 23423,
            "street": "asdfasd",
            "_id": "5a097de74c468c17af16d18d"
          }
        ],
        "billingAddress": {
          "province": "adfasd",
          "city": "adsfasd",
          "postalCode": 23423,
          "street": "asdfasd"
        }
      }
    },
    {
      "_id": "5a0c0d91e5127378570f13b0",
      "price": 14,
      "productId": "5a09ac68e640a63e0520301f",
      "clientId": "59f350dfb8634f659680299e",
      "product": {
        "_id": "5a09ac68e640a63e0520301f",
        "erpId": "2355",
        "name": "Prueba",
        "description": "25",
        "weight": 25,
        "photoURL": "https://okelan.s3.amazonaws.com/camera.jpg",
        "familyId": "59e721e8b8634f65967eabf4"
      },
      "client": {
        "_id": "59f350dfb8634f659680299e",
        "contactName": "Pablo 2",
        "NIF": "12345678Z",
        "email": "[email protected]",
        "password": "$2a$10$gXn.G/q4ar3wbjSyBCu0XOVud0HL5l3d.2WXNab4cCfB3uToncQLm",
        "orders": [],
        "erpId": "14123",
        "IBAN": "ES6621000418401234567891",
        "photoURL": "https://okelan.s3.amazonaws.com/logo.png",
        "company": "Cárnicas Paco",
        "deliveryMode": "Prueba",
        "paymentMode": "Prueba",
        "active": true,
        "feeId": "59e78d76b8634f65967ec1b3",
        "phone": "620859192",
        "fee": "59e78d76b8634f65967ec1b3",
        "shippingAddresses": [
          {
            "province": "Asturias",
            "city": "Grado",
            "postalCode": 33820,
            "street": "Calle Asturias, 14 3°DCHA",
            "_id": "5a097de74c468c17af16d18e"
          },
          {
            "province": "adfasd",
            "city": "adsfasd",
            "postalCode": 23423,
            "street": "asdfasd",
            "_id": "5a097de74c468c17af16d18d"
          }
        ],
        "billingAddress": {
          "province": "adfasd",
          "city": "adsfasd",
          "postalCode": 23423,
          "street": "asdfasd"
        }
      }
    }
  ]
}

Here you can find some source documents: https://pastebin.com/qBDqKa5y

Could be possible to do it in just one query with an aggregation operator?

Thanks to @dnickless for the final solution:

  Price.aggregate([
{
  $lookup:
    {
      'from': 'products',
      'localField': 'productId',
      'foreignField': '_id',
      'as': 'product'
    }
},
{
  $unwind: '$product'
},
{
  $lookup:
    {
      'from': 'clients',
      'localField': 'clientId',
      'foreignField': '_id',
      'as': 'client'
    }
},
{
  $unwind: '$client'
},
{
  $match: {
    $and: [
      {
        "product.familyId": new mongoose.Types.ObjectId(req.params.familyId)
      },
      {
        $or: [
          {"clientId": new mongoose.Types.ObjectId(req.params.clientId)},
          {"feeId": new mongoose.Types.ObjectId(req.body.feeId)}
        ]
      }
    ]
  }
},
{
  $sort:
    {
      "clientId":
        -1
    }
},
{
  $group: {

    _id: {
      "productId":
        "$productId"
    }
    ,
    "doc":
      {
        $first: "$$ROOT"
      }
  }
},
{
  $project: { // restore target structure
    "_id": "$doc._id", // you may or may not need this field
    "price": "$doc.price",
    "productId": "$_id.productId",
    "clientId": "$doc.clientId",
    "feeId": "$doc.feeId",
    "promoPrice": "$doc.promoPrice"
  }
},
{
  $lookup:
    {
      'from': 'products',
      'localField': 'productId',
      'foreignField': '_id',
      'as': 'product'
    }
},
{
  $unwind: '$product'
},
{
  $lookup:
    {
      'from': 'clients',
      'localField': 'clientId',
      'foreignField': '_id',
      'as': 'client'
    }
},
{
  $unwind: '$client'
}

])

@dnickless I have posted some examples of the desired documents — Pablo García Miranda
– Pablo García Miranda, Commented Nov 15, 2017 at 8:49
Some source documents is what we need, not the results you're getting right now. — dnickless
– dnickless, Commented Nov 15, 2017 at 9:33
Also, there must be something wrong. Your last $match stage will only return documents with a ´clientId´ that has a specific value so not the first and last document in your sample...?! — dnickless
– dnickless, Commented Nov 15, 2017 at 9:38

dnickless · Accepted Answer · 2017-11-15 10:27:38Z

1

Ok, based on what I think I've understood about your issue, here is something that should work together with some explanations:

Price.aggregate([{
    $match: { // keep this as your first stage as it will leverage an index on "clientId" (there should be one!) and limit the number of documents to process by the following stages nicely
        $or: [ // we want to consider all prices that
            { "clientId": new mongoose.Types.ObjectId(req.params.clientId) }, // are either for the chose client
            { "clientId": null } // or null - so valid for all clients
        ]
    }
}, {
    $sort: {
        "clientId": -1 // nulls will be at the end of our list so we can use $first later in the next stage in to pick the right price that should take precedence
    }
}, {
    $group: {
        _id: { "productId": "$productId" }, // look at all products individually
        "doc": { // take the first document per "productId" that hit the $group stage (the preceding $sort stage is needed for this to do the right thing)

            $first: "$$ROOT"
        }
    }
}, {
    $project: { // restore target structure
        "_id": "$doc._id", // you may or may not need this field
        "price": "$doc.price",
        "productId": "$_id.productId",
        "clientId": "$doc.clientId",
    }
} /* here you may want to include the $lookup stage if  you need the product information */ ])

answered Nov 15, 2017 at 10:27

dnickless

10.9k1 gold badge22 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pablo García Miranda Over a year ago

Thanks so much! It worked as i wanted. I will post my final solution in the post.

Collectives™ on Stack Overflow

Aggregation query operator excluding values with repeated key MongoDB

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related