Spark SQL中如何将嵌套结构字符串解析为指定多列？

问题背景 #

需要将以下非标准格式的字符串数据提取为4个独立列：PostingType、AccountRef.name、AccountRef.value、AccountRef.AcctNum：

原始字符串示例：{PostingType=Credit, AccountRef={name=John Doe, value=27, AcctNum=111111}}

尝试使用from_json函数，但不清楚如何处理其中的嵌套结构。

解决方案 #

1. 转换为标准JSON格式 #

给定的字符串不是标准JSON（使用=替代:，字符串值无引号），首先通过正则替换转换为合法JSON：

SELECT 
  regexp_replace(
    regexp_replace(
      raw_str,
      '([a-zA-Z0-9_]+)=([^,{}]+)(?=[,}])',
      '$1:"$2"'
    ),
    '={',
    ':{') AS json_str
FROM your_table

• 第一个正则：匹配键值对，将key=value替换为key:"value"，给字符串值添加引号
• 第二个正则：修正嵌套结构的格式，将AccountRef={...}转为AccountRef:{...}

2. 解析嵌套JSON并提取字段 #

使用from_json结合嵌套Struct Schema解析JSON，再直接提取所需字段：

WITH json_converted AS (
  SELECT 
    regexp_replace(
      regexp_replace(
        raw_str,
        '([a-zA-Z0-9_]+)=([^,{}]+)(?=[,}])',
        '$1:"$2"'
      ),
      '={',
      ':{') AS json_str
  FROM your_table
),
json_parsed AS (
  SELECT 
    from_json(
      json_str,
      'struct<PostingType:string,AccountRef:struct<name:string,value:string,AcctNum:string>>'
    ) AS parsed_data
  FROM json_converted
)
SELECT
  parsed_data.PostingType,
  parsed_data.AccountRef.name,
  parsed_data.AccountRef.value,
  parsed_data.AccountRef.AcctNum
FROM json_parsed

可选调整 #

• 如果value或AcctNum是数值类型，可将Schema中的string改为int/bigint
• 若原始字符串存在值包含逗号等特殊情况，需微调正则表达式适配

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