I need help retrieving sub arrays from JSON with a HIVE query -

i have following json.

{     "_id": "00075602-f58d-49f5-8a82-9b5fb5582443",     "activitycountedcollection": [{         "lesson": "98d66ab9-1ef4-4b61-a05d-857b3e07e0f8",         "dataset": 1,         "datetime": "2013-06-19t15:54:27.79+00:00",         "elapsedseconds": 0.0000     },     {         "lesson": "kindergarten - play & practice",         "dataset": 0,         "datetime": "2014-01-01t00:00:00",         "elapsedseconds": 0.0,         "scores": [{             "dataset": 11,             "lesson": "c241ab4b-3d11-4aca-bb9b-a8a645c1e6ca",             "activitynode": "ef2d4aae-08ad-48eb-a2e3-0ab616ef2e9c",             "datetime": "2013-07-01t15:31:11.81+00:00",             "numpossible": 2,             "numcorrect": 1,             "mastered": false         },         {             "dataset": 1,             "lesson": "01d6691f-911f-45b5-b861-778c725b4d98",             "activitynode": "a2783eb1-873d-4ae7-bd58-6ab4bf48692c",             "datetime": "2013-07-08t15:09:54.61+00:00",             "numpossible": 5,             "numcorrect": 5,             "mastered": false         }]     }] }        

i want retrieve _id value, along scores values within activitycountedcollection. docs have scores, not.

so lets resultant table :

  _id,   lesson,   datetime,   numpossible,   numcorrect 

i can retrieve scores array string, creating following table , loading said json.

 create external table if not exists $activitycounted (       id string,       activitycountedcollection array<struct<         scores:array<string>         >       >     ) 

but retrieve actual fields within scores array. have tried loading scores table string, exploding them again, believe since doesnt know individual fields, , live in string field, cant retrieved. tried recreating table include sub array.

 create external table if not exists $activitycounted (       id string,       activitycountedcollection array<struct<         scores:array<struct<           skill:string,           lesson:string,           datetime:timestamp,           numpossible:int,           numcorrect:int,           mastered:string           > >         >       >     ) 

but not work either.

i had example not complex array of fields, , able extract table, stick array table, explode table again, , extracted individual values. explode again , extract multiple fields array, stumped.

i have determined code necessary produce results in request