Q:

how to work with array of structs in hive

I have a json file given below, which I want to access using hive by creating an external table using jsonserde.

{"ResponseCode":"1","Response":"Data Found","data" :[{"Season":"RABI","Sector":"HORTICULTURE","Category":"Fruits","Crop":"Mango","QueryType":"Cultural Practices","QueryText":"how to control flower drop in mango ?","KCCAns":"recommended for spray to planofix 5-7 mili/pump","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"BALAYAPALLE                                       ","SubmitDate":"11/1/2016 9:05:27 AM"},{"Season":"RABI","Sector":"AGRICULTURE","Category":"Others","Crop":"Others","QueryType":"Weed Management","QueryText":"weed management in eucalyptus","KCCAns":"RECOMMENDED TO SPRAY GLYPOSATE @ 2 LITRES PER ACRE","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"MARRIPADU                                         ","SubmitDate":"11/1/2016 9:07:04 AM"},{"Season":"RABI","Sector":"HORTICULTURE","Category":"Vegetables","Crop":"Bhindi(Okra/Ladysfinger)","QueryType":"\tPlant Protection\t","QueryText":"OKRA MITE DAMAGE","KCCAns":"RECOMMENDED TO SPRAY DICOFOL 1 LITER /200 LITRES OF WATER /ACRE","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"KOVUR                                             ","SubmitDate":"11/1/2016 3:11:59 PM"},{"Season":"RABI","Sector":"AGRICULTURE","Category":"Cereals","Crop":"Paddy (Dhan)","QueryType":"Weed Management","QueryText":"paddy weed management at 15-20 days crop","KCCAns":"RECOMMENDED TO SPRAY CYHALOPOP-P- BUTYL  250 ML PER  ACRE","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"BALAYAPALLE                                       ","SubmitDate":"11/3/2016 2:11:17 PM"},{"Season":"KHARIF","Sector":"AGRICULTURE","Category":"Others","Crop":"Others","QueryType":"Weather","QueryText":"WEATHER  REPORT","KCCAns":"WEATHER  REPORT   CONTACT  18004253141 ,08912543031","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"KALIGIRI                                          ","SubmitDate":"11/3/2016 5:22:22 PM"},{"Season":"RABI","Sector":"AGRICULTURE","Category":"Pulses","Crop":"Pigeon pea (red gram/arhar/tur)","QueryType":"Nutrient Management","QueryText":"RED GRAM NUTRIENT MANAGEMENT","KCCAns":"RECOMMENDED TO SPRAY 13-045 ONE KG PER ACRE","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"ANUMASAMUDRAMPETA                                 ","SubmitDate":"11/3/2016 7:25:10 PM"},{"Season":"RABI","Sector":"AGRICULTURE","Category":"Pulses","Crop":"Pigeon pea (red gram/arhar/tur)","QueryType":"Nutrient Management","QueryText":"RED GRAM NUTRIENT MANAGEMENT","KCCAns":"RECOMMENDED TO SPRAY 13-0-45 ONE  KG/ACRE","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"ANUMASAMUDRAMPETA                                 ","SubmitDate":"11/3/2016 7:30:02 PM"},{"Season":"RABI","Sector":"HORTICULTURE","Category":"Fruits","Crop":"Citrus","QueryType":"\tPlant Protection\t","QueryText":"CITRUS SUCKING PEST","KCCAns":"RECOMMENDED FOR DIMETHOATE  400 ML / ACRE 200 LITER WATER","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"KODAVALUR                                         ","SubmitDate":"11/4/2016 8:48:03 AM"},{"Season":"RABI","Sector":"HORTICULTURE","Category":"Fruits","Crop":"Citrus","QueryType":"\tPlant Protection\t","QueryText":"CITRUS SUCKING PEST","KCCAns":"RECOMMENDED FOR DIMETHOATE  400 ML / ACRE 200 LITER WATER","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"KODAVALUR                                         ","SubmitDate":"11/4/2016 8:48:04 AM"},{"Season":"KHARIF","Sector":"AGRICULTURE","Category":"Others","Crop":"Others","QueryType":"Weather","QueryText":"weather report","KCCAns":"Considerable cloudiness with a shower or thunderstorm around this afternoon","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"VINJAMUR                                          ","SubmitDate":"11/4/2016 3:14:03 PM"},{"Season":"KHARIF","Sector":"AGRICULTURE","Category":"Others","Crop":"Others","QueryType":"Weather","QueryText":"WEATHER RTEPORT","KCCAns":"Considerable cloudiness with a shower or thunderstorm around this afternoon","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"MARRIPADU                                         ","SubmitDate":"11/4/2016 5:18:50 PM"},{"Season":"KHARIF","Sector":"AGRICULTURE","Category":"Others","Crop":"Others","QueryType":"\tPlant Protection\t","QueryText":"BENGAL GRAM BEST VARIETY","KCCAns":"BENGAL GRAM BEST VARIETY  : J.G-11, LAM SENAGA (LBEG-7),ICCV-10","StateName":"ANDHRA PRADESH","DistrictName":"NELLORE","BlockName":"PELLAKUR                                          ","SubmitDate":"11/4/2016 7:57:44 PM"}]}

I have created below table for accessing the above json data.
create EXTERNAL table kccdata1 (
Response string,
data array<struct<
Season:STRING,
Sector:STRING,
Category:STRING,
Crop:STRING,
QueryType:STRING,
QueryText:string,
KCCAns:string,
StateName:string,
DistrictName:string,
BlockName:string,
SubmitDate:string>>) 
row format serde "org.apache.hive.hcatalog.data.JsonSerDe" 
location "/user/hduser/jsondata";

This table has put each struct as a value for each record. I am able to access the elements from it by using the queries like below.
select data[0].querytype from kccdata1;

select data.season[1] from kccdata1;

But if I want to find something like all the sectors where crop=others, I am unable to do it using this table. 
Any suggestion on this will be of great help. Thanks.

A:

lateral view outer inline

Inline (Built-in Table-Generating Functions (UDTF)) 
LateralView

select  k.response
       ,d.* 

from    kccdata1 k 
        lateral view outer inline (k.data) d
;

+------------+----------+--------------+------------+---------------------------------+---------------------+------------------------------------------+-----------------------------------------------------------------------------+----------------+----------------+--------------------+----------------------+
| k.response | d.season |   d.sector   | d.category |             d.crop              |     d.querytype     |               d.querytext                |                                  d.kccans                                   |  d.statename   | d.districtname |    d.blockname     |     d.submitdate     |
+------------+----------+--------------+------------+---------------------------------+---------------------+------------------------------------------+-----------------------------------------------------------------------------+----------------+----------------+--------------------+----------------------+
| Data Found | RABI     | HORTICULTURE | Fruits     | Mango                           | Cultural Practices  | how to control flower drop in mango ?    | recommended for spray to planofix 5-7 mili/pump                             | ANDHRA PRADESH | NELLORE        | BALAYAPALLE        | 11/1/2016 9:05:27 AM |
| Data Found | RABI     | AGRICULTURE  | Others     | Others                          | Weed Management     | weed management in eucalyptus            | RECOMMENDED TO SPRAY GLYPOSATE @ 2 LITRES PER ACRE                          | ANDHRA PRADESH | NELLORE        | MARRIPADU          | 11/1/2016 9:07:04 AM |
| Data Found | RABI     | HORTICULTURE | Vegetables | Bhindi(Okra/Ladysfinger)        | Plant Protection    | OKRA MITE DAMAGE                         | RECOMMENDED TO SPRAY DICOFOL 1 LITER /200 LITRES OF WATER /ACRE             | ANDHRA PRADESH | NELLORE        | KOVUR              | 11/1/2016 3:11:59 PM |
| Data Found | RABI     | AGRICULTURE  | Cereals    | Paddy (Dhan)                    | Weed Management     | paddy weed management at 15-20 days crop | RECOMMENDED TO SPRAY CYHALOPOP-P- BUTYL 250 ML PER ACRE                     | ANDHRA PRADESH | NELLORE        | BALAYAPALLE        | 11/3/2016 2:11:17 PM |
| Data Found | KHARIF   | AGRICULTURE  | Others     | Others                          | Weather             | WEATHER REPORT                           | WEATHER REPORT CONTACT 18004253141 ,08912543031                             | ANDHRA PRADESH | NELLORE        | KALIGIRI           | 11/3/2016 5:22:22 PM |
| Data Found | RABI     | AGRICULTURE  | Pulses     | Pigeon pea (red gram/arhar/tur) | Nutrient Management | RED GRAM NUTRIENT MANAGEMENT             | RECOMMENDED TO SPRAY 13-045 ONE KG PER ACRE                                 | ANDHRA PRADESH | NELLORE        | ANUMASAMUDRAMPETA  | 11/3/2016 7:25:10 PM |
| Data Found | RABI     | AGRICULTURE  | Pulses     | Pigeon pea (red gram/arhar/tur) | Nutrient Management | RED GRAM NUTRIENT MANAGEMENT             | RECOMMENDED TO SPRAY 13-0-45 ONE KG/ACRE                                    | ANDHRA PRADESH | NELLORE        | ANUMASAMUDRAMPETA  | 11/3/2016 7:30:02 PM |
| Data Found | RABI     | HORTICULTURE | Fruits     | Citrus                          | Plant Protection    | CITRUS SUCKING PEST                      | RECOMMENDED FOR DIMETHOATE 400 ML / ACRE 200 LITER WATER                    | ANDHRA PRADESH | NELLORE        | KODAVALUR          | 11/4/2016 8:48:03 AM |
| Data Found | RABI     | HORTICULTURE | Fruits     | Citrus                          | Plant Protection    | CITRUS SUCKING PEST                      | RECOMMENDED FOR DIMETHOATE 400 ML / ACRE 200 LITER WATER                    | ANDHRA PRADESH | NELLORE        | KODAVALUR          | 11/4/2016 8:48:04 AM |
| Data Found | KHARIF   | AGRICULTURE  | Others     | Others                          | Weather             | weather report                           | Considerable cloudiness with a shower or thunderstorm around this afternoon | ANDHRA PRADESH | NELLORE        | VINJAMUR           | 11/4/2016 3:14:03 PM |
| Data Found | KHARIF   | AGRICULTURE  | Others     | Others                          | Weather             | WEATHER RTEPORT                          | Considerable cloudiness with a shower or thunderstorm around this afternoon | ANDHRA PRADESH | NELLORE        | MARRIPADU          | 11/4/2016 5:18:50 PM |
| Data Found | KHARIF   | AGRICULTURE  | Others     | Others                          | Plant Protection    | BENGAL GRAM BEST VARIETY                 | BENGAL GRAM BEST VARIETY : J.G-11, LAM SENAGA (LBEG-7),ICCV-10              | ANDHRA PRADESH | NELLORE        | PELLAKUR           | 11/4/2016 7:57:44 PM |
+------------+----------+--------------+------------+---------------------------------+---------------------+------------------------------------------+-----------------------------------------------------------------------------+----------------+----------------+--------------------+----------------------+

