CODEWITHSUNDEEP
javascriptdatabasemongodbmongodb-querynosql
Alk
Alk

Reputation: 5557

Mongo Query - match and lookup combined

I’ve defined the following query which fetches me all items with an id which is in a given list of ids, and a status of either active or retracted.

const query = { 
   $and : [ 
     { 
       $or: [
         { 
           status: ‘active’,
         }, 
         { 
           status: ‘retracted’,
         },
       ], 
     }, 
     {
        id: { $in: ids }, 
     }, 
  ],
};

Each of these items has a parent_id field, which can either be null if the item does not have a parent, or can be the id of the parent.

I want my query to fetch all items with the ids I supply, as well as their parent items, if such a parent exists.

For example, if I supply the following IDs

[1,2,3]

and item 2 has a parent with id 5, while item 1 and 3 have parent_id set to null, I want my query to return the following items:

[1,2,3,5].

To achieve this I wrote the following query:

const collection = db.collection(‘myCollection’);

const data = await collection.aggregate([ 
      {$match : query}, 
      {
        $lookup: { 
          from: ‘myCollection’, 
          let: { parentID: ‘$parent_id’}, 
          pipeline: [ 
             { 
                $match: { 
                  $expr: { 
                    $eq: [‘$id’, ‘$$parentID’], 
                  }, 
                }, 
             }, 
          as: ‘parent’, 
         }, 
      }, 
     ]).sort(‘created_date’, ‘desc’).toArray();

return data;

However, this always returns null.

Sample Data:

[
{
id: 1, 
parent_id: 3, 
data: ‘bla bla’
}, 
{
id: 2, 
parent_id: null, 
data: ‘bla bla bla’
},
{
id: 3, 
parent_id: null, 
data: ‘bla’
}
]

Input: [1]

Output:

[
{
id: 1, 
parent_id: 3, 
data: ‘bla bla’
}, 
{
id: 3, 
parent_id: null, 
data: ‘bla’
}
]

Upvotes: 1

Views: 73

Answers (3)

Rajdeep D
Rajdeep D

Reputation: 3920

You can try this. The input array is [2,3] where 2 has parent id=1 and that is not in the input array. But the output array has the entry.

Working Playground

db.collection.aggregate([
  {
    $match: {
      _id: {
        $in: [
          2,
          3
        ]
      }
    }
  },
  {
    $lookup: {
      from: "collection",
      localField: "p",
      foreignField: "_id",
      as: "parent"
    }
  },
  {
    $project: {
      _id: 0,
      id: {
        $concatArrays: [
          [
            "$_id"
          ],
          "$parent._id"
        ]
      }
    }
  },
  {
    $unwind: "$id"
  },
  {
    $sort: {
      id: 1
    }
  }
])

Upvotes: 1

mickl
mickl

Reputation: 49975

The approach with $lookup being run upon same collection should work however it gives you a nested array so you need few additional stages to flatten such array and get all elements as on result set:

db.collection.aggregate([
    {
        $match: { id: { $in: [1] } }
    },
    {
        $lookup: {
            from: "collection",
            localField: "parent_id",
            foreignField: "id",
            as: "parent"
        }
    },
    {
        $project: {
            all: {
                $concatArrays: [
                    "$parent",
                    [ "$$ROOT" ]
                ]
            }
        }
    },
    {
        $project: {
            "all.parent": 0
        }
    },
    {
        $unwind: "$all"
    },
    {
        $replaceRoot: {
            newRoot: "$all"
        }
    }
])

Mongo Playground

Upvotes: 1

Rubén Vega
Rubén Vega

Reputation: 730

Your aggregation was malformed and lack some "]" for example closing the pipeline fied.

If you fix that the query works fine for me. Example

Upvotes: 1

Related Questions