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.

    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 },
      forUpdate: { 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
    having a predicate expression
    groupBy an array of expressions
    orderBy an array of ordering terms
    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:

    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}},
      forUpdate: {wait:10}
    }}
    

    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'] }
    }}
    
    Show/Hide Beta Features