I have the following collection

{
    "Id" : "12345-7",
    },
    "Stock" : [ 
            {
                "Code" : "1",
                "Qty" : 5.0
            }, 
            {
                "Code" : "3",
                "Qty" : 7.0
            }
        ]
    }
    { 
    "Id" : "22222-0",
    "Stock" : [ 
            {
                "Code" : "3",
                "Qty" : 10.0
            }, 
            {
                "Code" : "10",
                "Qty" : 2.0
            }
         ]
    }

And I have this list:

{1 , 10}

My expected output is:

Id : 12345-7
Code: 1
Availability : in stock


Id : 12345-7
Code: 10
Availability : out of stock

Id : 12345-7
Code: 1
Availability : out of stock


Id : 12345-7
Code: 10
Availability : out of stock

Basically, if the values of the list doesn't exists in the Stock.Code I need to generate a document with the value of the list. The last document of the expected output is out of stock because there is a rule that is in stock if Qty >= 3

I've tried this

db.Collection.aggregate([
{
  "$unwind" : "$Stock"
},
{
  "$match" : 
  {
       "$in" : ["Stock.Code", list]
  }
},
{
       "$projoect" :
       {
             "Id" : "$Id",
             "Code" : "$Stock.Code",
             "Availability" :
             {
                "$cond"
                        ...
             }
       }
}
])

My doubts are:

• Can I do this using only one aggregate?
• What is the best way to do this? I'm using pyMongo to code that, is better to do this in database side or code side
• How Can I change my aggregate Code and Condition to match my expected output?

CodePudding user response：

Query

• add the list(Code) in each document
• unwind Code
• filter each array for the Code to exist in array and with qty>=3
• if filter result is empty the Code is out of stock
• else it is in stock

Test code here

aggregate(
[{"$set": {"Code": ["1", "10"]}},
 {"$unwind": {"path": "$Code"}},
 {"$set": 
   {"Availability": 
     {"$cond": 
       [{"$eq": 
         [{"$filter": 
            {"input": "$Stock",
             "cond": 
               {"$and": 
                 [{"$gte": ["$$this.Qty", 3]},
                  {"$eq": ["$$this.Code", "$Code"]}]}}},
           []]},
       "out of stock","in stock"]}}},
  {"$project": {"_id": 0, "Id": 1, "Code": 1, "Availability": 1}}])

Edit

If you want try this also that tries to keep things local, and unwinds only when done.

Query

• from stock keep the codes that are also in the code-list
• out-stock codes = the missing codes (as documents)
• in-stock codes = the found codes (as documents)
• concat in stock, without stock, project
• unwind and replace root with those subdocuments

*the main difference is that works local in each document, and unwinds only when done, maybe will be faster

Test code here

aggregate(
[{"$set": {"codes": ["1", "10"]}},
  {"$set": 
    {"Stock": 
      {"$reduce": 
        {"input": "$Stock",
          "initialValue": [],
          "in": 
          {"$cond": 
            [{"$and": 
                [{"$gte": ["$$this.Qty", 3]},
                  {"$in": ["$$this.Code", "$codes"]}]},
              {"$concatArrays": ["$$value", ["$$this.Code"]]}, "$$value"]}}}}},
  {"$set": 
    {"out-stock": 
      {"$map": 
        {"input": {"$setDifference": ["$codes", "$Stock"]},
          "in": 
          {"Id": "$Id", "Code": "$$this", "Availability": "out of stock"}}}}},
  {"$set": 
    {"in-stock": 
      {"$map": 
        {"input": "$Stock",
          "in": 
          {"Id": "$Id", "Code": "$$this", "Availability": "in stock"}}}}},
  {"$project": 
    {"stock-info": {"$concatArrays": ["$out-stock", "$in-stock"]}}},
  {"$unwind": {"path": "$stock-info"}},
  {"$replaceRoot": {"newRoot": "$stock-info"}}])