Aggregate to insert array values in a document

Question

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?

Answer 1

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

PlayMongo

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}}])

---

Edit1 (array operators)

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

PlayMongo

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"}}])

---

Edit2(array operators and keep qty)

If you want to keep the quantity also you can try this. Its a bit different maybe its faster than both but i cant test it.

If code exists on an Id it will have the quantity it had, else it will have quantity 0 (you can put any valua if code was not found, or even remove the field completely) change this part "Qty": 0

Query

• map Stock, the codes that are in code-list, becomes documents with Availability in or out on stoke, the other values map to null
• find the missing-codes= codes that are not in Stock like the first doesn't even have the code 10, and make the documents with out-of-stock and qty=0
• concat found codes in Stock and missing codes
• unwind and replace root

PlayMongo

aggregate(
[{"$set": {"codes": ["1", "10"]}},
  {"$set": 
    {"Stock": 
      {"$map": 
        {"input": "$Stock",
          "in": 
          {"$switch": 
            {"branches": 
              [{"case": 
                  {"$and": 
                    [{"$gte": ["$$this.Qty", 3]},
                      {"$in": ["$$this.Code", "$codes"]}]},
                  "then": 
                  {"$mergeObjects": 
                    ["$$this", {"Availability": "in stock", "Id": "$Id"}]}},
                {"case": {"$in": ["$$this.Code", "$codes"]},
                  "then": 
                  {"$mergeObjects": 
                    ["$$this", {"Availability": "out of stock", "Id": "$Id"}]}}],
              "default": null}}}}}},
  {"$set": 
    {"Stock": 
      {"$filter": {"input": "$Stock", "cond": {"$ne": ["$$this", null]}}},
      "missing-codes": 
      {"$map": 
        {"input": {"$setDifference": ["$codes", "$Stock.Code"]},
          "in": 
          {"Availability": "out of stock",
            "Id": "$Id",
            "Code": "$$this",
            "Qty": 0}}}}},
  {"$project": 
    {"_id": 0, "Stock": {"$concatArrays": ["$Stock", "$missing-codes"]}}},
  {"$unwind": {"path": "$Stock"}},
  {"$replaceRoot": {"newRoot": "$Stock"}}])