Supported OData Protocol Features

Skyvia Connect supports most OData protocol features. This section contains OData features supported by Skyvia Connect endpoints. The examples are made with an endpoint to a sample CRM_DEMO database for SQL Server.

Metadata

The endpoint URL itself returns the list of Entity sets corresponding to the published database tables/views or cloud objects. The endpoint URL followed by the $metadata keyword returns the endpoint metadata. For example: https://connect.skyvia.com/********/$metadata.

These metadata features are fully supported by Skyvia for both OData v1 - v3 and OData v4 endpoints.

Data Selection via Resource Paths

This is a basic OData functionality. It is supported by Skyvia for both OData v1-v3 and OData v4 endpoints. You can select entities in the following way:

Selecting Entities from Entity Sets

Adding an entity set name after the endpoint URL returns all the entities from this entity set: https://endpoint.skyvia.com/********/ProductCategories

Generated SQL is as follows:

1
2
3
4
5

SELECT
  t.CategoryID,
  t.CategoryName,
  t.ParentCategory
FROM dbo.[Product Categories] AS t WITH (NOLOCK)

Selecting Separate Entities by Entity Keys

You can also select entities by their key values: https://endpoint.skyvia.com/********/ProductCategories(1)

Generated SQL is as follows:

1
2
3
4
5
6

SELECT
  t.CategoryID,
  t.CategoryName,
  t.ParentCategory
FROM dbo.[Product Categories] AS t WITH (NOLOCK)
WHERE (t.CategoryID = 1)

Please note that in this example we used an entity with the numeric key. If an entity has a string key, the value must be quoted with single quotation marks. For OData v1-v3 endpoints, unquoted string values may work, for OData v4 endpoints they will not work. If a string literal contains single quotation mark, add the second single quotation mark next to it. Go to OData documentation to see examples of this case.

If an entity has a composite key, the syntax will be the following: https://endpoint.skyvia.com/********/OrderDetails(OrderID=1,ProductID=7101)

Specifying property names is mandatory.

Generated SQL is as follows:

1
2
3
4
5
6
7
8

SELECT
  t.OrderID,
  t.ProductID,
  t.Price,
  t.Quantity
FROM dbo.[Order Details] AS t WITH (NOLOCK)
WHERE (t.OrderID = 1
  AND t.ProductID = 7101)

When resource paths return a single entity, you can add the name of the navigation property to the path to get the corresponding related entity or entities: https://endpoint.skyvia.com/********/ProductCategories(1)/Products/

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.CategoryID = 1)

If a navigation property returns a collection of entities, you may select separate entities from the collection by their key values: https://endpoint.skyvia.com/********/ProductCategories(1)/Products(7319)

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.CategoryID = 1
  AND t.ProductID = 7319)

Then you can use navigation properties of the returned entity to navigate further: https://endpoint.skyvia.com/********/ProductCategories(1)/Products(7319)/OrderDetails

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10

SELECT
  OrderDetails.OrderID,
  OrderDetails.ProductID,
  OrderDetails.Price,
  OrderDetails.Quantity
FROM dbo.Products WITH (NOLOCK)
INNER JOIN dbo.[Order Details] AS OrderDetails WITH (NOLOCK)
  ON dbo.Products.ProductID = OrderDetails.ProductID
WHERE (dbo.Products.CategoryID = 1
  AND dbo.Products.ProductID = 7319)

Actually, you can navigate by relations to any depth, but please note that queries generated for such multi-level navigation use JOINs and, thus, may be not optimal: https://endpoint.skyvia.com/********/ProductCategories(1)/Products(7319)/OrderDetails(OrderID=10,ProductID=7319)/OrderDetails_Order/Orders_Company/

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SELECT
  Orders_Company.CompanyID,
  Orders_Company.CompanyName,
  Orders_Company.PrimaryContact,
  Orders_Company.Web,
  Orders_Company.Email,
  Orders_Company.AddressTitle,
  Orders_Company.Address,
  Orders_Company.City,
  Orders_Company.Region,
  Orders_Company.PostalCode,
  Orders_Company.Country,
  Orders_Company.Phone,
  Orders_Company.Fax
