I think currently Drill does not support the cast from an array / map into String using "cast" function. The only available is to use convert_to( array/map, 'JSON').
select cast(strCategories as varchar(250)) from (select convert_to(b.categories, 'JSON') as strCategories from dfs.`/Users/jni/work/data/json/yelp.json` b ) where strCategories LIKE '%Restaurants%'; +------------+ | EXPR$0 | +------------+ | [ "Restaurants" ] | | [ "American (Traditional)", "Restaurants" ] | +------------+ 2 rows selected (0.243 seconds) For your re-written query, the error message is not clear why it fails. We should modify Drill so that it issue a more meaningful error msg ( Something like " Can not cast from repeated type to string"). On Sun, Sep 28, 2014 at 6:58 PM, Neeraja (JIRA) <[email protected]> wrote: > Neeraja created DRILL-1462: > ------------------------------ > > Summary: Error in performing operations on array casted as > string > Key: DRILL-1462 > URL: https://issues.apache.org/jira/browse/DRILL-1462 > Project: Apache Drill > Issue Type: Bug > Affects Versions: 0.5.0 > Environment: The following query casts an array as string to find > out the potential occurrence of Restaurant string in it. It is throwing an > exception. > > 0: jdbc:drill:zk=local> select cast (b.categories as varchar(250)) as > strCategories from > dfs.`/users/nrentachintala/Downloads/yelp_dataset_challenge_academic_dataset/yelp_academic_dataset_business.json` > b where strCategories LIKE 'Restaurants'; > Error: exception while executing query: DrillBuf(ridx: 0, widx: 0, cap: > 0/0, unwrapped: DrillBuf(ridx: 0, widx: 0, cap: 0/0, unwrapped: > EmptyByteBufLE)).slice(0, 4) (state=,code=0) > > If I rewrite the query as below, it fails as well. The specific failure > cause is unknown, but logging a bug as tracking. > > 0: jdbc:drill:zk=local> select strCategories from (select cast > (b.categories as varchar(250)) as strCategories from > dfs.`/users/nrentachintala/Downloads/yelp_dataset_challenge_academic_dataset/yelp_academic_dataset_business.json` > b ) where strCategories LIKE '%Restaurants%'; > Query failed: Screen received stop request sent. Line 42, Column 21: > "value" is neither a method, a field, nor a member class of > "org.apache.drill.exec.expr.holders.RepeatedVarCharHolder" > [803b2c89-2689-4270-a4b7-b2e4a9ab62ca] > > Sample JSON: > {"business_id": "vcNAWiLM4dR7D2nwwJ7nCA", "full_address": "4840 E Indian > School Rd\nSte 101\nPhoenix, AZ 85018", "hours": {"Tuesday": {"close": > "17:00", "open": "08:00"}, "Friday": {"close": "17:00", "open": "08:00"}, > "Monday": {"close": "17:00", "open": "08:00"}, "Wednesday": {"close": > "17:00", "open": "08:00"}, "Thursday": {"close": "17:00", "open": > "08:00"}}, "open": true, "categories": ["Doctors", "Health & Medical"], > "city": "Phoenix", "review_count": 7, "name": "Eric Goldberg, MD", > "neighborhoods": [], "longitude": -111.98375799999999, "state": "AZ", > "stars": 3.5, "latitude": 33.499313000000001, "attributes": {"By > Appointment Only": true}, "type": "business"} > {"business_id": "JwUE5GmEO-sH1FuwJgKBlQ", "full_address": "6162 US Highway > 51\nDe Forest, WI 53532", "hours": {}, "open": true, "categories": > ["Restaurants"], "city": "De Forest", "review_count": 26, "name": "Pine > Cone Restaurant", "neighborhoods": [], "longitude": -89.335843999999994, > "state": "WI", "stars": 4.0, "latitude": 43.238892999999997, "attributes": > {"Take-out": true, "Good For": {"dessert": false, "latenight": false, > "lunch": true, "dinner": false, "breakfast": false, "brunch": false}, > "Caters": false, "Noise Level": "average", "Takes Reservations": false, > "Delivery": false, "Ambience": {"romantic": false, "intimate": false, > "touristy": false, "hipster": false, "divey": false, "classy": false, > "trendy": false, "upscale": false, "casual": false}, "Parking": {"garage": > false, "street": false, "validated": false, "lot": true, "valet": false}, > "Has TV": true, "Outdoor Seating": false, "Attire": "casual", "Alcohol": > "none", "Waiter Service": true, "Accepts Credit Cards": true, "Good for > Kids": true, "Good For Groups": true, "Price Range": 1}, "type": "business"} > {"business_id": "uGykseHzyS5xAMWoN6YUqA", "full_address": "505 W North > St\nDe Forest, WI 53532", "hours": {"Monday": {"close": "22:00", "open": > "06:00"}, "Tuesday": {"close": "22:00", "open": "06:00"}, "Friday": > {"close": "22:00", "open": "06:00"}, "Wednesday": {"close": "22:00", > "open": "06:00"}, "Thursday": {"close": "22:00", "open": "06:00"}, > "Sunday": {"close": "21:00", "open": "06:00"}, "Saturday": {"close": > "22:00", "open": "06:00"}}, "open": true, "categories": ["American > (Traditional)", "Restaurants"], "city": "De Forest", "review_count": 16, > "name": "Deforest Family Restaurant", "neighborhoods": [], "longitude": > -89.353437, "state": "WI", "stars": 4.0, "latitude": 43.252267000000003, > "attributes": {"Take-out": true, "Good For": {"dessert": false, > "latenight": false, "lunch": false, "dinner": false, "breakfast": false, > "brunch": true}, "Caters": false, "Noise Level": "quiet", "Takes > Reservations": false, "Delivery": false, "Parking": {"garage": false, > "street": false, "validated": false, "lot": true, "valet": false}, "Has > TV": true, "Outdoor Seating": false, "Attire": "casual", "Ambience": > {"romantic": false, "intimate": false, "touristy": false, "hipster": false, > "divey": false, "classy": false, "trendy": false, "upscale": false, > "casual": true}, "Waiter Service": true, "Accepts Credit Cards": true, > "Good for Kids": true, "Good For Groups": true, "Price Range": 1}, "type": > "business"} > Reporter: Neeraja > > > > > > > -- > This message was sent by Atlassian JIRA > (v6.3.4#6332) >
