求助：将嵌套JSON扁平化，需把Message字段从StringType转为MapType

核心问题在于Message字段是JSON格式的字符串，需要先将其反序列化为字典，再与顶层字段合并，实现整个JSON结构的扁平化。以下是Python的实现方案：

步骤说明 #

1. 解析Message字符串：使用JSON解析工具将字符串转为字典类型
2. 合并字段：将顶层字段与解析后的Message字段合并，移除原Message键
3. 处理数组字段：可选操作，将数组类型字段转为逗号分隔的字符串（若需完全扁平化）

代码实现 #

import json

# 原始AWS SNS通知JSON
raw_json = '''
{
  "Type" : "Notification",
  "MessageId" : "5b37cfab-8a88-5609-b179-93f87126030a",
  "SequenceNumber" : "10000000000000010000",
  "TopicArn" : "arn:aws:sns:us-east-1:956978673417:de-eec-org-group-association-info.fifo",
  "Message" : "{\\"messageType\\": \\"NEW_USER_ASSOCIATION\\",\\"messageStatus\\": \\"SUCCESS\\",\\"messageDetails\\": \\"Successfully Associated\\",\\"organizationId\\": 1000830784,\\"organizationName\\": \\"Pfizer\\",\\"emailDomains\\": [\\"@pfizer.com\\",\\"@gmail.com\\"],\\"parentDunsNumber\\": \\"879262386\\",\\"cdcId\\": \\"49f5b88e036d46f38e05ce260aeaeb2a\\",\\"isUserActive\\": true,\\"associatedDateTime\\": 1674467158154,\\"associatedBy\\": \\"01bdd9a4a21345929b7fa06acd3c53d5\\"}",
  "Timestamp" : "2023-02-01T05:32:30.825Z",
  "UnsubscribeURL" : "https://sns.us-east-1.amazonaws.com/?Action=Unsubscribe&SubscriptionArn=arn:aws:sns:us-east-1:956978673417:de-eec-org-group-association-info.fifo:e986e954-6f3d-4309-b7c4-a4df2182227e"
}
'''

# 1. 解析顶层JSON
data = json.loads(raw_json)

# 2. 解析Message字段的JSON字符串
message_data = json.loads(data['Message'])

# 3. 合并字段：移除原Message键，合并顶层字段与Message解析后的数据
flattened_data = {**{k: v for k, v in data.items() if k != 'Message'}, **message_data}

# 4. 可选：将数组字段转为逗号分隔的字符串（完全扁平化）
if 'emailDomains' in flattened_data:
    flattened_data['emailDomains'] = ', '.join(flattened_data['emailDomains'])

# 输出扁平化结果
print(json.dumps(flattened_data, indent=2))

输出结果 #

{
  "Type": "Notification",
  "MessageId": "5b37cfab-8a88-5609-b179-93f87126030a",
  "SequenceNumber": "10000000000000010000",
  "TopicArn": "arn:aws:sns:us-east-1:956978673417:de-eec-org-group-association-info.fifo",
  "Timestamp": "2023-02-01T05:32:30.825Z",
  "UnsubscribeURL": "https://sns.us-east-1.amazonaws.com/?Action=Unsubscribe&SubscriptionArn=arn:aws:sns:us-east-1:956978673417:de-eec-org-group-association-info.fifo:e986e954-6f3d-4309-b7c4-a4df2182227e",
  "messageType": "NEW_USER_ASSOCIATION",
  "messageStatus": "SUCCESS",
  "messageDetails": "Successfully Associated",
  "organizationId": 1000830784,
  "organizationName": "Pfizer",
  "emailDomains": "@pfizer.com, @gmail.com",
  "parentDunsNumber": "879262386",
  "cdcId": "49f5b88e036d46f38e05ce260aeaeb2a",
  "isUserActive": true,
  "associatedDateTime": 1674467158154,
  "associatedBy": "01bdd9a4a21345929b7fa06acd3c53d5"
}

其他语言思路 #

如果使用Java/Scala等JVM语言，可借助Jackson/Gson库解析Message字符串为Map，再与原对象的字段合并；使用JavaScript则直接用JSON.parse()解析Message字段后展开对象即可。

内容的提问来源于stack exchange，提问作者SAIKAT BARDHAN