FROM dbo.Products WITH (NOLOCK)
LEFT OUTER JOIN dbo.[Order Details] AS OrderDetails WITH (NOLOCK)
  ON dbo.Products.ProductID = OrderDetails.ProductID
INNER JOIN dbo.Orders AS OrderDetails_Order WITH (NOLOCK)
  ON OrderDetails.OrderID = OrderDetails_Order.OrderID
INNER JOIN dbo.Company AS Orders_Company WITH (NOLOCK)
  ON OrderDetails_Order.CompanyID = Orders_Company.CompanyID
WHERE (dbo.Products.CategoryID = 1
  AND dbo.Products.ProductID = 7319
  AND OrderDetails.OrderID = 10
  AND OrderDetails.ProductID = 7319)

$select and $expand Query Options

In OData, the $select query option allows requesting a specific set of properties. The $expand query option specifies the related entities to be included in the returned data. It is somewhat similar to SQL JOIN — it accepts the names of the entity navigation properties.

Features Supported for Both OData v1-v3 and OData v4

To query separate columns, list them as a value of the $select query option separated by commas. The asterisk character means all fields.

https://endpoint.skyvia.com/********/ProductCategories?$select=CategoryID,CategoryName

Generated SQL is as follows:

1
2
3
4

SELECT
  t.CategoryID,
  t.CategoryName
FROM dbo.[Product Categories] AS t WITH (NOLOCK)

Querying all the columns from the related entity looks like the following:

https://endpoint.skyvia.com/********/ProductCategories?$select=Products&$expand=Products

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT
  dbo.[Product Categories].CategoryID,
  dbo.[Product Categories].CategoryName,
  dbo.[Product Categories].ParentCategory,
  Products.ProductID,
  Products.ProductName,
  Products.CategoryID,
  Products.UnitName,
  Products.UnitScale,
  Products.InStock,
  Products.Price,
  Products.DiscontinuedPrice,
  Products.Discontinued
FROM dbo.[Product Categories] WITH (NOLOCK)
LEFT OUTER JOIN dbo.Products AS Products WITH (NOLOCK)
  ON dbo.[Product Categories].CategoryID = Products.CategoryID

A more useful case is to query all the data from related entities and some (or all) fields from the main entity: https://endpoint.skyvia.com/********/ProductCategories?$select=*,Products&$expand=Products

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT
  dbo.[Product Categories].CategoryID,
  dbo.[Product Categories].CategoryName,
  dbo.[Product Categories].ParentCategory,
  Products.ProductID,
  Products.ProductName,
  Products.CategoryID,
  Products.UnitName,
  Products.UnitScale,
  Products.InStock,
  Products.Price,
  Products.DiscontinuedPrice,
  Products.Discontinued
FROM dbo.[Product Categories] WITH (NOLOCK)
LEFT OUTER JOIN dbo.Products AS Products WITH (NOLOCK)
  ON dbo.[Product Categories].CategoryID = Products.CategoryID

While the OData protocol supports selecting only part of fields from the related entities, Skyvia does not support such requests.

OData v4 Specific Features

The following features are introduced by the fourth version of OData protocol and, thus, are not supported by Skyvia’s OData v1 – v3 endpoints.

In OData v4, $expand can be used without $select. The following query returns all the ProductCategories together with their products: https://endpoint.skyvia.com/********/ProductCategories?$expand=Products

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SELECT
  dbo.[Product Categories].CategoryID,
  dbo.[Product Categories].CategoryName,
  dbo.[Product Categories].ParentCategory,
  Products.ProductID,
  Products.ProductName,
  Products.CategoryID,
  Products.UnitName,
  Products.UnitScale,
  Products.InStock,
  Products.Price,
  Products.DiscontinuedPrice,
  Products.Discontinued
