Search

OData Support

Features Overview

OData is an OASIS standard, which essentially enhances plain REST by standardized query options like $select, $expand, $filter, etc. Find a rough overview of the feature coverage in the following table.

Query Options Remarks Node.js Java
$value Retrieves single rows/values ✔ n/a
$count Get number of rows for paged results ✔ ✔
$top,$skip Requests paginatied results ✔ ✔
$select Like SQL select clause ✔ ✔
$orderby Like SQL order by clause ✔ ✔
$filter Like SQL where clause ✔ ✔
$expand Deep-read associated entities ✔ ✔
$search Search in multiple/all text fields ✔ ✔
$apply For data aggregation ✔ in prog.
Lambda Operators Boolean expressions on a collection ✔ ✔ (1)

(1) Current limitation: Navigation path identifying the collection can only contain one segment.

Learn more in the Getting Started guide on odata.org

By default, $search requests are applied to all String elements in the root target entity. In addition, add @Capabilities.SearchRestrictions.Searchable on entity-level to include them as nested entities, and add @Search.defaultSearchElement to elements to be involved in search.

Learn more in the reference guide on OData Annotations in CDS.

OData Annotations

The following sections explain how to add OData annotations to CDS models and how they’re mapped to EDMX outputs.

Terms and Properties

OData defines a strict two-fold key structure composed of @<Vocabulary>.<Term> and all annotations are always specified as a Term with either a primitive value, a record value, or collection values. The properties themselves may, in turn, be primitives, records, or collections.

Example

@Common.Label: 'Customer'
@Common.ValueList: {
  Label: 'Customers',
  CollectionPath: 'Customers'
}
entity Customers { }

This is represented in CSN as follows:

{"definitions:"{
  "Customers":{
    "kind": "entity",
    "@Common.Label": "Customer",
    "@Common.ValueList.Label": "Customers",
    "@Common.ValueList.CollectionPath": "Customers"
  }
}}

And would render to EDMX as follows:

<Annotations Target="MyService.Customers">
  <Annotation Term="Common.Label" String="Customer"/>
  <Annotation Term="Common.ValueList">
    <Record Type="Common.ValueListType">
      <PropertyValue Property="Label" String="Customers"/>
      <PropertyValue Property="CollectionPath" String="Customers"/>
    </Record>
  </Annotation>
</Annotations>

The value for @Common.ValueList is flattened to individual key-value pairs in CSN and ‘restructured’ to a record for OData exposure in EDMX. The rules for this restructuring from CSN sources are:

For each annotated target definition in CSN:

  1. Annotations with a single-identifier key are skipped (as OData annotations always have @Vocabulary.Term... key signature).
  2. All individual annotations with the same @<Vocabulary.Term> prefix are collected and…
  3. If there is (only!) one without suffix, → that one is a scalar or array value of an OData term.
  4. If there are more with suffix key parts →, it’s a record value for the OData term.

Qualified Annotations

OData foresees qualified annotations, which essentially allow to specify different values for a given property. CDS syntax for annotations was extended to also allow appending OData-style qualifiers after a # sign to an annotation key. However, always only as the last component of a key in the syntax.

For example, this is supported:

@Common.Label: 'Customer'
@Common.Label#Legal: 'Client'
@Common.Label#Healthcare: 'Patient'
@Common.ValueList: {
  Label: 'Customers',
  CollectionPath:'Customers'
}
@Common.ValueList#Legal: {
  Label: 'Clients',
  CollectionPath:'Clients'
}

and would render as follows in CSN:

{
  "@Common.Label": "Customer",
  "@Common.Label#Legal": "Clients",
  "@Common.Label#Healthcare": "Patients",
  "@Common.ValueList.Label": "Customers",
  "@Common.ValueList.CollectionPath": "Customers",
  "@Common.ValueList#Legal.Label": "Clients",
  "@Common.ValueList#Legal.CollectionPath": "Clients",
}

Note that there’s no interpretation and no special handling to these qualifiers in CDS you have to write and apply them 1:1 as your chosen OData vocabularies specify them. Note also that something like…

@Common.ValueList#Legal.Label: "Clients"

… isn’t supported in CDS syntax → you’d always have to write that as in line three of the snippet above, which anyways is the recommended and preferred way to reflect OData’s distinction of Terms and Properties.

Primitives

The annotation @Some isn’t a valid term definition. The following example illustrates the rendering of primitive values.

Primitive annotation values, that are, Strings, Numbers, true, false, and null are mapped to corresponding OData annotations as follows:

@Some.Null: null
@Some.Boolean: true
@Some.Integer: 1
@Some.Number: 3.14
@Some.String: 'foo'

<Annotation Term="Some.Null"><Null/></Annotation>
<Annotation Term="Some.Boolean" Bool="true"/>
<Annotation Term="Some.Integer" Int="1"/>
<Annotation Term="Some.Number" Decimal="3.14"/>
<Annotation Term="Some.String" String="foo"/>

Records

The annotation @Some isn’t a valid term definition. The following example illustrates the rendering of record values.

