# Spark sql to query array fields in elastic search **URL:** \ **Category:** Elasticsearch\ **Tags:** es-hadoop\ **Created:** [September 11, 2015, 1:39pm UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143 "2015-09-11T13:39:56Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![tridib](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tridib/32/4727_2.png) [@tridib](https://discuss.elastic.co/u/tridib)\ **Post date:** [September 11, 2015, 1:39pm UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/1 "2015-09-11T13:39:56Z")
I have integrated elasticsearch with spark using elastic-spark connector. I can query elastic search through spark. But the query does not return any result when I put array fields in where clause. Elastic search document looks like bellow. { ``` "_index": "ppm", "_type": "docs", "_id": "AU-5lX7CCHEkhOBzqUsa", "_version": 1, "_score": 1, "_source": { "member_id": 1, "year": "2015", "r": [ "R1" , "R3" ] } ``` } SQL used: select member\_id from table\_xxx where r = 'R1' No result returned. I tried hive function array\_contains(r, 'R1'), but it says expected array found string. looks like elastic stores array as string type. Is there a different way to query the array fields? How can I see the elastic search native json query corresponding to SQL? Thanks -Tridib ---
**Author:** ![eliasah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliasah/32/34741_2.png) [@eliasah](https://discuss.elastic.co/u/eliasah)\ **Post date:** [September 20, 2015, 10:00am UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/2 "2015-09-20T10:00:53Z")
Can you elaborate on how you are querying Elasticsearch through Spark? ---
**Author:** ![tridib](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tridib/32/4727_2.png) [@tridib](https://discuss.elastic.co/u/tridib)\ **Post date:** [September 23, 2015, 5:44am UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/3 "2015-09-23T05:44:15Z")
I register the ES index "ppm" as "table\_xxx ". Then I query "table\_xxx". Standard spark sql. This setup works for other simple condion in where clause. But did not work for array type. ---
**Author:** ![eliasah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliasah/32/34741_2.png) [@eliasah](https://discuss.elastic.co/u/eliasah)\ **Post date:** [September 24, 2015, 3:58pm UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/4 "2015-09-24T15:58:49Z")
You know, once the index is in Spark, it's out of the Elasticsearch scope query. To query the table you have created, you should considering using functions from the Spark SQL API. ---
**Author:** ![tridib](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tridib/32/4727_2.png) [@tridib](https://discuss.elastic.co/u/tridib)\ **Post date:** [September 25, 2015, 4:45pm UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/5 "2015-09-25T16:45:21Z")
As I mentioned in my original post that spark sql query "array\_contains(r, 'R1')" did not work with elastic search. According to elastic/hadoop connector this should work. ---
**Author:** ![eliasah](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/eliasah/32/34741_2.png) [@eliasah](https://discuss.elastic.co/u/eliasah)\ **Post date:** [September 25, 2015, 4:58pm UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/6 "2015-09-25T16:58:51Z")
Can you share the reference (documentation) where is says that this actions should work? ---
**Author:** ![costin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/costin/32/44950_2.png) [@costin](https://discuss.elastic.co/u/costin)\ **Post date:** [September 27, 2015, 10:50am UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/7 "2015-09-27T10:50:55Z")
This is a known issue stemming from the fact that ES doesn't treat arrays [differently](https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-array-type.html) than single values. Which means when reading a mapping, one doesn't know whether a certain field is an array or not until after reading _all_ the values (as the array can appear at any point in time). Typically this is not an issue however since Spark SQL requires the schema to be known before hand and be fixed, when the underlying format changes, this causes issues. There's an [issue raised](https://github.com/elastic/elasticsearch-hadoop/issues/484) to address this which should hit master soon. ---
**Author:** ![tridib](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tridib/32/4727_2.png) [@tridib](https://discuss.elastic.co/u/tridib)\ **Post date:** [September 27, 2015, 6:18pm UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/8 "2015-09-27T18:18:08Z")
From elastic hadoop docs and costin's presentation I got the message that all Spark SQL will work. ---
**Author:** ![tridib](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tridib/32/4727_2.png) [@tridib](https://discuss.elastic.co/u/tridib)\ **Post date:** [September 27, 2015, 6:19pm UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/9 "2015-09-27T18:19:03Z")
Thanks for you response. Will wait for the fix. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 1:27pm UTC](https://discuss.elastic.co/t/spark-sql-to-query-array-fields-in-elastic-search/29143/10 "2017-07-06T13:27:27Z")