FROM dbo.[Product Categories] WITH (NOLOCK)
LEFT OUTER JOIN dbo.Products AS Products WITH (NOLOCK)
  ON dbo.[Product Categories].CategoryID = Products.CategoryID

The $expand query option in OData v4 also accepts the asterisk character as a value, which means, expanding all the navigation properties: https://endpoint.skyvia.com/********/ProductCategories?$expand=*

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

SELECT
  dbo.[Product Categories].CategoryID,
  dbo.[Product Categories].CategoryName,
  dbo.[Product Categories].ParentCategory,
  ProductCategories_Parent.CategoryID,
  ProductCategories_Parent.CategoryName,
  ProductCategories_Parent.ParentCategory,
  ProductCategories.CategoryID,
  ProductCategories.CategoryName,
  ProductCategories.ParentCategory,
  Products.ProductID,
  Products.ProductName,
  Products.CategoryID,
  Products.UnitName,
  Products.UnitScale,
  Products.InStock,
  Products.Price,
  Products.DiscontinuedPrice,
  Products.Discontinued
FROM dbo.[Product Categories] WITH (NOLOCK)
LEFT OUTER JOIN dbo.[Product Categories] AS ProductCategories_Parent WITH (NOLOCK)
  ON dbo.[Product Categories].ParentCategory = ProductCategories_Parent.CategoryID
LEFT OUTER JOIN dbo.[Product Categories] AS ProductCategories WITH (NOLOCK)
  ON dbo.[Product Categories].CategoryID = ProductCategories.ParentCategory
LEFT OUTER JOIN dbo.Products AS Products WITH (NOLOCK)
  ON dbo.[Product Categories].CategoryID = Products.CategoryID

Skyvia supports the following query options inside $expand:

  • $filter
    This query option allows filtering query results. It is described in more details below, in the $filter Query Option section. The following query returns only ProductCategories, including discontinued Products, with the corresponding Products.

    https://endpoint.skyvia.com/********/ProductCategories?$expand=Products($filter=Discontinued%20eq%20true)

    Generated SQL is as follows:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

    SELECT
  dbo.[Product Categories].CategoryID,
  dbo.[Product Categories].CategoryName,
  dbo.[Product Categories].ParentCategory,
  Products.ProductID,
  Products.ProductName,
  Products.CategoryID,
  Products.UnitName,
  Products.UnitScale,
  Products.InStock,
  Products.Price,
  Products.DiscontinuedPrice,
  Products.Discontinued
FROM dbo.[Product Categories] WITH (NOLOCK)
LEFT OUTER JOIN dbo.Products AS Products WITH (NOLOCK)
  ON dbo.[Product Categories].CategoryID = Products.CategoryID
WHERE (Products.Discontinued = 1)

  • $search
    This query option searches for the specified string (or strings) in all the textual fields of the returned data. It is described in more details below, in the $search Query Option section. https://endpoint.skyvia.com/********/Companies?$select=*,PersonContacts&$expand=PersonContacts($search=west)

    Generated SQL is as follows:

    1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

    SELECT
  dbo.Company.CompanyID,
  dbo.Company.CompanyName,
  dbo.Company.PrimaryContact,
  dbo.Company.Web,
  dbo.Company.Email,
  dbo.Company.AddressTitle,
  dbo.Company.Address,
  dbo.Company.City,
  dbo.Company.Region,
  dbo.Company.PostalCode,
  dbo.Company.Country,
  dbo.Company.Phone,
  dbo.Company.Fax,
  PersonContacts.ContactID,
  PersonContacts.Title,
  PersonContacts.FirstName,
  PersonContacts.MiddleName,
  PersonContacts.LastName,
  PersonContacts.CompanyID,
  PersonContacts.HomePhone,
  PersonContacts.MobilePhone,
  PersonContacts.AddressTitle,
  PersonContacts.Address,
  PersonContacts.City,
  PersonContacts.Region,
  PersonContacts.PostalCode,
  PersonContacts.Country,
  PersonContacts.Phone,
  PersonContacts.Fax
