MongoDB - Get an unique array from two queries

Question

First of all sorry if the title does not reflect what I really need to do.

Imagine a collection that represents what products sell which vendor. Let´s simplify like:

{_id, productID, VendorID, Price, Stock}

For example, we could have:

{_id: 1 ,productID: 1, vendorID: A, price: 0, stock: 0},
{_id: 2 ,productID: 2, vendorID: A, price: 0, stock: 0},
{_id: 3 ,productID: 3, vendorID: A, price: 0, stock: 0},
{_id: 4 ,productID: 4, vendorID: A, price: 0, stock: 0},
{_id: 5 ,productID: 1, vendorID: B, price: 0, stock: 19},
{_id: 6 ,productID: 3, vendorID: B, price: 0, stock: 21}

The idea is that Vendor A is the Super Admin and the one who controls which products can be sold in the marketplace. That´s why all products sold by A have price = 0 and stock = 0.

What I am trying to get within the same query is. When Vendor B is logged:

List all the items that A is selling.
But if a product is sold by B, then I should return it instead of the A-one.

The result would look like:

    {_id: 2 ,productID: 2, vendorID: A, price: 0, stock: 0},
    {_id: 4 ,productID: 4, vendorID: A, price: 0, stock: 0},
    {_id: 5 ,productID: 1, vendorID: B, price: 0, stock: 19},
    {_id: 6 ,productID: 3, vendorID: B, price: 0, stock: 21}

Do you have any idea how I can do it in a shot?

I can do the filter in the frontend but I would prefer doing like this in order to avoid problems with pagination for example.

So, your desired response for products with IDs 1,2,3,4 is array of records with IDs 5,2,6,4 right? — Xeelley
– Xeelley, Commented Jan 11, 2022 at 11:43

Xeelley · Accepted Answer · 2022-01-11 12:59:15Z

If you are sure that all products sold by A always have price = 0 and stock = 0, you can do the following trick:

db.collection.aggregate({
  $match: {
    vendorID: {
      $in: [
        "A",
        "B"
      ]
    }
  }
},
{
  $sort: {
    productID: 1,
    price: -1
  }
},
{
  "$group": {
    "_id": "$productID",
    "product": {
      $first: "$$ROOT"
    }
  }
})

Mongo playground

by $match you are looking for all products owned by default vendor (A) and target vendor (B).
by $sort you group products by productID and by price in each group, so product sold by A will be last one in each group
by $group you extract product from each group
using $first it will be first product (with higher price, so product sold by A will be matched only if no other products with same ID were sold).

DB documents will be aggregated by $$ROOT into product field, so you can map them using:

// (javascript)
const productDocuments = aggregationResult.map(row => row.product);

Notice: product sold by A still can be extracted even if B sold product with same ID in cases when B's product also have price = 0.

Collectives™ on Stack Overflow

MongoDB - Get an unique array from two queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related