Record-like source structures are mapped to <Record> nodes in EDMX, with primitive types translated analogously to above:

@Some.Record: {
  Null: null
  Boolean: true
  Integer: 1
  Number: 3.14
  String: 'foo'
}

<Annotation Term="Some.Record">
  <Record>
    <PropertyValue Property="Null"><Null/></PropertyValue>
    <PropertyValue Property="Boolean" Bool="true"/>
    <PropertyValue Property="Integer" Int="1"/>
    <PropertyValue Property="Number" Decimal="3.14"/>
    <PropertyValue Property="String" String="foo"/>
  </Record>
</Annotation>

Frequently, you need to specify an explicit type for records in OData. Do so by adding a property named Type as the first element of the record. For example:

@UI.Identification: [
  {$Type:'UI.DataField', Value: deliveryId }
]

<Annotation Term="UI.Identification">
  <Collection>
    <Record Type="UI.DataField">
      <PropertyValue Property="Value" Path="deliveryId"/>
    </Record>
  </Collection>
</Annotation>

Collections

The annotation @Some isn’t a valid term definition. The following example illustrates the rendering of collection values.

Arrays are mapped to <Collection> nodes in EDMX and if primitives showing up as direct elements of the array, these elements are wrapped into individual primitive child nodes of the resulting collection as in. The rules for records and collections being applied recursively:

@Some.Collection: [
  true, 1, 3.14, 'foo',
  { $Type:'UI.DataField', Label:'Whatever', Hidden }
]

<Annotation Term="Some.Collection">
  <Collection>
    <Null/>
    <Bool>true</Bool>
    <Int>1</Int>
    <Decimal>3.14</Decimal>
    <String>foo</String>
    <Record Type="UI.DataField">
      <PropertyValue Property="Label" String="Whatever"/>
      <PropertyValue Property="Hidden" Bool="True"/>
    </Record>
  </Collection>
</Annotation>

References

The annotation @Some isn’t a valid term definition. The following example illustrates the rendering of reference values.

References in cds annotations are mapped to .Path properties or nested <Path> elements respectively:

@Some.Term: Some.Reference
@Some.Record: {
  Value: Some.Reference
},
@Some.Collection: [
  Some.Reference
],

<Annotation Term="Some.Term" Path="Some/Reference"/>
<Annotation Term="Some.Record">
  <Record>
    <PropertyValue Property="Value" Path="Some/Reference"/>
  </Record>
</Annotation>
<Annotation Term="Some.Collection">
  <Collection>
    <Path>Some/Reference</Path>
  </Collection>
</Annotation>

Enumeration Values

Enumeration symbols are mapped to corresponding EnumMember properties in OData. For example:

@Common.FieldControl: #Hidden
@Common.FilterExpressionRestrictions: [{
  Property: deliveryDate,
  AllowedExpressions: #SingleInterval,
}]

<Annotation Term="Common.FieldControl" EnumMember="Common.FieldControlType/Hidden"/>
<Annotation Term="Common.FilterExpressionRestrictions">
  <Collection>
    <Record>
      <PropertyValue Property="Property" PropertyPath="deliveryDate"/>
      <PropertyValue Property="AllowedExpressions" EnumMember="Common.FilterExpressionType/SingleInterval"/>
    </Record>
  </Collection>
</Annotation>

Annotating Annotations

OData has the possibility of annotating annotations. This often occurs in combination with enums like UI.Importance and UI.TextArrangement. CDS has no corresponding language feature. For OData annotations, nesting can be achieved in the following way:

  • when a Record is to be annotated, add an additional element to the CDS source structure. The name of this element is the full name of the annotation, including the @, and needs to be quoted in order to pass the compiler.
  • when a single value is to be annotated, first turn it into a structure and put the actual value into an artificial property called $value, then add the annotation as a further property.
@UI.LineItem: { ![@UI.TextArrangement]: #TextOnly,
    $value:[
            {$Type: 'UI.DataField',Value: ApplicationName},
            {$Type: 'UI.DataField',Value: Description},
            {$Type: 'UI.DataField',Value: SourceName},
            {$Type: 'UI.DataField',Value: ChangedBy},
            {$Type: 'UI.DataField',Value: ChangedAt}
    ]
 }
@Common.Text: {
  $value: Text, ![@UI.TextArrangement]: #TextOnly
}

As TextArrangement is common, there’s a shortcut for this specific situation:

...
@Common: {
  Text: Text, TextArrangement: #TextOnly
}

In both cases, the resulting EDMX is:

<Annotation Term="UI.LineItem">
  <Collection>
    <Record Type="UI.DataField">
      ...
      <Annotation Term="UI.Importance" EnumMember="UI.ImportanceType/High"/>
    </Record>
  </Collection>
</Annotation>
<Annotation Term="Common.Text" Path="Text">
  <Annotation Term="UI.TextArrangement" EnumMember="UI.TextArrangementType/TextOnly"/>
</Annotation>

sap: Annotations

In general, backends and SAP Fiori UIs understand or even expect OData V4 annotations. You should use those rather than the OData V2 SAP Extensions.

