jsonoracleoracle-ords

How to use query filtering in ORDS on values in an array


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.


Solution

  • 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.