Array to array join on specific index mongodb

Working with Data
, ,
1

I want to get to know about joining to collections array to array based on index of array. Is it possible? If yes, how? What are the semantics, how do we specify array index in join/lookup?

Also,

It would be much appreciated if it could be explained too i.e., how to specify array index while array to string join?

2

Hi @MWD_Wajih_N_A ,

Is the array index the only classification for the lookup or there is additional fields/ids in the join?

What should happen if there is no match?

Can you provide a simple 2 document examples and ho the result should be based on them, this will help me assist you.

Thanks
Pavel

3

col A
{id:1,
Array:[1,2,2,4,5]}
col B
{id,3
Array:[5,3,2,7,9]}

Can I join A to B if A.Array.index(2)==B.Array.index(2)

4

Hi @MWD_Wajih_N_A ,

Ok this is a pretty unnatural requirement for a join and you should consider a redesign of the schema to better support a more efficient join across documents like keeping some embedded indexes to join the collection.

However, it is possible to build a pretty complex aggregation which is probably not very efficient but it can join documents of this type by this condition:

// Save the root object for further grouping
[{$addFields: {
  root: "$$ROOT"
}}, 
// Unwind the arrays and add indexes
{$unwind: {
  path: '$Array',
  includeArrayIndex: 'idx'
}}, 
// Lookup using an internal pipeline to equal idx and parent Array value
{$lookup: {
  from: 'collB',
 let : { "parent" : "$Array" , idx : "$idx"},
 pipeline : [{$addFields: {
  root: "$$ROOT"
}},{$unwind  : {
  path: '$Array',
  includeArrayIndex: 'idx'
}},{$match : {$expr: { $and : [ { $eq : [ "$idx", "$$idx" ] }, {$eq : [ "$Array", "$$parent" ]}]}}}],
as : "collB"
}}, 
// Filter garbage unmatched elements 
{$match: {
  $expr : {$gt : [ {$size : "$collB"}, 0]}

  }}, 
// Rebuild the objects to have the matched documents as a lookedup document
{$replaceRoot: {
  newRoot: { $mergeObjects : ["$root", {"collB" : "$collB.root" }] }
}}]

Result:

db.collA.find()
{ _id: ObjectId("61025cc6b818e0b49461d57d"),
  id: 1,
  Array: [ 1, 2, 2, 4, 5 ] }
db.collB.find()
{ _id: ObjectId("61025cf4b818e0b49461d57e"),
  id: 3,
  Array: [ 5, 3, 2, 7, 9 ] }
db.collA.aggregate([{
    $addFields: {
        root: "$$ROOT"
    }
}, {
    $unwind: {
        path: '$Array',
        includeArrayIndex: 'idx'
    }
}, {
    $lookup: {
        from: 'collB',
        let: {
            "parent": "$Array",
            idx: "$idx"
        },
        pipeline: [{
            $addFields: {
                root: "$$ROOT"
            }
        }, {
            $unwind: {
                path: '$Array',
                includeArrayIndex: 'idx'
            }
        }, {
            $match: {
                $expr: {
                    $and: [{
                        $eq: ["$idx", "$$idx"]
                    }, {
                        $eq: ["$Array", "$$parent"]
                    }]
                }
            }
        }],
        as: "collB"
    }
}, {
    $match: {
        $expr: {
            $gt: [{
                $size: "$collB"
            }, 0]
        }

    }
}, {
    $replaceRoot: {
        newRoot: {
            $mergeObjects: ["$root", {
                "collB": "$collB.root"
            }]
        }
    }
}])
{ _id: ObjectId("61025cc6b818e0b49461d57d"),
  id: 1,
  Array: [ 1, 2, 2, 4, 5 ],
  collB: 
   [ { _id: ObjectId("61025cf4b818e0b49461d57e"),
       id: 3,
       Array: [ 5, 3, 2, 7, 9 ] } ] }

Hope this helps.

Thanks
Pavel

1 Like
5

Man this is amazing. Yeah I understand this is pretty unnatural and will surely look into schema but glad to learn its possible. Thank you so much.

6

It will work better if you add a first stage to $match specific documents that you need to search for and index those fields to optimize the search before the join.

1 Like
7

sure. understood. Thanks :slight_smile:

1 Like
8

Can you look here if possible!

9

$lookup does outer left join.
I am applying lookup from array to array which is giving me weird results as joining even the non same arrays.
Is array to array join not possible in mongo db?
I am talking about basic array to array join.

col A

{_id:1,
name:xyz,
phoneNumbers:[0123,3654,6567]}

col B

{_id:30,
Designation:abc,
phoneNumbers:[0123,1,1]}

Query to try.

db.A.aggregate(
[ {$lookup: {
from: ‘B’,
localField: ‘phoneNumbers’,
foreignField: ‘phoneNumbers’,
as: ‘joined’
}}])

It joins other documents too even though the phoneNumbers does not match there

10

How is

different from

for which a solution from @Pavel_Duchovny has already been posted?

11

Hi @steevej and @MWD_Wajih_N_A ,

Just using local and foreign field to join arrays won’t work .

My pipeline is much more complex

Pavel

1 Like