How to do a query on documents that match one document's property

Working with Data
,
1

Hi,
I have a collection called reports, which has (among other fields) the following:
_id (of course), incident_date, incident_details, address_string, city, province, country, lat, and lon. I’m trying to find all documents in the collection which match the address_string (and eventually I will match on co-ordinates but that will require some refactoring), so I can find documents that are for the same address as my original. Ideally I would like to exclude the one I’m matching against.
So if my report collection had, say 4 different reports for 123 Fake St. Springfield, OH, all with different dates and details, I would supply the ID for one and get the other 3.

in SQL it would be like this (simplifying to search on just 123 Fake St. for now):
select *A. from A.REPORTS WHERE _ID in (select _ID from REPORTS B where B.ADDRESS_STRING=A.ADDRESS_STRING)

I have a feeling this is probably aggregation pipeline, but not sure how to formulate it.
Currently the only concrete way I can think of doing it is to do something like
db.reports.find({_id: ObjectId(‘myUID’)})
and take the results of that in an object and query again using the address_string
from the first query as a parameter in the second db.reports.find().
That approach feels clumsy and not well suited to nodeJS which is inherently asynchronous.

Thanks in advance
-Hugh
PS. I’m probably going to eventually refine my schema so that I can do geographical searches (i.e. give me all reports within 20 ft of this address).

2

In the absence of sample source documents, I create some very sample documents where the field a is the address field.

mongosh > use test
mongosh > c = db.reports
mongosh > c.find()
{ _id: 1, a: 1 }
{ _id: 2, a: 1 }
{ _id: 3, a: 2 }
// So we have 3 reports and 2 have the same address
// You will need an aggregation with 4 stages, match, lookup, unwind and replaceRoot defined as
// The _id supplied for which we want all reports at the same address
mongosh > match = { $match : { _id:1 } }
// The lookup is to find all reports at the same address as the report with _id:1
mongosh > lookup = { $lookup : { from : "reports" , "localField" : "a" , "foreignField" : "a" , "as" : "all" } }
// We unwind so that we get a list of report rather than 1 document with an array of reports inside
mongosh > unwind = { $unwind : { "path" : "$all" } }
// We remove the details of _id:1 that are repeated because of unwind
mongosh > replace = { "$replaceRoot" : { "newRoot" : "$all" } }
mongosh > pipeline = [ match , lookup , unwind , replace ]
mongosh > c.aggregate( pipeline )
< { _id: 1, a: 1 }
  { _id: 2, a: 1 }
3

looks good to me, well done