How to explode nested array of structure with unknown array length in Hive?
<p>I have a hive table emp_test as below:</p>

<pre><code>'name' as string &lt;br&gt;
'testing' as array&lt; struct &lt; code:string,tests:array &lt; struct &lt; testtype:string,errorline:string&gt;&gt;&gt;&gt;
</code></pre>

<p>and have column values :"name" as "JOHN" and "testing" as <br></p>

<pre><code>[{"code":"cod1234","tests":[{"testtype":"java","errorline":"100"},{"testtype":"C++","errorline":"10000"}]},&lt;br&gt;
 {"code":"cod6790","tests":[{"testtype":"hive","errorline":"10"},{"testtype":"pig","errorline":"978"},{"testtype":"spark","errorline":"35"}]}
]
</code></pre>

<p>How to select these values and store in another table </p>

<pre><code>emp_test_detail(name,code,testtype,errorline) as

JOHN cod1234 java       100 &lt;br&gt;
JOHN cod1234 C++        10000&lt;br&gt;
JOHN cod6790 hive       10&lt;br&gt;
JOHN cod6790 pig        978&lt;br&gt;
JOHN cod6790 spark      35&lt;br&gt;
</code></pre>

<p>i have tried below query but got error :</p>

<pre><code>*insert into emp_test_detail select &lt;br&gt;
        emp_tasting.code, &lt;br&gt;
        emp_tasting.emp_tests.testtype, &lt;br&gt;
        emp_tasting.emp_tests.errorline from emp_test &lt;br&gt; 
lateral view explode(testing) mytest as emp_tasting &lt;br&gt;
lateral view explode(testing[0].tests) mytest as emp_tasting;* &lt;br&gt;
</code></pre>

<p>and here I don't know the exact length of testing array.so how to reference array fields?</p>

<p>Please help me on this ?</p>