Search

Query Language (CQL)

CDS QL is based on standard SQL, which it enhances by…

Postfix Projections

CQL allows to put projections, i.e. the SELECT clause, behind the FROM clause enclosed in curly-braces. For example, the following are equivalent:

SELECT name, address.street from Authors;
SELECT from Authors { name, address.street };

Nested Expands

Postfix projections can be appended to any column referring to a struct element or an association and hence be nested. This allows expand results along associations and hence read deeply structured documents:

SELECT from Authors {
   name, address { street, town { name, country }}
};

This actually executes three correlated queries to authors, addresses, and towns and returns a structured result set like that:

results = [
  {
    name: 'Victor Hugo',
    address: {
      street: '6 Place des Vosges', town: {
        name: 'Paris',
        country: 'France'
      }
    }
  }, {
    name: 'Emily Brontë', ...
  }, ...
]

This is rather a feature tailored to NoSQL databases and has no equivalent in standard SQL as it requires structured result sets. Some SQL vendors allow things like that with non-scalar subqueries in SELECT clauses.

Nested Inlines

Put a "." before the opening brace to inline the target elements and avoid writing lengthy lists of paths to read several elements from the same target. For example:

SELECT from Authors {
   name, address.{ street, town.{ name, country }}
};

… is equivalent to:

SELECT from Authors {
  name,
  address.street,
  address.town.name,
  address.town.country
};

Smart * Selector

Within postfix projections, the * operator queries are handled slightly different than in plain SQL select clauses.

Example:

SELECT from Books { *, author.name as author }

Queries like in our example, would result in duplicate element effects for author in SQL, while in CQL explicitly defined columns following a * will replace equally named columns inferred before.

Path Expressions

Use path expressions to navigate along associations and/or struct elements in any of the SQL clauses as follows.

Path Expressions in from Clauses

Path expressions in from clauses allow to fetch only those entries from a target entity, which are associated to a parent entity. They unfold to SEMI JOINS in plain SQL queries. For example, the above queries would unfold to the following plain SQL counterparts:

SELECT * from Books WHERE EXISTS (
  SELECT 1 from Authors WHERE Authors.ID = Books.author_ID
    AND Authors.name='Emily Brontë'
);
SELECT * from Towns WHERE EXISTS (
  SELECT 1 from Authors WHERE Authors.town_ID = Towns.ID AND EXISTS (
    SELECT 1 from Books WHERE Books.author_ID = Authors.ID
  )
);

Path Expressions in All Other Clauses

Path expressions in all other clauses are very much like standard SQL’s column expressions with table aliases as single prefixes. CQL essentially extends the standard behavior to paths with multiple prefixes, each resolving to a table alias from a corresponding LEFT OUTER JOIN. For example, the path expressions in the above queries would unfold to the following plain SQL queries:

SELECT Books.title, author.name from Books
LEFT JOIN Authors author ON author.ID = Books.author_ID;
SELECT Books.*, author_address_town.name from Books
LEFT JOIN Authors author ON author.ID = Books.author_ID
LEFT JOIN Addresses author_address ON author_address.ID = author.address_ID
LEFT JOIN Towns author_address_town ON author_address_town.ID = author_address.town_ID;
SELECT Books.* from Books
LEFT JOIN Authors author ON author.ID = Books.author_ID
WHERE author.name='Emily Brontë'

Note that all column references get qualified → in contrast to plain SQL joins there is no risk of ambiguous or conflicting column names.

With Infix Filters

Append infix filters to associations in path expressions to narrow the resulting joins. For example:

SELECT books[genre='Mystery'].title from Authors
 WHERE name='Agatha Christie'

… unfolds to:

SELECT books.title from Authors
LEFT JOIN Books books ON ( books.author_ID = Authors.ID )
  AND ( books.genre = 'Mystery' )  --> from Infix Filter
WHERE Authors.name='Agatha Christie';

CDL-style Casts

Instead of SQL-style type casts you can alternatively use CDL-style casts. For example, the following statements are equivalent:

SELECT cast (foo+1 as Decimal) as bar from Foo;  -- standard SQL
SELECT from Foo { foo+1 as bar : Decimal };      -- CDL-style

learn more about CDL type definitions

SQL-style type casts are not yet supported.

Excluding Clause

Use the excluding clause in combination with SELECT * to select all elements except for the ones listed in the exclude list.

SELECT from Books { * } excluding { author };
SELECT from Books { *, author.name as author, author{*} }
excluding { author.town };

The effect is about late materialization of signatures and staying open to late extensions. For example assume the following definitions:

entity Foo { foo; bar; car; }
entity Bar as SELECT from Foo excluding { bar }
entity Boo as SELECT from Foo { foo, car }

A SELECT * from Bar would result into the same as a query of Boo:

SELECT * from Bar --> { foo, car }
SELECT * from Boo --> { foo, car }

Now, assume a consumer of that package extends the definitions as follows:

extend Foo with { boo : String; }

With that, queries on Bar and Boo would return different results:

SELECT * from Bar --> { foo, car, boo }
SELECT * from Boo --> { foo, car }

Query-local Mixins

Use the mixin...into clause to logically add elements to the source of the query, which you can use and propagate in the query’s projection.

SELECT from Books mixin {
  localized : Association to LocalizedBooks on localized.ID = ID;
} into {
  ID, localized.title
};

Current Limitations

Currently, only the postfix projection syntax is supported in entity definitions in CDL sources:

supported:

entity Bar as SELECT from Foo {*};

not supported:

entity Bar as SELECT * from Foo;