FROM dbo.Company WITH (NOLOCK)
LEFT OUTER JOIN dbo.[Person Contact] AS PersonContacts WITH (NOLOCK)
  ON dbo.Company.CompanyID = PersonContacts.CompanyID
WHERE (LOWER(PersonContacts.Title) LIKE '%godwin%'
  OR LOWER(PersonContacts.FirstName) LIKE '%godwin%'
  OR LOWER(PersonContacts.MiddleName) LIKE '%godwin%'
  OR LOWER(PersonContacts.LastName) LIKE '%godwin%'
  OR LOWER(PersonContacts.HomePhone) LIKE '%godwin%'
  OR LOWER(PersonContacts.MobilePhone) LIKE '%godwin%'
  OR LOWER(PersonContacts.AddressTitle) LIKE '%godwin%'
  OR LOWER(PersonContacts.Address) LIKE '%godwin%'
  OR LOWER(PersonContacts.City) LIKE '%godwin%'
  OR LOWER(PersonContacts.Region) LIKE '%godwin%'
  OR LOWER(PersonContacts.PostalCode) LIKE '%godwin%'
  OR LOWER(PersonContacts.Country) LIKE '%godwin%'
  OR LOWER(PersonContacts.Phone) LIKE '%godwin%'
  OR LOWER(PersonContacts.Fax) LIKE '%godwin%')

  • $select
    https://endpoint.skyvia.com/********/Companies?$select=CompanyName,PersonContacts&$expand=PersonContacts($select=FirstName,LastName)

    Generated SQL is as follows:

    1
2
3
4
5
6
7
8

    SELECT
  dbo.Company.CompanyID,
  dbo.Company.CompanyName,
  PersonContacts.FirstName,
  PersonContacts.LastName
FROM dbo.Company WITH (NOLOCK)
LEFT OUTER JOIN dbo.[Person Contact] AS PersonContacts WITH (NOLOCK)
  ON dbo.Company.CompanyID = PersonContacts.CompanyID

Please note that such queries as $levels, $orderby, $skip, $top and $count are not supported inside $expand.

$orderby Query Option

The $orderby query option allows sorting query results. You can specify one or more fields to sort the results by, use ‘asc’ and ‘desc’ modifiers to change sort order, sort by field ordinals, etc. For example: https://endpoint.skyvia.com/********/PersonContacts?$orderby=LastName

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT
  t.ContactID,
  t.Title,
  t.FirstName,
  t.MiddleName,
  t.LastName,
  t.CompanyID,
  t.HomePhone,
  t.MobilePhone,
  t.AddressTitle,
  t.Address,
  t.City,
  t.Region,
  t.PostalCode,
  t.Country,
  t.Phone,
  t.Fax
FROM dbo.[Person Contact] AS t WITH (NOLOCK)
ORDER BY t.LastName

https://endpoint.skyvia.com/********/PersonContacts?$orderby=Country%20desc,%20LastName%20asc

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

SELECT
  t.ContactID,
  t.Title,
  t.FirstName,
  t.MiddleName,
  t.LastName,
  t.CompanyID,
  t.HomePhone,
  t.MobilePhone,
  t.AddressTitle,
  t.Address,
  t.City,
  t.Region,
  t.PostalCode,
  t.Country,
  t.Phone,
  t.Fax
FROM dbo.[Person Contact] AS t WITH (NOLOCK)
ORDER BY t.Country DESC, t.LastName

https://endpoint.skyvia.com/********/ProductCategories?$orderby=2

Generated SQL is as follows:

1
2
3
4
5
6

SELECT
  t.CategoryID,
  t.CategoryName,
  t.ParentCategory
FROM dbo.[Product Categories] AS t WITH (NOLOCK)
ORDER BY 2

Please note that the ordinal in the third example is 1-based, the first result field has ordinal 1.

$skip and $top Query Options

