lookup compare collection data with array in aggregate result in mongo DB

Question

i want to compare collection with array in aggregate result

i have following two collection.

chat collection

chat.tags is a array value in reference key come from the tags collection.

"chat": [
    {
      "id": "test1",
      "tags": [
        "AAA",
        "BBB",
        "CCC",
        "AAA"
      ]
    },
    {
      "id": "test2",
      "tags": [
        "AAA",
        "BBB",
        "CCC"
      ]
    }
  ]

tag collection

  "tag": [
    {
      "id": "1234",
      "key": "AAA",
      "name": "a"
    },
    {
      "id": "1235",
      "key": "BBB",
      "name": "b"
    },
    {
      "id": "1236",
      "key": "CCC",
      "name": "c"
    },
    {
      "id": "1237",
      "key": "DDD",
      "name": "d"
    },
  ]

i want to result that id is "test1" and unique tags in chat collection.

i want to following result using with mongo aggregate. Is it possible with from, let, pipeline when using lookup?

[
  {
    "chat": [
      {
        "id": "test1",
        "setTags": [
          "AAA",
          "BBB",
          "CCC"
        ]
      }
    ],
    "tag": [
    {
       "id": "1234",
       "key": "AAA",
       "name": "a"
     },
     {
       "id": "1235",
       "key": "BBB",
       "name": "b"
     },
     {
       "id": "1236",
       "key": "CCC",
       "name": "c"
     }
    ]
  }
]

please help me.

marc_s · Accepted Answer · 2021-07-14 13:22:44Z

1

This can be achieved with a simple $lookup, like so:

db.chat.aggregate([
  {
    $match: {
      id: "test1"
    }
  },
  {
    $lookup: {
      from: "tag",
      localField: "tags",
      foreignField: "key",
      as: "tagDocs"
    }
  },
  {
    $project: {
      chat: [
        {
          id: "$id",
          setTags: "$tags"
        }
      ],
      tag: "$tagDocs"
    }
  }
])

Mongo Playground

I didn't fully understand what the output structure you want is but it can easily be changed via a different $project stage.

--- EDIT ---

With Mongo's v3.6 $lookup syntax the pipeline remains the same, just the $lookup stage changes:

{
    $lookup: {
      from: "tag",
      let: {
        tagKeys: "$tags"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [
                "$key",
                "$$tagKeys"
              ]
            }
          }
        }
      ],
      as: "tagDocs"
    }
  },

Mongo Playground

edited Jul 14, 2021 at 13:22

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jul 9, 2021 at 7:21

Tom Slabbaert

22.5k10 gold badges35 silver badges45 bronze badges

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

4 Comments

GYEONGSEOP KIM Over a year ago

Is it possible with from, let, pipeline when using lookup?

Tom Slabbaert Over a year ago

Yep, whatever you can do with the original syntax you can do the other way as well mongoplayground.net/p/v0hi7qLRErD

GYEONGSEOP KIM Over a year ago

Thank you very much. If the tag value in the chat collection is empty, the chat collection is not included in the result, can I include it? example like cond?

Tom Slabbaert Over a year ago

you mean show an empty array if no matches were found? if so then yes, just add in the projection stage tag: {$ifNull: ["$tagDocs", []]}

Collectives™ on Stack Overflow

lookup compare collection data with array in aggregate result in mongo DB

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related