Spark - Convert a coulmn containing JSON string from StringType to ArrayType(StringType())

Question

I have a dataframe df that contains json string something like below,

'''[{"@id":"Party_1","@ObjectID":"Policy_1"},{"@id":"Party_2","@ObjectID":"Policy_2"},{"@id":"Party_3","@ObjectID":"Policy_3"}]'''

df schema:

root
 |-- col1: string (nullable = true)

How do I convert this to array of string (ArrayType(StringType()))?

result should be something like,

['{"@id":"Party_1","@OriginatingObjectID":"Policy_1"}',
 '{"@id":"Party_2","@OriginatingObjectID":"Policy_2"}',
 '{"@id":"Party_3","@OriginatingObjectID":"Policy_3"}']

result schema:

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

Any help would be appreciated. Thank you!

Your "expected result" does not have the schema you're describing. The result you're expecting is ArrayType(MapType(StringType())). Be clearer in what you're expecting please. — jrip
Sorry, I have updated the result. The expected result is Array of JSON Strings. — Bharath

koiralo koiralo · Accepted Answer · 2021-06-07T08:54:21

You can use from_json function the get the json fields with slight modification on values as below

data = [
    ('[{"@id":"Party_1","@ObjectID":"Policy_1"},{"@id":"Party_2","@ObjectID":"Policy_2"},{"@id":"Party_3","@ObjectID":"Policy_3"}]', 2767),
    ('[{"@id":"Party_1","@ObjectID":"Policy_1"},{"@id":"Party_2","@ObjectID":"Policy_2"},{"@id":"Party_3","@ObjectID":"Policy_3"}]', 4235)
]

df = spark.createDataFrame(data).toDF(*["value", "count"])\
    .withColumn("value", f.regexp_replace(f.col("value"), "\\[\\{", "{\"arr\": [{"))\
    .withColumn("value", f.regexp_replace(f.col("value"), "\\}\\]", "}]}"))


json_schema = spark.read.json(df.rdd.map(lambda row: row.value)).schema
resultDF = df.select(f.from_json("value", 
schema=json_schema).alias("array_col"))\
    .select("array_col.*")

resultDF.printSchema()
resultDF.show(truncate=False)

Or you can use custom schema if you want nested json as string.

Output Schema:

root
 |-- arr: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- @ObjectID: string (nullable = true)
 |    |    |-- @id: string (nullable = true)

Output:

+---------------------------------------------------------------+
|arr                                                            |
+---------------------------------------------------------------+
|[{Policy_1, Party_1}, {Policy_2, Party_2}, {Policy_3, Party_3}]|
|[{Policy_1, Party_1}, {Policy_2, Party_2}, {Policy_3, Party_3}]|
+---------------------------------------------------------------+

Spark - Convert a coulmn containing JSON string from StringType to ArrayType(StringType())

1 Answers