Search code examples
jsonoracleoracle-ords

What is the correct FilterObject ORDS syntax?

I want to filter the result of a RestApi ORDS, using a FilterObject to send it through the url, but I can't find an example of how to group several operators and and or.

If the FilterObject for this case:

where (codigo like 'DACK%' or codigo like 'DO%')

Is this:

{"$or":[{"codigo":{"$like":"DACK%"}},{"codigo":{"$like":"DO%"}}]}

What should be the syntax for the following example:

 WHERE VALOR = 'SUPER'
   AND ( CODIGO LIKE 'DACK%'
    OR CODIGO LIKE 'DO%' )
 ORDER BY CODIGO

I use ords 19.1

Thanks

Solution

  • The 'tricky' part is the $or..

    I'm going to write an equivalent example using HR.EMPLOYEES, since I don't have your table.

    GET /ords/hr/employees?q={"job_id":{"$eq":"IT_PROG"},"email":{"$or":[{"$like":"AH%25"},{"$like":"%25VPAT%25"}]},"$orderby":{"salary":"desc"}}

    Equates to

    SELECT *
  FROM EMPLOYEES
 WHERE JOB_ID = 'IT_PROG'
   AND ( EMAIL LIKE '%VPAT%'
    OR EMAIL LIKE 'AH%' )
 ORDER BY SALARY DESC

    Which looks like this

    enter image description here

    I have more examples here

    This is officially documented here