AX1751

Requesting data using the OData API

Using the OData API, you can request data from any table in the Axiom Table Library. The standard OData syntax provides options to filter and sort the data returned.

Creating data requests

One advantage of using the Open Data Protocol is that the syntax for data requests is standardized. You can use any of the standard OData query options, such as $filter, $orderby, $select, $expand, and $top. For more information on this syntax, see OData Query Options (external link).

To query data, use a GET request to the Axiom OData service. The GET request must include an Authorization header with a valid Axiom key. The GET request uses the following basic format:

GET <pathtoAxiom>/odata/tablename?$option=value&option=value

The following GET request examples illustrate some common request types:

GET https://mycompany.axiom.cloud/odata/dept

Returns all available rows of data in table Dept.

GET https://mycompany.axiom.cloud/odata/dept?$filter=Region eq 'US West'

Returns all available rows of data in table Dept where the Region column contains US West.

GET https://mycompany.axiom.cloud/odata/dept?$filter=Region eq 'US West'&$orderby=VP

This is the same as the previous example, except that return data is sorted by the values in the VP column.

GET https://mycompany.axiom.cloud/odata/GL2022?$select=dept,acct,m1,m2,m3

Returns all available rows of data in table GL2022, but only includes the specified columns.

GET https://mycompany.axiom.cloud/odata/GL2022?$expand=DeptRef($select=Description)

Returns all available rows of data in table GL2022, and also includes the Description column from the Dept lookup table. The GL2022 table contains a Dept column with a lookup to Dept.Dept. In order to include columns from the Dept lookup table, it must be referred to as DeptRef. This convention applies to all lookups.

GET https://mycompany.axiom.cloud/odata/GL2022?$filter=DeptRef/Region eq 'US West'&$expand=DeptRef($select=Description, Region)

This is similar to the previous example, except that now the request to table GL2022 filters the data by the Region column in the Dept lookup table. (using the syntax DeptRef/Region in the filter). This is the same effect as filtering an Axiom query to GL2022 by Dept.Region='US West'.

Request data is returned in JSON format. Each row of data returned from the table is a JSON object with columnname: value pairs. Click to see an example.

Data returned from the OData API is paged using the standard nextpage syntax. By default, the page limit is set to 100,000 records per page. Your custom solution must be designed to follow the nextpage links to read all data, if the data to be returned may exceed this limit. If necessary, you can adjust this page limit using the ODataPageSize system configuration setting.

Querying table metadata

Using the OData API, you can query the table metadata for the Axiom system. The metadata includes the table name, the key columns of the table, and each column name and data type. Lookup columns are also indicated. This is available to help you construct data queries to specific tables.

To query table metadata, use a GET request to the Axiom OData service, using the $metadata option. For example:

GET https://mycompany.axiom.cloud/odata/$metadata

It is not required to include an Authorization header in the metadata request. Metadata is available without authorization.

Metadata is returned in the following format. Click to see an example.

  • Each table is returned as an entity. The entity contains a list of key columns, then a list of all columns in the table and their data type, then a list of navigation items for each validated column (to point to the target lookup table).

  • Each table's validated columns are returned as an entity set. This set contains a list of validated columns and the target lookup table for each column.

Validated columns are listed using the convention ColumnNameRef. For example, if the GL2022 table contains a validated column Dept that looks up to the Dept table, the validated column is listed as DeptRef. The *Ref version of the column must be used with the $expand query option, in order to return data from the lookup table when querying the table with the validated column.

Remarks

  • All data requests honor the security permissions of the user identity used to authorize the request. All table names and properties are exposed when querying table metadata, but data is restricted when querying the table. If the user has no access to data in the table, then no data is returned. If the user has filtered access to data in the table, then returned data is filtered.

  • If a table or column is added to Axiom, the Axiom Application Server must be restarted before that new table or column is available to the OData API. See Refreshing the OData API for table changes.

  • If a column contains date/time values, the time is returned in local time.