(MongoDB) Intersect the output of two query pipelines

Jord 0
Viewed 34

I’m trying to create a MongoDB pipeline that does the same as this SQL query:

SELECT DISTINCT A.ID FROM
  (Query_A) A 
  INNER JOIN 
  (Query_B) B
  ON A.ID = B.ID

I’ve come up with the following code to run both query pipelines:

[
    {
        "$facet": {
            "query_a": [...],
            "query_b": [...]
        }
    },
   ...
]

These pipelines return IDs and I want to get an intersection of these IDs ("$query_a.ID" equal to “$query_b.ID”).

EDIT:

The result of the $facet step is:

[
  {
    queryA: [
      {"ID": "c80ea2cb-3272-77ae-8f46-d95de600c5bf",
        "date": "1"},
      {"ID": "cdbcc129-548a-9d51-895a-1538200664e6",
        "date": "2"},
      {"ID": "a4ece1ba-42ae-e735-17b0-f619daa506f9",
        "date": "3"}
    ],
    queryB: [
      {"ID": "c80ea2cb-3272-77ae-8f46-d95de600c5bg",
        "date": "4"},
      {"ID": "cdbcc129-548a-9d51-895a-1538200664e6",
        "date": "5"},
      {"ID": "a4ece1ba-42ae-e735-17b0-f619daa506f9",
        "date": "6"}
    ]
  }
]

But my requested result is:

[
  {
    "dateA": "2",
    "dateB": "5",
    "intersection": "cdbcc129-548a-9d51-895a-1538200664e6"
  },
  {
    "dateA": "3",
    "dateB": "6",
    "intersection": "a4ece1ba-42ae-e735-17b0-f619daa506f9"
  }
]
aggregation inner-join join mysql mongodb
1 Answers

One option is to use $setIntersection and then find the matching dates:

db.collection.aggregate([
  {$project: {
      intersection: {$setIntersection: ["$queryA.ID", "$queryB.ID"]},
      queryA: 1, queryB: 1}
  },
  {$project: {
      _id: 0,
      data: {
        $map: {
          input: "$intersection",
          in: {intersection: "$$this",
            queryA: {
              $first: {$filter: {
                  input: "$queryA",
                  as: "item",
                  cond: {$eq: ["$$item.ID", "$$this"]}
              }}
            },
            queryB: {
              $first: {$filter: {
                  input: "$queryB",
                  as: "item",
                  cond: {$eq: ["$$item.ID", "$$this"]}
              }}
            }
          }
        }
      }
    }
  },
  {$unwind: "$data"},
  {$replaceRoot: {newRoot: "$data"}},
  {$project: {intersection: 1, dateA: "$queryA.date", dateB: "$queryB.date"}}
])

See how it works on the playground example

Another option, since you want document per intersected ID, is to use $unwind and then $group to find common ID:

db.collection.aggregate([
  {$project: {
      all: {$concatArrays: [
          {$map: {
              input: "$queryA",
              in: {ID: "$$this.ID", dateA: "$$this.date"}
           }},
          {$map: {
              input: "$queryB",
              in: {ID: "$$this.ID", dateB: "$$this.date"}
          }}
      ]}
  }},
  {$unwind: "$all"},
  {$group: {
      _id: "$all.ID",
      dateA: {$push: "$all.dateA"},
      dateB: {$push: "$all.dateB"}
  }},
  {$project: {
      dateA: {$first: "$dateA"},
      dateB: {$first: "$dateB"},
      intersection: "$_id",
      _id: 0
  }},
  {$match: {dateA: {$exists: true}, dateB: {$exists: true}}}
])

See how it works on the playground example

nimrod serok0
Related