Awesome, thanks! This solves my exact match (and wildcard matches) requirements.
I also have a requirement to apply standard conditions like <,<=,>,>=, etc. on numbers in an array For eg. I need something like *repeated_contains(array, 5, '>')* and this should return true if the array has any element greater than 5 and false otherwise. So should I be creating custom functions, for this? If yes, are the custom functions somehow perform slower than the ones provided by drill out of the box? Thanks, Kathir On Friday, August 19, 2016, Andries Engelbrecht <[email protected]> wrote: > Use REPEATED_CONTAINS. > http://drill.apache.org/docs/repeated-contains/ < > http://drill.apache.org/docs/repeated-contains/> > > > --Andries > > > > > On Aug 19, 2016, at 3:15 PM, Kathiresan S <[email protected] > <javascript:;>> wrote: > > > > Hi, > > > > We have a json where one of the columns is of type array (of Strings) and > > we would like to run a query to get the rows where any of the values in > the > > array matches the given value. > > > > *(Eg): cities.json* > > {"id":"1","city":["nyc","la"]} > > {"id":"2","city":["la"]} > > {"id":"3","city":["nyc"]} > > {"id":"4","city":["la","nyc"]} > > > > *Query we would like to run (something like below)* > > > > > > *select id from cp.`cities.json` where any[city] = 'la'* > > > > Or > > > > *select id from cp.`cities.json` where city contains 'la'* > > > > which is expected to return rows 1,2 & 4 > > > > Is there any way we can get this done without using FLATTEN? > > > > Drill version that we use : 1.6.0 > > > > Thanks, > > Kathir > >