If necessary, CDS automatically translates OData V4 annotations to OData V2 SAP extensions when invoked with v2 as OData version. This means you shouldn’t have to care for the latter at all.

Nevertheless, in case you need to do so you can add sap:... attribute-style annotations as follows:

  @sap.applicable.path: 'to_eventStatus/EditEnabled'
  action EditEvent ...

Which would render to OData EDMX as follows:

  <FunctionImport Name="EditEvent" ...
    sap:applicable-path="to_eventStatus/EditEnabled">
    ...
  </FunctionImport>

The rules are:

  • Only strings are supported as values
  • The first dot in @sap. is replaced by a colon :
  • Subsequent dots are replaced by dashes

Differences to ABAP

In contrast to ABAP CDS, we apply a generic, isomorphic approach where names and positions of annotations are exactly as specified in the OData Vocabularies. This has the following advantages:

  • Single source of truth — users only need to consult the official OData specs
  • Speed — we don’t need complex case-by-case mapping logic
  • No bottlenecks — we always support the full set of OData annotations
  • Bidirectional mapping — we can translate CDS to EDMX and vice versa

Not the least, it also saves us lots of efforts as we don’t have to write derivate of all the OData vocabulary specs.

OData Vocabularies

OASIS Vocabularies

Vocabulary Description
@Core for general purpose annotations
@Capabilities for restricting capabilities of a service
@Validation for adding validation rules
@Authorization for authorization requirements
@Aggregation for describing aggregatable data
@Measures for monetary amounts and measured quantities

SAP Vocabularies

Vocabulary Description
@Common for all SAP vocabularies
@Communication for annotating communication-relevant information
@PersonalData for annotating personal data
@Analytics for annotating analytical resources
@UI for presenting data in user interfaces

Data Aggregation

Data aggregation in OData V4 is leveraged by the system query option $apply, which defines a pipeline of transformations that is applied to the input set specified by the URI. On the result set of the pipeline the standard system query options come into effect.

Example

GET /Order(10)/books?
    $apply=filter(year eq 2000)/
           groupBy((author/name),aggregate(price with average as avg))&
    $orderBy=title&$top=3

This query operates on the books of the order with ID 10. It firstly filters out the books from year 2000 to an intermediate result set. The intermediate result set is grouped by author name and the price is averaged. Finally, the result set is sorted by title and only the top 3 entries are retained.

Transformations

Transformation Description Node.js Java
filter filter by filter expression ✔ ✔
search filter by search term or expression n/a ✔
groupby group by dimensions and aggregates values ✔ ✔
aggregate aggregate values ✔ ✔
compute add computed properties to the result set n/a ✔
expand expand navigation properties n/a n/a
concat append additional aggregation to the result n/a n/a
top/skip paginate n/a n/a
orderby sort the input set n/a n/a
topcount/bottomcount retain highest/lowest n values n/a n/a
toppercent/bottompercent retain highest/lowest p% values n/a n/a
topsum/bottomsum retain n values limited by sum n/a n/a

Aggregation Methods

Aggregation Method Description Node.js Java
min smallest value ✔ ✔
max largest ✔ ✔
sum sum of values ✔ ✔
average average of values ✔ ✔
countdistinct count of distinct values ✔ ✔
custom method custom aggregation method n/a n/a
       
$count number of instances in input set n/a n/a

Other Features

Feature Node.js Java
use path expressions in transformations ✔ ✔
chain transformations ✔ ✔
chain transformations within group by n/a n/a
groupby with rollup/$all n/a n/a
$expand result set of $apply n/a n/a
$filter/$search result set ✔ ✔
sort result set with $orderby ✔ ✔
paginate result set with $top/$skip ✔ ✔

OData v2 Support

While CAP defaults to OData V4, the latest protocol version, some projects need to fallback to OData V2, for example, to keep using existing V2-based UIs. CAP supports that through the OData V2 proxy protocol adapter.

Using the V2 Proxy with Node.js Apps

If Node.js projects, add the proxy as express.js middleware as follows:

  1. Add the proxy package to your project:
npm add @sap/cds-odata-v2-adapter-proxy
  1. Add this to a project-local ./srv/server.js:
const proxy = require('@sap/cds-odata-v2-adapter-proxy')
const cds = require('@sap/cds')
cds.on('bootstrap', app => app.use(proxy()))
module.exports = cds.server
  1. Access services through OData V2 at http://localhost:4004/v2
  2. Access services through OData V4 at http://localhost:4004 (as before)

Find more detailed instructions at @sap/cds-odata-v2-adapter-proxy.

Enabling OData V2 support in Java Apps

Although it is also possible to use the OData V2 proxy protocol adapter in Java projects, the recommended way is to use the natively supported OData v2 Adapter. To enable the feature, please, refer to Java Migration guide.

Miscellaneous

Omitting Elements from APIs

Add annotation @cds.api.ignore to suppress unwanted entity fields (for example, foreign-key fields) in APIs exposed from this the CDS model, that is, OData or Open API. For example:

entity Books { ...
  @cds.api.ignore 
  author : Association to Authors;
}