-1

I am still learning mongo queries and need help in fixing below aggregate query.

Requirement: A and B collections are related and has a common property "phone number" and "name". Trying to pull the records from collection A where phone number exists in A but not exist in collection B.

SQL equivalent query if it helps:

select * from A 
where col1 is not null and phone_number NOT EXISTS(select 1 from B where A.phone_number = B.phone_number  and x='123')

I tried to write below mongo query but its throwing error at $ne

db.A.aggregate([
    { $match: { "dob": { $exists: true  } } },
    {
        $lookup: {
            from: "B",
            localField: "phone_number",
            foreignField: "phone_number",
            as: "all_profiles"
        }
    },
    {
        $match: {
            $and: [
                { "all_profiles.col3": "xxxx" },
                { $ne: [
                    "all_profiles.phone_number",
                    "$phone_number"
                ] }
            ]
        }
    },
    {
        $project: {
            first_name: 1, last_name: 1, dob: 1, phone_number: 1
        }
    }
])

Error :

Mongo Server error (MongoCommandException): Command failed with error 2 (BadValue): 'unknown operator: $ne' on server

The full response is: { "ok" : 0.0, "errmsg" : "unknown operator: $ne", "code" : 2.0, "codeName" : "BadValue" }

Thanks

sidgate
  • 14,650
  • 11
  • 68
  • 119
Manju
  • 21
  • 2
  • 9
  • Have you checked out $let? https://docs.mongodb.com/manual/reference/operator/aggregation/let/ – oblivion02 Nov 09 '21 at 04:41
  • 1
    check `$lookup` with `pipeline` query. refer https://stackoverflow.com/questions/9621928/how-do-i-query-referenced-objects-in-mongodb/39476690#39476690 – sidgate Nov 09 '21 at 04:44
  • https://idownvotedbecau.se/noattempt/ – ray Nov 09 '21 at 05:12
  • @sidgate Thanks for the help. I tried to write above query (which is in description) based on your inputs but above script is throwing below error. Can you please help. Thanks The full response is: { "ok" : 0.0, "errmsg" : "unknown operator: $ne", "code" : 2.0, "codeName" : "BadValue" } – Manju Nov 09 '21 at 22:13
  • @oblivion02 for some reason, studio 3T TRIAL version is not supporting $let so I cant use it but tried with $lookup and added my query in the description – Manju Nov 09 '21 at 22:18
  • @Manju - Take a look at this, might help you out: https://stackoverflow.com/questions/8772936/get-data-from-collection-b-not-in-collection-a-in-a-mongodb-shell-query – oblivion02 Nov 12 '21 at 00:51
  • 1
    @oblivion02 Thanks for the article. I was able to write a complex query similar to the one showed in article :) – Manju Nov 13 '21 at 14:22
  • post your answer for the community!! @Manju – oblivion02 Nov 13 '21 at 19:17
  • sure, I will post my answer here soon as I Need to mask my fields and do some prep work for security purposes. @oblivion02 How do I connect with you in facebook/twitter or any other social media for some quick help? Thanks – Manju Nov 29 '21 at 13:18
  • LinkedIn - https://www.linkedin.com/in/fabian-valle-24b22b149/ – oblivion02 Nov 30 '21 at 16:23

1 Answers1

0

we do support $let, the Trial version of the tool does not have any limitations like this. If you have more detail and a screenshot of what you're doing, you can send it to our Support page: https://studio3t.com/feedback/

Studio 3T
  • 326
  • 1
  • 7
  • I sent some screenshots for the issues I faced in using $let and other keywords to your team(Hannah). Hope this will be considered. Thanks – Manju Nov 29 '21 at 13:20