如何提取非标准化JSON格式outgoing字段键中下划线后的值？

现有如下表格数据：

需要提取outgoing列中每个键下划线_后的部分，例如从a945248027_14454878提取14454878。之前误以为该JSON数据不规范导致卡壳，求技术实现方案。

你给出的outgoing列字符串是标准JSON格式（键用双引号包裹、结构合法），完全可以用常规JSON工具解析处理，以下是两种常见场景的实现：

1. Python 处理 #

通过JSON解析+字符串分割即可完成提取：

import json

# 模拟表格数据
table_data = [
    {"id": 1, "status": "paid", "outgoing": '{"a945248027_14454878":"processing","old.a945248027_14454878":"cancelled"}'},
    {"id": 2, "status": "pending", "outgoing": '{"069e5248cf_45299995":"processing"}'}
]

for row in table_data:
    # 把outgoing字段解析为字典
    outgoing_dict = json.loads(row["outgoing"])
    # 遍历所有键，提取下划线后的部分
    extracted = [key.split("_")[-1] for key in outgoing_dict if "_" in key]
    print(f"id {row['id']} 提取结果: {extracted}")

运行输出：

id 1 提取结果: ['14454878', '14454878']
id 2 提取结果: ['45299995']

2. SQL（MySQL 8.0+）处理 #

利用JSON函数+字符串函数实现批量提取：

SELECT 
    id,
    status,
    JSON_ARRAYAGG(SUBSTRING_INDEX(key_val, '_', -1)) AS extracted_parts
FROM (
    SELECT 
        id,
        status,
        JSON_TABLE(
            JSON_KEYS(outgoing),
            '$[*]' COLUMNS(key_val VARCHAR(255) PATH '$')
        ) AS keys_table
    FROM your_table_name
) t
GROUP BY id, status;

该语句会先提取outgoing中的所有键，再分割出下划线后的部分，最后聚合为数组返回结果。

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