如何在ORDS中对数组内的值执行查询过滤

问题核心 #

你遇到的问题是ORDS内置查询过滤功能对CURSOR生成的嵌套JSON数组支持有限，导致尝试的过滤语法要么触发400请求错误，要么返回403禁止访问。

原因分析 #

ORDS的内置查询过滤主要针对直接映射的表列，而CURSOR返回的是动态生成的游标结果集，ORDS无法直接解析这类嵌套结构的过滤条件，这就是报错的根本原因：

• 400错误：过滤语法存在格式问题（比如引号不配对），或者过滤字段名与返回JSON的实际字段名不匹配（你用了active，但SQL返回的是IS_ACTIVE）
• 403错误：ORDS尝试解析游标嵌套结构的过滤条件时，调用了不存在的内部函数，触发权限/函数不存在的报错

解决方案 #

方案1：直接在SQL中过滤（推荐）

既然目标是只返回活跃连接，最直接的方式是修改原SQL，在CURSOR的子查询里直接添加过滤条件，不需要依赖ORDS的过滤功能：

select :customer_id as CUSTOMER_ID, 
       CURSOR(SELECT CONNECTION_ID, IS_ACTIVE 
              from CONNECTIONS 
              where CONNECTION_CUSTOMER_ID = :customer_id
                AND IS_ACTIVE = 1) AS CONNECTIONS 
FROM DUAL

如果需要动态控制是否过滤（比如允许用户通过参数选择是否只看活跃连接），可以添加一个查询参数（比如?active=1），然后在SQL中绑定该参数：

select :customer_id as CUSTOMER_ID, 
       CURSOR(SELECT CONNECTION_ID, IS_ACTIVE 
              from CONNECTIONS 
              where CONNECTION_CUSTOMER_ID = :customer_id
                AND (:active IS NULL OR IS_ACTIVE = :active)) AS CONNECTIONS 
FROM DUAL

用户请求https://myapi.test/data/customer/12345/connections?active=1时返回活跃连接，不带参数则返回全部连接。

方案2：改用JSON函数生成数组以支持ORDS过滤

如果必须使用ORDS的内置过滤功能，需要把CURSOR替换为JSON_ARRAYAGG生成标准JSON数组，ORDS可以识别这种结构并应用过滤：

select :customer_id as CUSTOMER_ID,
       (SELECT JSON_ARRAYAGG(JSON_OBJECT('CONNECTION_ID' VALUE CONNECTION_ID, 'IS_ACTIVE' VALUE IS_ACTIVE))
        FROM CONNECTIONS
        WHERE CONNECTION_CUSTOMER_ID = :customer_id) AS CONNECTIONS
FROM DUAL

此时可以使用正确的ORDS过滤语法（注意字段名要和JSON中的键名完全一致，区分大小写）：

# 过滤活跃连接
https://myapi.test/data/customer/12345/connections?q={"connections[*].IS_ACTIVE":1}

# 过滤特定connection_id
https://myapi.test/data/customer/12345/connections?q={"connections[*].CONNECTION_ID":123}

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