如何在Oracle数据库中拆分大型JSON文档为多个子文档

完全可以按每60个键值对拆分JSON文档，以此规避单个JSON文档超过32767字节的存储限制，同时保持数据的可管理性。Oracle提供的JSON处理函数可轻松实现该需求，以下是具体实现思路和示例：

实现步骤 #

1. 解析原JSON为行数据并分组 #

先将原JSON列中的所有键值对拆分为单行记录，同时为每条记录分配分组ID（每60个键值对为一组）：

SELECT 
    t.id,
    j.key_name,
    j.amount,
    -- 按每60个键值对分组，生成组ID
    CEIL(ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY j.key_name) / 60) AS group_id
FROM my_table t,
     JSON_TABLE(
         t.json_col,
         '$.*' COLUMNS(
             key_name VARCHAR2(100) PATH '$.key',
             amount NUMBER PATH '$.value'
         )
     ) j

• 若需保留原JSON中键值对的顺序，可改用WITH ORDINALITY获取原顺序序号，替代ROW_NUMBER()：

  CEIL(ord / 60) AS group_id
  

  对应的JSON_TABLE调整为：

  JSON_TABLE(
      t.json_col,
      '$.*' WITH ORDINALITY COLUMNS(
          key_name VARCHAR2(100) PATH '$.key',
          amount NUMBER PATH '$.value',
          ord NUMBER FOR ORDINALITY
      )
  ) j
  

2. 聚合生成拆分后的JSON文档 #

将分组后的行数据重新聚合成JSON文档，存入新表（或原表的关联字段）：

-- 创建存储拆分后JSON的表
CREATE TABLE split_json_table (
    id NUMBER,
    group_id NUMBER,
    split_json_col CLOB -- 用CLOB避免长度限制
);

-- 插入拆分后的JSON数据
INSERT INTO split_json_table
SELECT 
    id,
    group_id,
    JSON_OBJECT_AGG(key_name VALUE amount) AS split_json_col
FROM (
    -- 嵌入第一步的查询语句
    SELECT 
        t.id,
        j.key_name,
        j.amount,
        CEIL(ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY j.key_name) / 60) AS group_id
    FROM my_table t,
         JSON_TABLE(
             t.json_col,
             '$.*' COLUMNS(
                 key_name VARCHAR2(100) PATH '$.key',
                 amount NUMBER PATH '$.value'
             )
         ) j
)
GROUP BY id, group_id;

注意事项 #

• 确保数值字段类型适配大金额：若金额数值过大，可将amount字段定义为NUMBER(38,2)或对应精度的类型。
• 查询拆分后的数据时，需通过id和group_id关联，确保能还原完整的原JSON数据。
• 若原表需保留拆分逻辑，可创建视图关联原表和拆分表，方便统一查询。

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