MongoDB $lookup with $match and $eq, matching value inside array of object

Question

Here are the document bson example. Mongodb Playground Link

db={
  "chats": [
    {
      "_id": ObjectId("6000cb3a7ca86a96c13bada8"),
      "clients": [
        {
          "email": "2222@mail.id",
          "_id": ObjectId("6000cb3a7ca86a96c13bada9"),
          "id": 2222,
          "as": "owner",
          "username": "2222",
          "phonenumber": "1111"
        },
        {
          "email": "1111@mail.id",
          "_id": ObjectId("6000cb3a7ca86a96c13badaa"),
          "id": 1111,
          "as": "user",
          "username": "1111",
          "phonenumber": "1111"
        }
      ],
      "roomId": "6",
      "createdAt": ISODate("2021-01-14T22:52:42.357Z"),
      "updatedAt": ISODate("2021-01-14T22:52:42.357Z"),
      "__v": 0
    }
  ],
  "messages": [
    {
      "_id": ObjectId("6000cb3a7ca86a96c13badab"),
      "status": "SEND",
      "roomId": ObjectId("6000cb3a7ca86a96c13bada8"),
      "text": "Hellw as a user here",
      "senderId": 2222,
      "receiverId": 1111,
      "createdAt": ISODate("2021-01-14T22:52:42.365Z"),
      "updatedAt": ISODate("2021-01-14T22:52:42.365Z"),
      "__v": 0,
      
    },
    {
      "_id": ObjectId("6000cb457ca86a96c13badac"),
      "status": "SEND",
      "roomId": ObjectId("6000cb3a7ca86a96c13bada8"),
      "text": "Hellow I'm onwer too guyy",
      "senderId": 1111,
      "receiverId": 2222,
      "createdAt": ISODate("2021-01-14T22:52:53.596Z"),
      "updatedAt": ISODate("2021-01-14T22:52:53.596Z"),
      "__v": 0
    }
  ]
}

I want to search on array of object joined result in MongoDB using $match, $expr and $eq to search message that has clients.id = 1111 and clients.as = "user" So far I use this query (also present on playground)

db.messages.aggregate([
  {
    $lookup: {
      from: "chats",
      /** $roomId are field from messages*/
      let: {
        roomId: "$roomId"
      },
      /** $_id are field from chats*/
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                {
                  $eq: [
                    "$_id",
                    "$$roomId"
                  ]
                },
                {
                  $eq: [
                    "$clients.id",
                    1111
                  ]
                },
                {
                  $eq: [
                    "$clients.as",
                    "user"
                  ]
                },
                
              ],
              
            },
            
          },
          
        },
        
      ],
      as: "chat_room",
      
    },
    
  },
  
])

Is not succeed for now and produce chat_room: [], using { $eq: ["$_id", "$$roomId"]} I successfully join the 2 schema, but when I use $eq it to search whether chat_room.clients.id is 1111 and chat_room.clients.as is user it only produce blank chat_room: []

How to find chat_room.clients.id is 1111 and chat_room.clients.as is user ?
Could it the query return empty if I search for chat_room.clients.id is 1111 and chat_room.clients.as is owner since there is no chats document that has clients.id = 1111 as an "owner"

user_8987128 · Answer

You have to correct your match conditions,

user $elemMatch for array field conditions

db.messages.aggregate([
  {
    $lookup: {
      from: "chats",
      let: { roomId: "$roomId" },
      pipeline: [
        {
          $match: {
            $expr: { $eq: ["$_id", "$$roomId"] },
            clients: {
              $elemMatch: {
                id: 1111,
                as: "user"
              }
            }
          }
        },

if you want to select matching element from clients try $filter and put your conditions

        {
          $addFields: {
            clients: {
              $filter: {
                input: "$clients",
                cond: {
                  $and: [
                    { $eq: ["$$this.id", 1111] },
                    { $eq: ["$$this.as", "user"] }
                  ]
                }
              }
            }
          }
        }
      ],
      as: "chat_room"
    }
  }
])

Playground

MongoDB $lookup with $match and $eq, matching value inside array of object

1 Answers