I have a ORDS GET API endpoint structured as follows available at a sample URI like this https://myapi.test/data/customer/:customer_id/connections
select :customer_id as CUSTOMER_ID, CURSOR(SELECT CONNECTION_ID,IS_ACTIVE from CONNECTIONS where CONNECTION_CUSTOMER_ID = :customer_id) AS CONNECTIONS FROM DUAL
This works fine and produces a JSON in this format when used with a :customer_id = 12345
{
"customer_id": "12345",
"connections": [{
"connection_id": "123",
"is_active": 0
}, {
"connection_id": "456",
"is_active": 0
}, {
"connection_id": "789",
"is_active": 1
}, {
"connection_id": "10",
"is_active": 1
}]
}
Now I want to use the built-in ORDS query filtering options documented here to only show the connections that are active on my customer.
This is when I run into problems. These are the formatting options I have tried and the result.
Format | Result | Error message |
---|---|---|
{"connections[*].active:"1"} | 400 - bad request | no message |
{"connections.active:"1"} | 400 - bad request | no message |
{"connections":{"connection_id":"123"}} | 403 - forbidden | The request could not be processed because a function referenced by the SQL statement being evaluated is not accessible or does not exist |
{"connections[*]":{"connection_id":"123"}} | 403 - forbidden | The request could not be processed because a function referenced by the SQL statement being evaluated is not accessible or does not exist |
At this point, I'm starting to wonder if it is possible to filter on the values inside an array. And if that is possible, then I'm guessing that my use of the CURSOR function to create the array is what is causing me grief.
For anyone else looking at how to do this, currently i found no way to do this with the built-in filtering just like @thatjeffsmith said in his comment to my original question. The solution I used in the end was the query string to pass in a optional parameter, I then use that parameter in my sql to do the filtering. Thus my original query then became:
select :customer_id as CUSTOMER_ID,
CURSOR (SELECT CONNECTION_ID,
IS_ACTIVE
from CONNECTIONS
where CONNECTION_CUSTOMER_ID = :customer_id
AND (:p_active is null or IS_ACTIVE = :p_active)) AS CONNECTIONS
FROM DUAL
This allows me to not apply the filter by not sending the optional parameter, or apply the filter by setting ?is_active=1
or ?is_active=0
.