Is there any way to combine $elemMatch and $expr or how to match internal object is in array or not?

Working with Data
,
1

Collection One:

[
  { _id: 1,obj: { a: 123, b: "ABC" } }
]

Collection Two:

[
  { _id: 1, arr: [{ a: 123, b: "ABC" }, { a: 234, b: "BCD" }] },
  { _id: 2, arr: [{ a: 123, b: "BCD" }, { a: 234, b: "ABC" }] }
]

Requirements: Select matching documents from collection two where obj of collection one is in arr of collection two, and should match first (_id: 1) document,

1) Try (Working):

I tried using $in condition in $expr in $match stage, and this is working perfectly,

db.one.aggregate([
  {
    $lookup: {
      from: "two",
      let: { obj: "$obj" },
      pipeline: [
        { $match: { $expr: { $in: ["$$obj", "$arr"] } } }
      ],
      as: "matchFromTwo"
    }
  }
])

Playground

2) Try (Not Working):

This will fail when order of fields in object are different, like i have swap both fields (a,b) position in collection one of obj:
Collection One:

[
  { _id: 1,obj: { b: "ABC", a: 123 } }
]

Collection Two:

[
  { _id: 1, arr: [{ a: 123, b: "ABC" }, { a: 234, b: "BCD" }] },
  { _id: 2, arr: [{ a: 123, b: "BCD" }, { a: 234, b: "ABC" }] }
]

Playground

This is not working because of fields position are different in obj and arr, this will also not work when other side arr objects position are different.

3) Try (Not Working):

I have tried other way also using separate condition of each field in $and,

db.one.aggregate([
  {
    $lookup: {
      from: "two",
      let: { obj: "$obj" },
      pipeline: [
        {
          $match: {
            $expr: {
              $and: [
                { $in: ["$$obj.a", "$arr.a"] },
                { $in: ["$$obj.b", "$arr.b"] }
              ]
            }
          }
        }
      ],
      as: "matchFromTwo"
    }
  }
])

Playground

This will not match and condition of array in particular element like $elemMatch, this returns both elements,

Is there any other way to deal with this kind of conditions?

2

Hi @turivishal,

Would the following aggregation work for you:

db.one.aggregate([
  {
    $match: {
      "_id": ObjectId("5a934e000102030405000000")
    }
  },
  {
    $lookup: {
      from: "two",
      pipeline: [
        {
          $match: {
            arr: {
              $elemMatch: {
                a: 123,
                b: "BCD",
                
              },
              
            },
            
          },
          
        }
      ],
      as: "matchFromTwo"
    }
  }
])

I tried it on your playground looks sufficient.

Please let me know if you have any additional questions.

Best regards,
Pavel

3

Thanks for your reply,

Actually i want to match condition dynamically, that is why i have mentioned how to match internal object is in array or not, there are many documents in collection one and i want to join with collection two on the base of obj field.

4

@turivishal,

Not sure I follow based on the provided examples . But if my imagination understand correctly , would that be what you are looking for:

db.one.aggregate([{$lookup: {
  from: 'two',
  'let': {
    x: '$obj.a',
    y: '$obj.b'
  },
  pipeline: [
    {
      $match: { $expr : {
          $in: [{
            a: '$$x',
            b: '$$y'
          },"$arr"]
        }
      }
    }
  ],
  as: 'matchFromTwo'
}}])

It seems to yield one match:

[ { _id: ObjectId("601feb0f7d5dd662646de63f"),
    obj: { b: 'ABC', a: 123 },
    matchFromTwo: 
     [ { _id: ObjectId("601feb267d5dd662646de640"),
         arr: [ { a: 123, b: 'ABC' }, { a: 234, b: 'BCD' } ] } ] } ]

Best regards,
Pavel

5

This is a way we can secure from object side, but what if arr contains different position, like taking your example:

Collection One:

[
  { _id: 1,obj: { b: "ABC", a: 123 } }
]

Collection Two:

[
  { _id: 1, arr: [{ b: 123, a: "ABC" }, { a: 234, b: "BCD" }] },
  { _id: 2, arr: [{ a: 123, b: "BCD" }, { a: 234, b: "ABC" }] }
]

Query:

db.one.aggregate([{$lookup: {
  from: 'two',
  'let': {
    x: '$obj.a',
    y: '$obj.b'
  },
  pipeline: [
    {
      $match: { $expr : {
          $in: [{
            a: '$$x',
            b: '$$y'
          },"$arr"] // actual array value is [{ b: 123, a: "ABC" }, { a: 234, b: "BCD" }]
        }
      }
    }
  ],
  as: 'matchFromTwo'
}}])

Playground

This will not work because position of fields in arr are different. we are searching for {a,b} but what if its in different position in arr [{b,a}], so $in will fail.

6

You can add a $or of both combinations right?

1 Like
7

Yes thanks that would help, I was never thought of $or condition,

$expr: {
  $or: [
    { $in: [{ a: "$$x", b: "$$y" }, "$arr"] },
    { $in: [{ b: "$$y", a: "$$x" }, "$arr"] }
  ]
}

Thanks again.

1 Like
8

I would also take a look at how many documents have the a different order. May be it is worth to reorder if only a few exceptions. Why is there exceptions? In principles, you have an API that creates and updates your documents. I would wish they all do the manipulation in a consistent order. Or would work, but it might be more efficient to have consistency and use a single $in.

1 Like
9

Thanks @steevej,

We have corrected API but there is no consistency in old documents, we have to just make sure response should 100% accurate, we are planning to reorder old documents and user $in condition only,

I want to confirm that if i push object in array from mongoose, node.js,

Two.updateOne({ _id: id }, { $push: { arr: { a: 123, b: "ABC" } } });

Does MongoDB guaranty it will insert in same order?
or I have to ask this same question to mongoose support?

1 Like
10

@turivishal,

I beilive the order should be preserved on the Server. I am not certain about your debugging or visual tools which mightnot gurantee the order of fields.

From server perspective I believe there is a difference between { a : ..., b : ....} and { b: ... , a : ...} as those are considered “different” documents.

Best regards,
Pavel

2 Likes
11

FYI @steevej and @turivishal:

1 Like
12

@Pavel_Duchovny Thank you for the confirmation.

13

Hi @turivishal,

As @Pavel_Duchovny noted, the MongoDB server will preserve the order of fields. However, a caveat to add is “as provided by the driver” because some data structures (like JavaScript Objects) are not guaranteed to be order-preserving in all cases.

ES6+ generally preserves the order of fields aside from the special case of object keys that can be represented as ints, which will be sorted in ascending order before any string keys.

Example in the node shell:

> { b: 2, a: 1 }
{ b: 2, a: 1 }

> { b: 2, a: 1, 123: "oops" }
{ '123': 'oops', b: 2, a: 1 }

> { b: 2, a: 1, "123": "oops" }
{ '123': 'oops', b: 2, a: 1 }

For more background, see discussion on Does JavaScript guarantee object property order?.

Regards,
Stennie

2 Likes
14

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.