I believe the current Drill MongoDB plugin is trying to work with MongoDB's extended JSON [1] at the moment. As such, I think you'd need to write the filter as myfield.`$oid` = 'id value'
http://docs.mongodb.org/manual/reference/mongodb-extended-json/ Note that currently Drill uses SQL expressions with dotted notation extensions for filters and doesn't currently support the mongodb based json object filters. On Tue, Jan 6, 2015 at 3:52 PM, Adam Gilmore <[email protected]> wrote: > Hi Kamesh, > > Unfortunately it's not on _id - it's on another objectId field we have in > our documents. That seems to work fine with _id but with anything else, it > returns no results. > > Any thoughts? > > > Regards, > > > *Adam Gilmore* > > Director of Technology > > [email protected] > > > +61 421 997 655 (Mobile) > > 1300 733 876 (AU) > > +617 3171 9902 (Intl) > > > *PharmaData* > > Data Intelligence Solutions for Pharmacy > > www.PharmaData.net.au <http://www.pharmadata.net.au/> > > > > [image: pharmadata-sig] > > > > *Disclaimer* > > This communication including any attachments may contain information that > is either confidential or otherwise protected from disclosure and is > intended solely for the use of the intended recipient. If you are not the > intended recipient please immediately notify the sender by e-mail and > delete the original transmission and its contents. Any unauthorised use, > dissemination, forwarding, printing, or copying of this communication > including any file attachments is prohibited. The recipient should check > this email and any attachments for viruses and other defects. The Company > disclaims any liability for loss or damage arising in any way from this > communication including any file attachments. > > On Wed, Jan 7, 2015 at 2:08 AM, Kamesh <[email protected]> wrote: > >> Hi Adam, >> Are you trying apply filter using _id?. If so, Mongo storage plugin >> supports it. >> >> 0: jdbc:drill:zk=localhost:2181> select * from mongo.usda.`zips` where _id >> = '01077'; >> +------------+ >> | * | >> +------------+ >> | { "city" : "SOUTHWICK" , "loc" : [ -72.770588 , 42.051099] , "pop" : >> 7667 >> , "state" : "MA"} | >> +------------+ >> 1 row selected (4.792 seconds) >> >> >> On Mon, Jan 5, 2015 at 1:45 PM, Adam Gilmore <[email protected]> >> wrote: >> >> > Hi all, >> > >> > I'm trying to work out how to filter by an objectId field using the >> Mongo >> > plugin. I've tried many combinations of = '{$oid: ''id''}' etc. but >> > nothing seems to work. >> > >> > Is this implemented yet? If not, is there a JIRA item for it already? >> > >> >> >> >> -- >> Kamesh. >> > >
