I am new to MongoDB. I have pymongo to access mongodb.

The data is in this format

{
shop:"shop A",
city:"xxx",
electronics:["phone","laptop","television"],
stationary:["pen","pencil","eraser"],
furniture:["sofa","stool"]
}

{
shop: "shop B",
city:"xxx",
electronics:["camera","radio","phone","television"],
stationary:["pen","pencil","eraser","sharpner"],
furniture:["chair","table","sofa"]
}

...

I want to get the intersection of electronics, stationary and furniture in all shops of city xxx.

Desired output:

{
electronics:["phone","television"],
stationary:["pen","pencil","eraser"],
furniture:["sofa"]
}

Should I use aggregation to achieve this? Please help me with the query.

CodePudding user response：

Query

• if $setIntersection could be used as accumulator we could group and intersect those, but it cant be used as accumulator
• group by city and push those arrays
• reduce in each and intersect (its 3x the same code)

Playmongo

aggregate(
[{"$group": 
   {"_id": "$city",
    "electronics": {"$push": "$electronics"},
    "stationary": {"$push": "$stationary"},
    "furniture": {"$push": "$furniture"}}},
 {"$set": 
   {"electronics": 
     {"$reduce": 
       {"input": "$electronics",
        "initialValue": null,
        "in": 
         {"$cond": 
           [{"$eq": ["$$value", null]}, "$$this",
             {"$setIntersection": ["$$value", "$$this"]}]}}},
    "stationary": 
     {"$reduce": 
       {"input": "$stationary",
        "initialValue": null,
        "in": 
         {"$cond": 
           [{"$eq": ["$$value", null]}, "$$this",
             {"$setIntersection": ["$$value", "$$this"]}]}}},
    "furniture": 
     {"$reduce": 
       {"input": "$furniture",
        "initialValue": null,
        "in": 
         {"$cond": 
           [{"$eq": ["$$value", null]}, "$$this",
             {"$setIntersection": ["$$value", "$$this"]}]}}}}}])

Edit

The above is for all cities, to find those, if you want only for one specific city you can replace the first group with this 2 stages

{"$match": {"city": {"$eq": "xxx"}}},
{"$group": 
   {"_id": null,
    "electronics": {"$push": "$electronics"},
    "stationary": {"$push": "$stationary"},
    "furniture": {"$push": "$furniture"}}}