mongodb add condition after get sum of fileds-CodePudding

i have two tables in mongo db like below

tabel inventories:

id	count	productId
0	10	0
1	0	1
2	4	0
3	0	1

table products:

id	title	inventoryIds
0	test	[0,2]
1	test 1	[1,3]

i want write a query for table products with this condition ==> if sum of count in inventories table for one products greater than zero return all data with this condition at products table else return all data in products

output i want :

Products

id	title	inventoryIds
0	test	[0,2]

because productId = 0 have 14 count in inventories but productId = 1 have zero count in inventories!

thank you for help me

CodePudding user response：

Here's one way to return documents from the products collection given your condition and example.

db.products.aggregate([
  {
    "$lookup": {
      "from": "inventories",
      "localField": "inventories",
      "foreignField": "productId",
      "pipeline": [
        {
          "$group": {
            "_id": null,
            "sum": {
              "$sum": "$count"
            }
          }
        }
      ],
      "as": "inventorySum"
    }
  },
  {
    "$match": {
      "inventorySum.sum": {
        "$gt": 0
      }
    }
  },
  {
    "$unset": [
      "_id",
      "inventorySum"
    ]
  }
])

Try it on mongoplayground.net.

Result:

[
  {
    "id": 0,
    "inventories": [
      0,
      2
    ],
    "title": "test"
  }
]