Oracle如何在JSON_QUERY中使用表字段值作为动态JSON查询路径

JSON_QUERY的路径参数默认要求是编译阶段可确定的静态字符串，不支持直接传入动态拼接的字段值，所以你之前用CONCAT的写法无法正常解析。

解决方案 #

方案1：JSON_TABLE 展开匹配（兼容 Oracle 12cR2 及以上，推荐） #

将JSON列的所有键值对展开后，直接和a.pwr字段匹配即可，写法如下：

SELECT TRIM(a.symbol),
       TRIM(a.ex_name),
       to_char(a.date_rw, 'dd-MON-yyyy'),
       a.pwr,
       a.last,
       j.value
FROM   adviser_log a
       INNER JOIN profit_model_d b
       ON a.date_rw = b.date_rw
       CROSS JOIN JSON_TABLE(
           b.mval,
           '$.*'
           COLUMNS (
               key VARCHAR2(100) PATH '$.$key',
               value VARCHAR2(4000) FORMAT JSON PATH '$'
           )
       ) j
WHERE  a.date_rw = '08-OCT-2021'
AND    j.key = TO_CHAR(a.pwr);

注意如果a.pwr是数值类型，需要转成字符串和JSON的字符串键名匹配。

方案2：动态路径变量（兼容 Oracle 18c 及以上） #

高版本Oracle支持在JSON路径中通过PASSING子句传入动态变量，写法更简洁：

SELECT TRIM(a.symbol),
       TRIM(a.ex_name),
       to_char(a.date_rw, 'dd-MON-yyyy'),
       a.pwr,
       a.last,
       JSON_QUERY(b.mval, '$.@key' PASSING TO_CHAR(a.pwr) AS "key") as value
FROM   adviser_log a
       INNER JOIN profit_model_d b
       ON a.date_rw = b.date_rw
WHERE  a.date_rw = '08-OCT-2021';

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