如何将JSON数组解析为单独记录并存储到CLOB类型的values列

下面提供两类常用实现方式，可根据你的技术栈选择：

方案1：数据库直接处理 #

PostgreSQL（版本≥9.4） #

假设原始JSON存储在表source_table的raw_json列，查询/插入语句如下：

-- 直接查询结果，需要插入的话在前面加INSERT INTO target_table (values) 即可
SELECT jsonb_pretty(elem)::TEXT AS values
FROM source_table,
     -- 拆分value数组为单行单个JSON对象
     jsonb_array_elements(raw_json::jsonb -> 'value') AS elem,
     -- 生成2条序列，实现每个JSON对象重复输出2次
     generate_series(1,2);

Oracle（版本≥12cR1） #

SELECT json_serialize(elem PRETTY) AS values
FROM source_table,
     -- 拆分JSON数组
     json_table(raw_json, '$.value[*]' COLUMNS (elem CLOB PATH '$')),
     -- 生成2行实现重复
     (SELECT LEVEL FROM dual CONNECT BY LEVEL <= 2);

方案2：代码层预处理后入库（Python示例） #

适用于需要先做业务逻辑处理再入库的场景：

import json
# 导入对应数据库驱动，比如MySQL用pymysql，Oracle用cx_Oracle，SQL Server用pyodbc

# 原始JSON数据
raw_json_str = '''
{
  "value": [
    {
      "id": "123",
      "createdDateTime": "2021-09-17T14:15:18Z"
    },
    {
      "id": "124",
      "createdDateTime": "2022-09-17T14:15:18Z"
    }
  ]
}
'''

# 解析并处理生成待插入数据
json_data = json.loads(raw_json_str)
insert_rows = []
for item in json_data["value"]:
    # indent=2 实现JSON格式化换行，不需要的话移除该参数即可
    formatted_json = json.dumps(item, indent=2)
    # 每个对象加2次到待插入列表
    insert_rows.append((formatted_json,))
    insert_rows.append((formatted_json,))

# 后续执行批量插入即可，以下为示例逻辑，根据你的数据库类型调整
# conn = 你的数据库连接对象
# cursor = conn.cursor()
# cursor.executemany("INSERT INTO target_table (values) VALUES (%s)", insert_rows)
# conn.commit()

提示：如果不需要JSON输出带换行的格式化效果，移除对应代码中的格式化参数（PostgreSQL的jsonb_pretty、Oracle的PRETTY、Python的indent=2）即可得到紧凑的JSON字符串。

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