Query in Spark SQL inside an array

Question

To give the backfround I have loaded the JSON using

sqlContext.read.json(sn3://...)
df.registerTable("posts")

I have the following schema for my table in Spark

scala> posts.printSchema
root
 |-- command: string (nullable = true)
 |-- externalId: string (nullable = true)
 |-- sourceMap: struct (nullable = true)
 |    |-- hashtags: array (nullable = true)
 |    |    |-- element: string (containsNull = true)
 |    |-- url: string (nullable = true)
 |-- type: string (nullable = true)

I want to select all posts with hashtag "nike"

sqlContext.sql("select sourceMap['hashtags'] as ht from posts where ht.contains('nike')");

I get an error undefined function ht.contains

I am not sure what method to use to search within the array.

Thanks!

lazywiz · Accepted Answer · 2016-03-03 23:59:05Z

17

I found the answer referring to Hive SQL.

sqlContext.sql("select sourceMap['hashtags'] from posts where array_contains(sourceMap['hashtags'], 'nike')");

The key function is array_contains()

answered Mar 3, 2016 at 23:59

lazywiz

1,1112 gold badges14 silver badges28 bronze badges

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

Collectives™ on Stack Overflow

Query in Spark SQL inside an array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related