I have in database the next array of objects

[
  {
     price: "1"
     type: "buy",
  },
  {
     price: "2"
     type: "buy",
  },
  {
     price: "3"
     type: "sell"
  },
  {
     price: "4"
     type: "sell"
  }
]

How can I make an aggregation to get an array of arrays with all possible combinations (price could be random number)

hide items with the highest buy price

and sort them, so the items with the largest price difference are at the top

[
  [
      {
         price: "1"
         type: "buy",
      },
      {
         price: "4"
         type: "sell"
      }
  ],
  [
      {
         price: "1"
         type: "buy",
      },
      {
         price: "3"
         type: "sell"
      },
  ],
  [
      {
         price: "2"
         type: "buy",
      },
      {
         price: "4"
         type: "sell"
      }
  ],
  [
      {
         price: "2"
         type: "buy",
      },
      {
         price: "3"
         type: "sell"
      },
  ],
]

CodePudding user response：

Query

• self-lookup and match if not same id (avoid same item on pair)
• map to add to the join result the parent item (the pair will always have as first member the one with the biggest price (needed for later))
• unwind pairs
• group pairs to remove the duplicates (each pair will appear 2 times, but its sorted from the map so we can group by it)
• find the price difference, sort by descending, unset the field

*i am not sure it does all you need, because i didn't understand the part hide items with the highest buy price the highest buy price is "2" and in your results you have this items

Playmongo

coll.aggregate(
[{"$lookup": 
   {"from": "coll",
    "pipeline": 
     [{"$match": {"$expr": {"$ne": ["$$id", "$_id"]}}},
       {"$project": {"_id": 0, "price": 1, "type": 1}}],
    "as": "pairs",
    "let": {"id": "$_id"}}},
 {"$project": 
   {"_id": 0,
    "pairs": 
     {"$map": 
       {"input": "$pairs",
        "in": 
         {"$cond": 
           [{"$gt": ["$$this.price", "$price"]},
             [{"price": "$price", "type": "$type"}, "$$this"],
             ["$$this", {"price": "$price", "type": "$type"}]]}}}}},
 {"$unwind": "$pairs"}, {"$group": {"_id": "$pairs"}},
 {"$project": {"_id": 0, "pair": "$_id"}},
 {"$set": 
   {"priceDifference": 
     {"$abs": 
       {"$subtract": 
         [{"$toDouble": 
             {"$getField": 
               {"field": "price", "input": {"$arrayElemAt": ["$pairs", 0]}}}},
           {"$toDouble": 
             {"$getField": 
               {"field": "price",
                "input": {"$arrayElemAt": ["$pairs", 1]}}}}]}}}},
 {"$sort": {"priceDifference": -1}},
 {"$unset": ["priceDifference"]}])