Spark Sql, unable to query multiple possible values in a array

Question

I have the data schema of LinkeIn account as shown below. I need to query the skills which is in the for of array, where array may contains either JAVA OR java OR Java or JAVA developer OR Java developer.

Dataset<Row> sqlDF = spark.sql("SELECT * FROM people"
            + " WHERE ARRAY_CONTAINS(skills,'Java') "
            + " OR ARRAY_CONTAINS(skills,'JAVA')"
            + " OR ARRAY_CONTAINS(skills,'Java developer') "
            + "AND ARRAY_CONTAINS(experience['description'],'Java developer')"  );

The above query is what i have tried and please suggest any better way.and also how to use case-insentive query ?

Ayan Guha · Accepted Answer · 2016-09-19 06:37:57Z

2

df.printschema()

root
 |-- skills: array (nullable = true)
 |    |-- element: string (containsNull = true)


df.show()

+--------------------+
|              skills|
+--------------------+
|        [Java, java]|
|[Java Developer, ...|
|               [dev]|
+--------------------+

Now lets register it as a temp table:

>>> df.registerTempTable("t")

Now, we will explode the array, convert each element as lower case and query using LIKE operator:

>>> res = sqlContext.sql("select skills, lower(skill) as skill from (select skills, explode(skills) skill from t) a where lower(skill) like '%java%'")
>>> res.show()
+--------------------+--------------+
|              skills|         skill|
+--------------------+--------------+
|        [Java, java]|          java|
|        [Java, java]|          java|
|[Java Developer, ...|java developer|
|[Java Developer, ...|      java dev|
+--------------------+--------------+

Now, you can do a distinct on skills field.

answered Sep 19, 2016 at 6:37

Ayan Guha

7503 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

AshisParajuli Over a year ago

can you help me more ?

Ayan Guha Over a year ago

please post your question, will help as far as I can :)

AshisParajuli Over a year ago

Ayan Guha . please have a look at this link . i wan unable to pos uqestion from this account so i have done it with another question . stackoverflow.com/questions/39634284/…

AshisParajuli Over a year ago

can you vote up my question @Ayan Gupta .. i am unable to post other questions due to 0 vote in my question

Collectives™ on Stack Overflow

Spark Sql, unable to query multiple possible values in a array

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related