如何将含键与值列表的JSON文档按指定格式插入Oracle数据库

我有如下格式的JSON文件：

{
  "key1": [ "v11", "v12", "v13"],
  "key2": [ "v21", "v22", "v23", "v24"],
  "key3": [ "v31", "v32"],
  ...
}

尝试将其插入Oracle数据库时，数据会以键作为列名、值列表作为单行CLOB的形式存储（格式1）：

key1                   |      key2                    | key3           | ....
-----------------------+------------------------------+-----------------+--------
["v11", "v12", "v13"]  | ["v21", "v22", "v23", "v24"] | [ "v31", "v32"]

但我希望按如下格式加载或转换现有数据（格式2）：

Project Name   |      Values
---------------+------------------
key1           | "v11", "v12", "v13"
key2           | "v21", "v22", ........
....

请问如何按该格式加载数据，或把现有数据转换为该格式？

一、直接将JSON加载为目标格式 #

利用Oracle原生的JSON处理函数JSON_TABLE，可以直接解析JSON并生成格式2的数据，无需先存储为格式1。

示例SQL（JSON作为CLOB输入） #

SELECT 
  jt.project_name, 
  jt.values_list
FROM JSON(
  '{
    "key1": [ "v11", "v12", "v13"],
    "key2": [ "v21", "v22", "v23", "v24"],
    "key3": [ "v31", "v32"]
  }'
) j,
JSON_TABLE(
  j.value,
  '$.*'
  COLUMNS
    project_name VARCHAR2(100) PATH '$key',
    values_list VARCHAR2(4000) PATH '$value'
) jt;

如果JSON存储在文件中，可先通过UTL_FILE读取文件内容为CLOB，再套用上述逻辑解析。

二、将已存储的格式1数据转换为格式2 #

假设数据已存在表json_storage中，列名对应JSON的key（如key1、key2、key3），可通过UNPIVOT配合字符串处理实现转换。

1. 将列转为键值对 #

SELECT 
  project_name, 
  value_clob AS values_list
FROM json_storage
UNPIVOT (
  value_clob FOR project_name IN (
    key1 AS 'key1',
    key2 AS 'key2',
    key3 AS 'key3'
    -- 需列出所有目标列名
  )
);

2. 可选：去除数组方括号 #

如果需要去掉values_list中的首尾方括号，用REGEXP_REPLACE处理：

SELECT 
  project_name,
  REGEXP_REPLACE(value_clob, '^\[|\]$', '') AS values_list
FROM json_storage
UNPIVOT (
  value_clob FOR project_name IN (
    key1 AS 'key1',
    key2 AS 'key2',
    key3 AS 'key3'
  )
);

列名较多时的优化 #

若表中列名数量大，可通过动态SQL自动生成UNPIVOT的列列表，比如查询USER_TAB_COLUMNS获取表的所有列名，拼接成IN子句执行。

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