These query options provide paging functionality. The $top query option limits the result to the specified number of entities. The $skip query option allows skipping the specified number of entities, which are first in the order, and starting with the next entity in sequence.

https://endpoint.skyvia.com/********/ProductCategories?$top=2

Generated SQL is as follows:

1
2
3
4
5
6
7
8

SELECT
  t.CategoryID,
  t.CategoryName,
  t.ParentCategory
FROM dbo.[Product Categories] AS t WITH (NOLOCK)
ORDER BY (SELECT
  NULL)
OFFSET 0 ROWS FETCH FIRST 2 ROWS ONLY

https://endpoint.skyvia.com/********/ProductCategories?$skip=3

Generated SQL is as follows:

1
2
3
4
5
6
7
8

SELECT
  t.CategoryID,
  t.CategoryName,
  t.ParentCategory
FROM dbo.[Product Categories] AS t WITH (NOLOCK)
ORDER BY (SELECT
  NULL)
OFFSET 3 ROWS

https://endpoint.skyvia.com/********/ProductCategories?$skip=3&$top=2

Generated SQL is as follows:

1
2
3
4
5
6
7
8

SELECT
  t.CategoryID,
  t.CategoryName,
  t.ParentCategory
FROM dbo.[Product Categories] AS t WITH (NOLOCK)
ORDER BY (SELECT
  NULL)
OFFSET 3 ROWS FETCH FIRST 2 ROWS ONLY

$filter Query Option

This query option allows filtering the result data. It allows using various conditions, expressions, uniting them with logical operators, etc. Skyvia Connect supports most features of OData protocol v1 – v3 for this query option, and some of the OData v4 protocol features.

Comparison Operators

OData supports the following comparison operators:

Operator Operator Name Operator in OData
= equals eq
!= not equal ne
> greater than gt
>= greater than or equal ge
< less than lt
<= less than or equal le

https://endpoint.skyvia.com/********/ProductCategories?$filter=ParentCategory eq 1

Generated SQL is as follows:

1
2
3
4
5
6

SELECT
  t.CategoryID,
  t.CategoryName,
  t.ParentCategory
FROM dbo.[Product Categories] AS t WITH (NOLOCK)
WHERE (t.ParentCategory = 1)

Logical Operators

OData and Skyvia Connect support AND, OR, and NOT logical operators.

https://endpoint.skyvia.com/********/PersonContacts?$filter=Fax ne null and Country eq 'UK'

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SELECT
  t.ContactID,
  t.Title,
  t.FirstName,
  t.MiddleName,
  t.LastName,
  t.CompanyID,
  t.HomePhone,
  t.MobilePhone,
  t.AddressTitle,
  t.Address,
  t.City,
  t.Region,
  t.PostalCode,
  t.Country,
  t.Phone,
  t.Fax
FROM dbo.[Person Contact] AS t WITH (NOLOCK)
WHERE (t.Fax IS NOT NULL
  AND t.Country = 'UK')

https://endpoint.skyvia.com/********/PersonContacts?$filter=Country eq 'UK' or Country eq 'United Kingdom'

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SELECT
  t.ContactID,
  t.Title,
  t.FirstName,
  t.MiddleName,
  t.LastName,
  t.CompanyID,
  t.HomePhone,
  t.MobilePhone,
  t.AddressTitle,
  t.Address,
  t.City,
  t.Region,
  t.PostalCode,
  t.Country,
  t.Phone,
  t.Fax
FROM dbo.[Person Contact] AS t WITH (NOLOCK)
WHERE (t.Country = 'UK'
  OR t.Country = 'United Kingdom')

https://endpoint.skyvia.com/********/PersonContacts?$filter=not(Country eq 'UK' or Country eq 'United Kingdom')

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

SELECT
  t.ContactID,
  t.Title,
  t.FirstName,
  t.MiddleName,
  t.LastName,
  t.CompanyID,
  t.HomePhone,
  t.MobilePhone,
  t.AddressTitle,
  t.Address,
  t.City,
  t.Region,
  t.PostalCode,
  t.Country,
  t.Phone,
  t.Fax
