If I do a find query on an integer, I don't get the right results

[Ralph_van_der_Sanden]

Hi,

I’m running MongoDB 4.x with the extension for PHP8.
I have a document structure like this:

_id
account_id (int)
survey_id (int)
answers (array)
  [0] (object)
  answer
  type
  question
  [1] ....

A few data snippet samples of the answers object

[doc 1] answer = 1
     type  = 12

[doc 2] answer = 2
     type  = 12

[doc 3] answer = 5
     type  = 12

[doc 4] answer = 1
     type  = 12

[doc 5] answer = 2
     type  = 12

My find filter array is:

Array
(
    [account_id] => 1005
    [survey_id] => 205244
    [answers.type] => 12
    [answers.answer] => 2
)

I expect to only get results where account_id = 1005 and survey_id=205244 and answers.type=12 and answers.answer=2

However, this returns results with 2 but also the results with 1 as answer.
I’m at it for a few hours now and I just don’t have any explanation why this happens.

[Rafael_Green]

your filter specifies all documents where account_id is 1005, survey_id is 205244 and there is at least one answer with type property equals to 12 and at least one answer with the answer property equals to 2

[steevej (Steeve Juneau)]

Good catch @Rafael_Green.

@Ralph_van_der_Sanden, you will need to look at https://docs.mongodb.com/manual/reference/operator/query/elemMatch/.

[Ralph_van_der_Sanden]

Hi @Rafael_Green and @steevej , thank you for your quick reply. If I understand you correctly steevej, I first do the query and then filter it out with elemMatch? If yes, is there another way that doesn’t filter after getting a lot of results but already filters it when doing the query?
I found this on StackOverflow, mongodb - elemMatch query does not work in php - Stack Overflow, see the answer, is that what you are talking about?
Thanks!

[Ralph_van_der_Sanden]

Just tried it but no luck, it doesn’t return any results anymore.
I tried:

Array
(
[account_id] => 1005
[survey_id] => 205244
[$elemMatch] => Array
    (
        [answers.type] => 12
        [answers.answer] => 2
    )
)

and also

Array
(
[account_id] => 1005
[survey_id] => 205244
[answers] => Array
    (
        [$elemMatch] => Array
            (
                [answers.type] => 12
                [answers.answer] => 2
            )

    )
)

Any suggestions?

[Ralph_van_der_Sanden]

I found the solution, the last query was the right one, but i had to remove the answers. so only type and answer is left. Thank you so much for pointing me the right way!