使用PySpark移除JSON对象嵌套数组（empval）中的id字段

需求说明 #

移除PySpark DataFrame中empval列（嵌套JSON对象的数组）内的id字段，保留其余字段并生成新的JSON格式数组列newColumn。

输入数据 #

+----------+--------+----------------------------------------------------------------------------------------------------------+
| empno    | empcode| empval                                                                                                   |
+----------+--------+----------------------------------------------------------------------------------------------------------+
| employee1| 100DRE | [{"id": "123", "key1": "aeroplane", "key2": "bus", "key3": "null", key4: "null"}, {"id": "234", "key1": "te", "key2": "buses", "key3": "token", key4: "null"}, {"id": "345", "key1": "grtregert", "key2": "buggy", "key3": "", key4: "null"}] |
+----------+--------+----------------------------------------------------------------------------------------------------------+

期望输出 #

+----------+--------+---------------------------------------------------------------------------------------------------------------------+
| empno    | empcode| newColumn                                                                                                           |
+----------+--------+---------------------------------------------------------------------------------------------------------------------+
| employee1| 100DRE | [ {"key1": "aeroplane", "key2": "bus", "key3": "null", key4: "null"}, { "key1": "te", "key2": "buses", "key3": "token", key4: "null"},  {"key1": "grtregert", "key2": "buggy", "key3": "", key4: "null"}]|
+----------+--------+---------------------------------------------------------------------------------------------------------------------+

实现代码 #

通过解析JSON时只定义需要保留的字段，再重新序列化的方式，直接丢弃id字段：

df.selectExpr(
    "empno", 
    "empcode", 
    "to_json(from_json(empval, 'array<struct<key1: string, key2: string, key3: string, key4: string>>')) AS newColumn"
)

代码解析 #

• from_json(...)：将empval的JSON字符串解析为仅包含目标字段的数组结构体，解析过程会自动忽略未定义的id字段
• to_json(...)：将解析后的结构体数组重新序列化为JSON字符串，生成不含id字段的newColumn列

内容的提问来源于stack exchange，提问作者Babu Srinivasan