FROM dbo.[Person Contact] AS t WITH (NOLOCK)
WHERE (NOT (t.Country = 'UK'
  OR t.Country = 'United Kingdom'))

Arithmetic Operations

Skyvia supports the following arithmetic operations:

Operation OData operator
Addition add
Subtraction sub
Multiplication mul
Division div
Modulus* mod

* Please note that the modulus operation is supported only for database endpoints (endpoints, publishing data from a database or cloud data warehouse).

https://endpoint.skyvia.com/********/Products?$filter=InStock div 2 gt 10

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE ((t.InStock / 2) > 10)

https://endpoint.skyvia.com/********/Products?$filter=InStock mul Price gt 10000

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE ((t.InStock * t.Price) > 10000)

OData Canonical Functions

String Functions

OData protocol and Skyvia Connect support the following string functions:

  • startswith - checks whether the string specified as the first parameter starts with the string specified as the second parameter.
  • substringof/contains - checks whether one of the specified strings contains another. Note that substringof is supported only for OData v1 – v3 endpoints, and contains is supported only for OData v4 endpoints. They also have different parameter order. substringof checks whether the second parameter contains the first, and contains – vice versa.
  • endswith - checks whether the string, specified as the first parameter, ends with the string, specified as the second parameter.
  • length - returns the length of the passed string in characters.
  • indexof - returns the zero-based position of the substring, passed as the second parameter, in a string, passed as the first parameter. If the first parameter value does not include the second, returns -1.
  • replace - replaces the substring, passed as the second parameter, with the string, passed as the third parameter, in a string, passed as the first parameter.
  • substring - returns a substring of the string, passed as the first parameter, which starts at the position, specified in the second parameter, and has length, specified as the third parameter. If the provided length is larger than the length of the remaining string, or is not specified at all, all the remaining string is returned.
  • tolower - returns the passed string in lower case.
  • toupper - returns the passed string in upper case.
  • trim - trims leading and trailing spaces in the passed string.
  • concat - concatenates the passed strings.

https://endpoint.skyvia.com/********/Products?$filter=contains(ProductName,'Twain')

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.ProductName LIKE '%Twain%'

Datetime Functions

The following datetime functions are available in OData v1 – v3, and are supported in Skyvia Connect for both OData v1 – v3 endpoints and OData v4 endpoints:

  • year - returns the year of a passed datetime value.
  • month – returns the number of month of a passed datetime value.
  • day – returns the number of day the day in the month of a passed datetime value.
  • hour – returns the hours portion of a passed datetime value in the 24 hours format.
  • minute - returns the minutes portion of a passed datetime value.
  • second - returns the seconds portion of a passed datetime value.

Please note that you can apply these functions only to Date or Datetime types or their analogs. Applying them to Time types (which don’t have the date part) is not supported.

https://endpoint.skyvia.com/********/Orders?$filter=year(OrderDate) eq 2018 and month(OrderDate) eq 12

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
  t.OrderID,
  t.CompanyID,
  t.ContactID,
  t.OrderDate,
  t.Freight,
  t.ShipDate,
  t.ShipCompanyID,
  t.Discount
FROM dbo.Orders AS t WITH (NOLOCK)
WHERE (YEAR(t.OrderDate) = 2018
  AND MONTH(t.OrderDate) = 12)

The following datetime functions are available in OData v1 – v3, and are supported in Skyvia Connect for both OData v1 – v3 endpoints and OData v4 endpoints:

  • now - returns the current datetime of a server.
  • fractionalseconds – returns the fraction of seconds portion of a passed datetime value.

https://endpoint.skyvia.com/********/Orders?$filter=year(OrderDate) eq year(now()) and month(OrderDate) eq month(now())

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
  t.OrderID,
  t.CompanyID,
  t.ContactID,
  t.OrderDate,
  t.Freight,
  t.ShipDate,
  t.ShipCompanyID,
  t.Discount
