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?

are you sure this is not a mistake? Id : 12345-7 Code: 10 Availability : in stock, 12345-7 doesn't have Code 10 at all — Takis
– Takis, Commented Nov 10, 2021 at 23:35
@Takis_ It was a typo, I've edited the answer and now is correct! Thanks — OdiumPura
– OdiumPura, Commented Nov 10, 2021 at 23:48
I'll test now, as soon as i finish i ll post here, just a moment — OdiumPura
– OdiumPura, Commented Nov 10, 2021 at 23:59

Takis · Accepted Answer · 2022-03-17 23:34:50Z

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

edited Mar 17, 2022 at 23:34

answered Nov 10, 2021 at 22:58

Takis

8,7552 gold badges17 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

OdiumPura Over a year ago

I'm running your answer on my collection with 2kk documents.

OdiumPura Over a year ago

It Worked, 3 minutes with spark! Thank you so much!

Takis Over a year ago

i tried one more query, if you see this maybe try it and send some feedback, on which one was faster.

OdiumPura Over a year ago

is it possible to set the Stock.Qty at output too?

Takis Over a year ago

i added one more query, to save the Qty also and you see it in results, i think this is what you mean by set, if you want to change the Qty you can do that also, but i think you meant just keep the Qty they had.

|

Collectives™ on Stack Overflow

Aggregate to insert array values in a document

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related