Oracle中含未知/任意键的JSON对象转表格方法问询

假设你的产品规格表名为product_specs，存储JSON的字段为spec_json（支持JSON、VARCHAR2或CLOB类型），以下是两种适配不同Oracle版本的实现方案：

方案一：Oracle 19c+ 推荐写法 #

利用Oracle 19c新增的keyvalue()语法，直接遍历JSON对象的所有键值对，代码简洁高效：

SELECT
    ps.product_id,
    jt.key_name AS "key",
    jt.val AS "val"
FROM
    product_specs ps,
    JSON_TABLE(
        ps.spec_json,
        '$'
        COLUMNS (
            NESTED PATH '$.keyvalue()' COLUMNS (
                key_name VARCHAR2(100) PATH 'key',
                val VARCHAR2(1000) PATH 'value'
            )
        )
    ) jt;

方案二：Oracle 12c/18c 兼容写法 #

如果你的Oracle版本低于19c，先通过JSON_KEYS提取所有键名，再逐个匹配取值：

SELECT
    ps.product_id,
    key_name AS "key",
    JSON_VALUE(ps.spec_json, '$."' || key_name || '"') AS "val"
FROM
    product_specs ps,
    JSON_TABLE(
        JSON_KEYS(ps.spec_json),
        '$[*]' COLUMNS (key_name VARCHAR2(100) PATH '$')
    ) keys;

性能优化建议（针对200万行数据量） #

• 若spec_json是VARCHAR2或CLOB类型，创建JSON搜索索引提升查询效率：

  CREATE SEARCH INDEX spec_json_idx ON product_specs(spec_json) FOR JSON;
  

• 分批处理数据，避免一次性返回海量结果，例如：

  SELECT * FROM (
      -- 此处放入上述主查询语句
  ) WHERE ROWNUM <= 10000;
  

• 处理多类型JSON值时，可通过CASE统一格式：

  SELECT
      ps.product_id,
      key_name AS "key",
      CASE
          WHEN JSON_VALUE(ps.spec_json, '$."' || key_name || '" TYPE') = 'NUMBER' 
              THEN TO_CHAR(JSON_VALUE(ps.spec_json, '$."' || key_name || '"'))
          WHEN JSON_VALUE(ps.spec_json, '$."' || key_name || '" TYPE') = 'BOOLEAN'
              THEN CASE JSON_VALUE(ps.spec_json, '$."' || key_name || '"') WHEN 'true' THEN '是' ELSE '否' END
          ELSE JSON_VALUE(ps.spec_json, '$."' || key_name || '"')
      END AS "val"
  FROM
      product_specs ps,
      JSON_TABLE(JSON_KEYS(ps.spec_json), '$[*]' COLUMNS (key_name VARCHAR2(100) PATH '$')) keys;
  

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