FROM dbo.Orders AS t WITH (NOLOCK)
WHERE (YEAR(t.OrderDate) = YEAR(GETDATE())
  AND MONTH(t.OrderDate) = MONTH(GETDATE()))

The following OData v4 datetime functions are not supported by Skyvia Connect:

  • totaloffsetofminutes
  • mindatetime
  • maxdatetime
  • totalnumberofseconds
  • date
  • time

Mathematical Functions

OData protocol and Skyvia Connect support the following mathematical functions:

  • round – rounds the passed numeric value to the nearest integer value.
  • floor - rounds the passed numeric value DOWN to the nearest integer value.
  • ceiling - rounds the passed numeric value UP to the nearest integer value.

However, the ceiling function is supported only for database endpoints. Correct work of the ceiling function for cloud endpoints is not guaranteed.

https://endpoint.skyvia.com/********/Orders?$filter=round(Discount) eq 2

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11

SELECT
  t.OrderID,
  t.CompanyID,
  t.ContactID,
  t.OrderDate,
  t.Freight,
  t.ShipDate,
  t.ShipCompanyID,
  t.Discount
FROM dbo.Orders AS t WITH (NOLOCK)
WHERE (ROUND(t.Discount, 0) = 2)

Special Values (true, false, null)

Such special values are fully supported in filters in Skyvia Connect.

https://endpoint.skyvia.com/********/Products?$filter=Discontinued eq true and UnitName ne null

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.Discontinued = 1
  AND t.UnitName IS NOT NULL)

Not Supported $filter Features

Skyvia Connect does not support some OData v4 features for the $filter query option. For example, it does not support geo functions, type functions, lambda operators, $root keyword, etc. It also does not support OData type functions cast and isof.

Selecting Single Property Value

Skyvia supports selecting a single property value, both via $value resource path or omitting the $value keyword.

https://endpoint.skyvia.com/********/Products(7807)/Price/$value
https://endpoint.skyvia.com/********/Products(7807)/Price

Generated SQL:

1
2
3
4

SELECT
  t.Price
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.ProductID = 7807)

$count

The $count keyword can be used as a resource path or as a query option. When being used as a resource path, it returns the number of records.

Skyvia Connect supports $count as a resource path for both OData v1 – v3 endpoints and OData v4 endpoints. However, using the $filter query option for this resource path is supported only for OData v1 – v3 endpoints.

https://endpoint.skyvia.com/********/Products/$count

Generated SQL is as follows:

1
2
3

SELECT
  COUNT_BIG(*)
FROM dbo.Products AS t WITH (NOLOCK)

Skyvia Connect supports $count (for OData v4 endpoints) and $inlinecount (for OData v1 – v3 endpoints) as a query option. In this case Skyvia Connect executes two queries – one for count, and one – for data.

https://endpoint.skyvia.com/********/Products?$filter=contains(ProductName,'Twain')&$count=true

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT
  COUNT_BIG(*)
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.ProductName LIKE '%Twain%')

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.ProductName LIKE '%Twain%')

Please note that $count is not supported in $filter and in $orderby.

$format Query Option

The $format query option allows explicitly specifying the format, in which you want to retrieve the request results. For OData v1 – v3 endpoints, Skyvia uses ATOM format by default. For OData v4 endpoints, JSON format is used by default. The $format query option allows the following file formats:

ATOM format (this format is supported for OData v1 – v3 endpoints only):

1

$format=atom

JSON format:

1

$format=json

JSON format with controllable metadata:

1
2
3

$format=application/json;odata.metadata=none 
$format=application/json;odata.metadata=minimal
$format=application/json;odata.metadata=full

The latter format was introduced in OData v4, and thus, is supported for OData v4 endpoints only.

$search Query Option

This query option was introduced in OData v4, and thus, it is supported for OData v4 endpoints only. It allows searching for the specified string (or strings) in all the textual fields of the returned data.

$search can be used together with $filter. It can include several values, united with AND or OR logical operators. NOT logical operator also can be used.

