How to convert json <String> array to csv in exc sql

Question

I tried this query to get the necessary experience from related data.

 Dataset<Row> filteredData = spark
                    .sql("select full_name ,experience from (select *, explode(experience['title']) exp from tempTable )"
                            + "  a where lower(exp) like '%developer%'");

But I got this error:

and finally I tried, but I got more lines with the same name.

Dataset<Row> filteredData = spark
                    .sql("select full_name ,explode(experience) from (select *, explode(experience['title']) exp from tempTable )"
                            + "  a where lower(exp) like '%developer%'");

Please give me a hint on how to convert an array of a string to a comma separated string in the same column.

Answer 1

You can use UDF to create a separate comma line.

Create UDF like this

def mkString(value: WrappedArray[String]): String = value.mkString(",")

Register UDF in sparkSQL context

sqlContext.udf.register("mkstring", mkString _)

Apply it to a SparkSQL query

sqlContext.sql(select mkstring(columnName) from tableName)

it will return a single array value