Filtering & Options

Use OData query options to filter, sort, page, and shape the data returned by the MVP One API.

OData is an open standard for accessing data over HTTP. It lets you query data through URLs in a way that is similar to SQL.

Main OData Features

Feature	SQL Example	OData Example
Querying specific table columns	`SELECT ENAME, JOB, SAL FROM People`	`/People?$select=ENAME,JOB,SAL`
Querying the second 5 records	`SELECT * FROM Accounts ORDER BY (SELECT NULL)` `OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY`	`/Accounts?$top=5&$skip=5`
Ordering data	`SELECT * FROM WorkOrders ORDER BY ActualStartDate DESC, SAL`	`/WorkOrders?$orderby=ActualStartDate desc,SAL`
Querying joined tables	`SELECT * FROM Dept LEFT OUTER JOIN Accounts ON Dept.DEPTNO = Accounts.DEPTNO`	`/Depts?$expand=Accounts`
Filtering data	`SELECT * FROM PurchaseOrders WHERE (SAL/2 > 500 AND OrderedDate <= '01/01/1985') OR (COMM IS NOT NULL AND ENAME LIKE 'J%')`	`/PurchaseOrders?$filter=(SAL div 2 gt 500 and OrderedDate le 1985-01-01) or (COMM ne null and startswith(ENAME,'J'))`
Aggregating data	`SELECT SUM(SAL) AS Sum, MAX(SAL) AS Max, MIN(SAL) AS Min, AVG(SAL) AS Avg FROM PartStockReplenishment`	`/PartStockReplenishment?$apply=aggregate(SAL with sum as Sum,SAL with max as Max,SAL with min as Min,SAL with average as Avg)`

Use the $filter query option to apply mathematical, logical, and string-based filtering. OData operators and functions are case-sensitive.

/Emps?$filter=(SAL div 2 gt 500 and HIREDATE le 1985-01-01) or (COMM ne null and startswith(ENAME,'J'))

SQL	OData
`=`	`eq`
`!=`	`ne`
`>`	`gt`
`>=`	`ge`
`<`	`lt`
`<=`	`le`
`AND`	`and`
`OR`	`or`
`NOT`	`not`
`+`	`add`
`-`	`sub`
`*`	`mul`
`/`	`div`
`%`	`mod`
`()`	`()`
`IS NULL`	`eq null`
`IS NOT NULL`	`ne null`
`X LIKE '%Y%'`	`substringof('Y',X)` in OData v1-v3 `contains(X,'Y')` in OData v4
`X LIKE 'Y%'`	`startswith(X,'Y')`
`X LIKE '%Y'`	`endswith(X,'Y')`