如何在Oracle PL/SQL内连接查询中使用JSON值匹配获取目标字段

适用版本 #

Oracle 12c R1及以上版本（原生支持JSON操作，低于该版本需自定义JSON解析逻辑，不推荐）

核心实现思路 #

利用Oracle原生JSON函数完成JSON片段提取、相等匹配、目标字段提取：

• 用JSON_EQUAL比较两个businessKeysJSON片段是否完全相等，自动忽略空格、属性顺序等格式差异
• 用JSON_QUERY提取完整的secondaryKeys数组内容
• 用带过滤条件的JSON_VALUE直接定位提取OUTPUT_VALUE的值

注意事项 #

你给出的T1表C1字段示例内容缺少外层大括号，属于不完整JSON片段，如果实际存储确实是这种格式，需要先拼接{}转为合法JSON；如果C1本身就是完整JSON对象，删除下方SQL中的拼接逻辑即可。

纯SQL实现（直接查询可用） #

SELECT 
  JSON_QUERY(t2.C2, '$.secondaryKeys') AS secondaryKeys,
  JSON_VALUE(t2.C2, '$.secondaryKeys[?(@.name == "OUTPUT_VALUE")].value') AS output_value
FROM T1 t1
INNER JOIN T2 t2 
ON JSON_EQUAL(
  -- T1.C1如果是完整JSON，替换下方为 t1.C1, '$.businessKeys'
  '{'||t1.C1||'}', '$.businessKeys',
  t2.C2, '$.businessKeys'
);

PL/SQL存储过程实现 #

如果需要封装到PL/SQL中调用，可以参考如下存储过程写法：

CREATE OR REPLACE PROCEDURE query_secondary_info(
  p_result_cursor OUT SYS_REFCURSOR
) IS
BEGIN
  OPEN p_result_cursor FOR
    SELECT 
      JSON_QUERY(t2.C2, '$.secondaryKeys') AS secondaryKeys,
      JSON_VALUE(t2.C2, '$.secondaryKeys[?(@.name == "OUTPUT_VALUE")].value') AS output_value
    FROM T1 t1
    INNER JOIN T2 t2 
    ON JSON_EQUAL(
      '{'||t1.C1||'}', '$.businessKeys',
      t2.C2, '$.businessKeys'
    );
END;
/

低版本兼容写法（12cR1无JSON_EQUAL函数时使用） #

如果你的Oracle版本不支持JSON_EQUAL，可以将两边提取的businessKeys转为格式化字符串后比较：

SELECT 
  JSON_QUERY(t2.C2, '$.secondaryKeys') AS secondaryKeys,
  JSON_VALUE(t2.C2, '$.secondaryKeys[?(@.name == "OUTPUT_VALUE")].value') AS output_value
FROM T1 t1
INNER JOIN T2 t2 
ON JSON_QUERY('{'||t1.C1||'}', '$.businessKeys' FORMAT JSON) = JSON_QUERY(t2.C2, '$.businessKeys' FORMAT JSON);

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