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]> > 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
