selecting a range of elements in an array spark sql
<p>I use <code>spark-shell</code> to do the below operations.</p>

<p>Recently loaded a table with an array column in spark-sql .</p>

<p>Here is the DDL for the same:</p>

<pre class="lang-sql prettyprint-override"><code>create table test_emp_arr{
    dept_id string,
    dept_nm string,
    emp_details Array&lt;string&gt;
}
</code></pre>

<p>the data looks something like this</p>

<p>

<pre><code>+-------+-------+-------------------------------+
|dept_id|dept_nm|                     emp_details|
+-------+-------+-------------------------------+
|     10|Finance|[Jon, Snow, Castle, Black, Ned]|
|     20|     IT|            [Ned, is, no, more]|
+-------+-------+-------------------------------+
</code></pre>

<p>I can query the emp_details column something like this :</p>

<pre class="lang-scala prettyprint-override"><code>sqlContext.sql("select emp_details[0] from emp_details").show
</code></pre>

<p><strong>Problem</strong></p>

<p>I want to query a range of elements in the collection :</p>

<p><strong>Expected query to work</strong></p>

<pre class="lang-scala prettyprint-override"><code>sqlContext.sql("select emp_details[0-2] from emp_details").show
</code></pre>

<p>or </p>

<pre><code>sqlContext.sql("select emp_details[0:2] from emp_details").show
</code></pre>

<p><strong>Expected output</strong></p>

<pre class="lang-none prettyprint-override"><code>+-------------------+
|        emp_details|
+-------------------+
|[Jon, Snow, Castle]|
|      [Ned, is, no]|
+-------------------+
</code></pre>

<p>In pure Scala, if i have an array something as :</p>

<pre class="lang-sql prettyprint-override"><code>val emp_details = Array("Jon","Snow","Castle","Black")
</code></pre>

<p>I can get the elements from 0 to 2 range using </p>

<pre class="lang-scala prettyprint-override"><code>emp_details.slice(0,3)
</code></pre>

<p>returns me </p>

<pre><code>Array(Jon, Snow,Castle)
</code></pre>

<p>I am not able to apply the above operation of the array in spark-sql.</p>

<p>Thanks</p>