Search

Query Notation (CQN)

CQN is a canonical plain object representation of CDS queries. Such query objects can be obtained by parsing CQL, by using the query builder APIs, or by simply constructing respective objects directly in your code.

try it live

Examples

The following three snippets all construct the same query object:

// Parsing CQL
let query = cds.parse.cql (`SELECT from Foo`)
// Query building
let query = SELECT.from('Foo')
// Constructing CQN objects in your code
let query = {SELECT:{from:[{ref:['Foo']}]}}

That object can be executed with cds.run:

cds.run (query)

Content

SELECT

A fully equipped SELECT query is represented as an object following this template (all properties except from are optional):

SELECT = {SELECT:{
  distinct: true,
  from: source | join,
  mixin: { ...element },
  columns: projection,
  excluding: [ ...string ],
  where: _xpr,   groupBy: [ ...expr ],
  having: _xpr,  orderBy: [ ...ordering_term ],
  limit: { rows:expr, offset:expr }
}}
Property Description
from a primary source or joined sources
mixin a dictionary of several CSN element definitions
columns an array of column expressions
excluding an array of qualified names
where a predicate expression
having a predicate expression
groupBy an array of expressions
orderBy an array of ordering terms
source         =  ( ref | SELECT ) + { as:string }
join           =  { join:string, sources:[...source], on:_xpr, using:[...string] }
projection     =  [ ...column_expr ]
column_expr    =  expr + { as:string, cast:def, (expand|inline):projection }
ordering_term  =  expr + { sort: 'asc'|'desc', nulls: 'first'|'last' }

Sources are references or subqueries with an optional:

Joins combine two sources with these properties:

Column Expressions are expressions with these optional additional properties:

Ordering Terms are expressions, usually references, with one or none of…

Example

For example, the following query in CQL:

SELECT from samples.bookshop.Books {
  title, author.name as author,
  1 as one,
  x+2 as two : Integer,
  author.*,
  author.{*},
  author as a3 { *, name }
} excluding {
  dummy.foo
}
WHERE ID=111
GROUP BY x.y
HAVING x.y<9
ORDER BY title asc
LIMIT 11 OFFSET 22

is represented in CQN as:

CQN = {SELECT:{
  from: {ref:["samples.bookshop.Books"]},
  columns: [
    {ref:["title"]},
    {ref:["author","name"], as: "author"},
    {val:1, as: "one"},
    {xpr:[{ref:['x'], '+', {val:2}}], as: "two",
      cast: {type:"cds.Integer"}
    },
    {ref:['author'], inline:['*']},
    {ref:['author'], inline:['*']},
    {ref:['author'], expand:['*', {ref:['name']} ], as:'a3'},
  ],
  excluding: [
    "dummy.foo",
  ],
  where: [{ref:["ID"]}, "=", {val: 111}],
  groupBy: [{ref:["x","y"]}],
  having: [{ref:["x","y"]}, "<", {val: 9}],
  orderBy: [{ref:["title"], sort:'asc' }],
  limit: {rows:{val:11}, offset:{val:22}}
}}

UPSERT

UPSERT = {UPSERT:{
   into: ref + { as:string },
   entries: [ ...{ ...column:any } ],
   as: SELECT
}}

INSERT

INSERT = {INSERT:{
   into: ref + { as:string },
   columns: [ ...string ],
   values: [ ...any ],
   rows: [ ...[ ...any ] ],
   entries: [ ...{ ...column:any } ],
   as: SELECT
}}

Either and only one of the properties values or rows or entries is expected to be specified. Each of which is expected to have one or more entries.

Examples:

CQN = {INSERT:{
  into: { ref: ['Books'] },
  columns: [ 'ID', 'title', 'author_id', 'stock' ],
  values: [ 201, 'Wuthering Heights', 101, 12 ],
}}
CQN = {INSERT:{
  into: { ref: ['Books'] },
  columns: [ 'ID', 'title', 'author_id', 'stock' ],
  rows: [
    [ 201, 'Wuthering Heights', 101, 12 ],
    [ 251, 'The Raven', 150, 333 ],
    [ 252, 'Eleonora', 150, 234 ],
  ],
}}
CQN = {INSERT:{
  into: { ref: ['Books'], as: 'NewBooks' },
  entries: [
    { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
    { ID:251, title:'The Raven', author_id:150, stock:333 },
    { ID:271, title:'Catweazle', author_id:170, stock:222 },
  ],
}}

The last one also allows to express so-called ‘deep inserts’. Let’s assume we want to store an author with two books:

CQN = {INSERT:{ into: { ref: ['Authors'] }, entries: [
  { ID:150, name:'Edgar Allen Poe', books:[
    { ID:251, title:'The Raven' },
    { ID:252, title:'Eleonora' },
  ] },
]}}

Instead of inserting new entries for books we might want to just add relationships to already existing books, in that case just specify the primary key value(s) of the target instance.

CQN = {INSERT:{ into: { ref: ['Authors'] }, entries: [
  { ID:150, name:'Edgar Allen Poe', books:[
    251, 252,
  ] },
]}}

UPDATE

UPDATE = {UPDATE:{
   entity: ref + { as:string },
   data: { ...column:any },
   where: _xpr,
}}

DELETE

DELETE = {DELETE:{
   from: ref + { as:string },
   where: _xpr,
}}

CREATE

CREATE = {CREATE:{
   entity: entity | string,
   as: SELECT,
}}

DROP

DROP = {DROP:{
   table: ref,
   view: ref,
   entity: ref,
}}

Examples:

CQN = {DROP:{
  table: { ref: ['Books'] }
}}
CQN = {DROP:{
  view: { ref: ['Books'] }
}}
CQN = {DROP:{
  entity: { ref: ['Books'] }
}}