MongoDb Join Lists in aggregate Query

Question

I have a collection with documents are like this:

{
    "date" : 20200817,
    "items" : [ 
        {
            "name" : "item1", "values" : ["val1", "val2", "val3"]
        }, 
        {
            "name" : "item2", "values" : ["val1", "val3", "val4", "val5"]
        }, 
        {
            "name" : "item3", "values" : ["val1", "val3"]
        }
    ]
}

I want to get intersection (∩) values that items.name in ["item1", "item2"] like this

["val1", "val3"]

I have this query :

db.output.aggregate([{
  $project: {
    output: {
      $map: {
        input: {
          $filter: {
            input: "$lists",
            cond: {$in: ["$$this.name", ["item1", "item2"]]}
          }
        }, in: "$$this.values"
      }
    }
  }
},
  {
    "$project": {
      "output": {"$setUnion": "$output"}
    }
  }])

but the result is

{
    "output" : [ 
        ["val1", "val2", "val3"], 
        ["val1", "val3", "val4", "val5"]
    ]
}

Where is my query wrong?

What can I do?

Thanks

turivishal · Accepted Answer · 2020-09-06 18:56:32Z

1

You can try $setUnion inside $reduce,

db.output.aggregate([
  {
    $project: {
      output: {
        $reduce: {
          input: "$items",
          initialValue: [],
          in: {
            $setUnion: [
              "$$value",
              {
                $cond: [
                  { $in: ["$$this.name", ["item1", "item2"]] },
                  "$$this.values",
                  []
                ]
              }
            ]
          }
        }
      }
    }
  }
])

Playground

Your second edited question,

You can try $setIntersection in your implemented query, inside second $project pipeline stage.

Playground

edited Sep 6, 2020 at 18:56

answered Sep 6, 2020 at 16:42

turivishal

36.4k7 gold badges48 silver badges69 bronze badges

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

6 Comments

Morteza Over a year ago

thanks for your answer. for get the common values (["values1", "val3"]) of list what should i do ?

turivishal Over a year ago

i am not getting you, i can not see this in your question, can you explain.

Morteza Over a year ago

it was a new question. I want to get common values that items.name in ["item1", "item2"] like this ["val1", "val3"]

Morteza Over a year ago

I want to get intersection (∩) of values

Morteza Over a year ago

for my second edited question if i want to get values that items.name in ["item1"] the output return null

|

Collectives™ on Stack Overflow

MongoDb Join Lists in aggregate Query

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related