I gave the slightly wrong query. I forgot that you need to prefix table name in case of complex paths (which you had done). Try this:
select * from mongo.`connect`.events e where e.`*`.customerId.`$oid` = '54901607f10c2236769f7b3b' limit 1; as well as: select customerId from mongo.`connect`.events e where e.customerId.`$oid` = '54901607f10c2236769f7b3b' limit 1; On Thu, Jan 8, 2015 at 5:26 PM, Adam Gilmore <[email protected]> wrote: > 0: jdbc:drill:> select * from mongo.`connect`.events where > `*`.events.customerId.`$oid` = '54901607f10c2236769f7b3b' limit 1; > Query failed: Query failed: Failure validating SQL. > org.eigenbase.util.EigenbaseContextException: From line 1, column 44 to > line 1, column 46: Table '*' not found > > Error: exception while executing query: Failure while executing query. > (state=,code=0) > 0: jdbc:drill:> select events from mongo.`connect`.events where > events.customerId.`$oid` = '54901607f10c2236769f7b3b' limit 1; > +------------+ > | events | > +------------+ > | null | > +------------+ > 1 row selected (0.261 seconds) > > Strange results there - I played around with the second query and it seems > to be able to return anything but * nicely. So you're probably right. > > > 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 Fri, Jan 9, 2015 at 2:01 AM, Jacques Nadeau <[email protected]> wrote: > >> I think I know what the problem is. Hanifi recently identified that the >> Mongo storage plugin doesn't handle * correctly. Instead of returning a >> set of columns for the top level of the mongo record, it returns a single >> top-level column called star. DRILL-1692 identifies the problem and it >> causes a number of issues when using mongo. Can you try each of the >> following to see if they work? >> >> select * from mongo.`connect`.events where `*`.events.customerId.`$oid` = >> '54901607f10c2236769f7b3b' limit 1; >> >> OR >> >> select events from mongo.`connect`.events where events.customerId.`$oid` = >> '54901607f10c2236769f7b3b' limit 1; >> >> Thanks, >> Jacques >> >> On Wed, Jan 7, 2015 at 12:44 AM, Adam Gilmore <[email protected]> >> wrote: >> >> > Unfortunately, that didn't work. I tried: >> > >> > select * from mongo.`connect`.events where events.customerId.`$oid` = >> > '54901607f10c2236769f7b3b' limit 1; >> > >> > returns no results - but there are definitely rows with that value in >> it. >> > >> > On Wed, Jan 7, 2015 at 2:18 PM, Jacques Nadeau <[email protected]> >> wrote: >> > >> > > 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. >> > > >> >> > > > >> > > > >> > > >> > >> > >
