Pyspark issue with json having a field with string as well as array values

user_2265190 0
Viewed 88

I have a Spark cluster and have to index JSON data to Elastic Search (ES). There is a column as follows (two samples only):

"ADDRESS": "PO box 1234, Lahore"
"ADDRESS": [ "PO box 5678, Karachi", "new city Lahore"]

The problem with above column is that, in some records, it is appearing as string and in some other cases, it is appearing as array of string. I have tested above case in ES, there is no issue in ES, it can index both cases without issue (if directly indexed).

I have written a Spark (pyspark) job to read large JSON and then insert it to ES. In Spark schema, it is showing this field as string instead of array. Now, when I insert data into ES, I get following responses

"ADDRESS": "PO box 1234, Lahore",
"ADDRESS": "[\"PO box 5678, Karachi\", \"new city Lahore\"]",

Instead of showing as array data, it is showing as string. Now, where is the issue ? I am reading JSON as follows:

df = spark.read.option("multiline", "false").json(data_path)

Updated Sample Input data as seen in dataframe enter image description here

elasticsearch pyspark apache-spark json
1 Answers

My idea is turning string "PO box 1234, Lahore" into string ["PO box 1234, Lahore"], then convert all of them into Array of String, like this:

import pyspark.sql.functions as F

(df
    .withColumn('ADDRESS2', F
        .when(~F.col('ADDRESS').startswith('['), F.concat(F.lit('["'), F.col('ADDRESS'), F.lit('"]')))
        .otherwise(F.col('ADDRESS'))
    )
    .withColumn('ADDRESS2', F.from_json('ADDRESS2', T.ArrayType(T.StringType())))
    .show(10, False)
)

# Output
# +------------------------------------------+---------------------------------------+
# |ADDRESS                                   |ADDRESS2                               |
# +------------------------------------------+---------------------------------------+
# |PO box 1234, Lahore                       |[PO box 1234, Lahore]                  |
# |["PO box 5678, Karachi","new city Lahore"]|[PO box 5678, Karachi, new city Lahore]|
# +------------------------------------------+---------------------------------------+


# root
#  |-- ADDRESS: string (nullable = true)
#  |-- ADDRESS2: array (nullable = true)
#  |    |-- element: string (containsNull = true)
user_34415100
Related