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.

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)

❗ Warning

Because of SQL injection, it's strongly discouraged to use cds.parse.cql in your request handlers.

Content

• SELECT
  • Example
• UPSERT
• INSERT
• UPDATE
• DELETE
• CREATE
• DROP

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 },
  forUpdate: { wait: number },
  forShareLock: { wait: number },
  search: _xpr,
  count: Boolean
}}

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 names
where	a predicate expression
groupBy	an array of expressions
having	a predicate expression
orderBy	an array of ordering terms
limit	a dictionary of two expressions: rows and offset
search	a predicate expression
count	a Boolean

source         =  ( ref | SELECT ) + { as:string }
join           =  { join:string, args:[...source], on:_xpr }
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:

• as – a string specifying a chosen source alias

Joins combine two sources with these properties:

• join is one of 'left', 'right', 'full', 'inner', or 'cross'
• args is an array of two sources or joins
• on is a predicate expression capturing the JOIN condition

Column Expressions are a plain string '*', or expressions with these optional additional properties:

• as is a string with the chosen name in the result set
• cast is a CSN type definition
• inline | expand are nested projections

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

• sort = 'asc' | 'desc'
• nulls = 'first' | 'last'

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,
} excluding {
  dummy
}
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"}
    }
  ],
  excluding: [
    "dummy"
  ],
  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:

• values is an array of values, which positionally match to specified columns.
• rows is an array of one or more values.
• entries is an array of records with name-value pairs.

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 one or more primary key values 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'] }
}}