However, please note that NOT operator is supported only for database endpoints in Skyvia Connect. Using it for cloud endpoints may lead to incorrect results.

https://endpoint.skyvia.com/********/Products?$search=(Twain OR King) AND NOT Finn

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (((LOWER(t.ProductName) LIKE '%twain%'
        OR LOWER(t.UnitName) LIKE '%twain%')
      OR (LOWER(t.ProductName) LIKE '%king%'
        OR LOWER(t.UnitName) LIKE '%king%'))
    AND (NOT (LOWER(t.ProductName) LIKE '%finn%'
        OR LOWER(t.UnitName) LIKE '%finn%')))

Please note that $search results in a query with a lot of LIKE clauses for all textual columns, and thus, it may be slow.

$apply Query Option

This query option was introduced in OData v4, and thus, it is supported for OData v4 endpoints only. It allows applying one or more transformations separated by the slash characters to the returned data.

Skyvia connect supports only a subset of transformations included in the OData v4 standard. They are aggregate (which applies aggregation expressions) and filter (filters data). As for multiple transformations, Skyvia supports $apply with multiple filter transformations, separated by the slash characters, optionally followed by one aggregate transformation (it should be the last one).

Filter Transformation

This transformation takes a boolean expression, like $filter query option, and returns instances, for which this expression evaluates to true.

Aggregate Transformation

The aggregate transformation takes one or more aggregation expressions, separated with commas, as arguments. Aggregation expression consists of the following parts:

  • expression, returning a single value, like in the $filter query option. In a simple case, this can be just a path to an aggregatable property. This expression will be aggregated.
  • with keyword, followed by the aggregation method.
  • as keyword, followed by an alias for the result value. The alias must not coincide with property names, aggregates, keywords, or other aliases.

Skyvia supports the following expression methods: sum, min, max, average, countdistinct.

Skyvia does not support aggregating over related entities or using their properties via navigation properties in aggregation expressions.

Note that Skyvia does not support the virtual $count property in the aggregate transformation.

$apply Example

https://endpoint.skyvia.com/********/Products?$apply=filter(Discontinued eq true)/filter(Price gt 35)/aggregate(ProductName with countdistinct as UniqueItems, Price with average as AveragePrice, Price mul InStock with sum as TotalStock)

Generated SQL is as follows:

1
2
3
4
5
6
7

SELECT
  COUNT(DISTINCT t.ProductName) AS UniqueItems,
  AVG(t.Price) AS AveragePrice,
  SUM((t.Price * t.InStock)) AS TotalStock
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.Discontinued = 1
    AND (t.Price > 35))

Parameter Aliases

OData v4 supports parameter aliases. Skyvia Connect supports them in $filter and $orderby for OData v4 endpoints. Parameter aliases start with the @ character.

https://endpoint.skyvia.com/********/ProductCategories(1)/Products?$orderby=@p1&$filter=InStock gt @p2&@p1=ProductName&@p2=15

Generated SQL is as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

SELECT
  t.ProductID,
  t.ProductName,
  t.CategoryID,
  t.UnitName,
  t.UnitScale,
  t.InStock,
  t.Price,
  t.DiscontinuedPrice,
  t.Discontinued
FROM dbo.Products AS t WITH (NOLOCK)
WHERE (t.CategoryID = 1
    AND (t.InStock > 15))
ORDER BY t.ProductName

OData Protocol Versions

Skyvia Connect supports creating both OData v1 – v3 endpoints and OData v4 endpoints. You can select the default OData version, that is used when accessing the endpoint by its root URL, when you create or edit the endpoint.

However, actually, Skyvia makes the published data available via both protocol versions, by adding odata3/ or odata4/ to the endpoint URL:

https://endpoint.skyvia.com/********/
OData protocol version, specified in the endpoint editor is used.

https://endpoint.skyvia.com/********/odata3/
OData protocol v3 is used.

https://endpoint.skyvia.com/********/odata4/
OData protocol v4 is used.

On This Page