Modifying queries, replacing the WHERE clause

04 February 2026
cap,
cql,
cqn

Here's a quick post to explain how to modify existing query objects in CAP, specifically using the CAP Node.js cds.ql API.

The result of modifying existing queries can be a little unexpected at first. For example, you might have a query object where you want to replace the WHERE clause before you execute it. Here's how not to do it, and then how to do it.

Setup

To illustrate, we can use my cdsnano script to set up a tiny project with a services.cds file containing:

context qmacro {
  entity Books {
    key ID    : Integer;
        title : String;
  }
}

service Bookshop {
  entity Books as projection on qmacro.Books;
}

Let's add a couple of sample books in test/data/qmacro-Books.csv:

ID,title
1,Book 1
2,Book 2

Now we can start up the cds REPL and ask for a CAP server to be started for this project:

cds repl --run .

Creating the query

Let's create a query object with a WHERE condition:

> q1 = cds.ql `
  SELECT from ${Bookshop.entities.Books} where title = 'Book 1'
`
cds.ql {
  SELECT: {
    from: { ref: [ 'Bookshop.Books' ] },
    where: [ { ref: [ 'title' ] }, '=', { val: 'Book 1' } ]
  }
}

This works as expected:

> await db.run(q1)
[ { ID: 1, title: 'Book 1' } ]

Modifying the query (wrong)

Let's say we want to change the WHERE condition, to retrieve any book with a title starting with "Book".

What happens when we do this:

> q1.where(`title like 'Book%'`)
cds.ql {
  SELECT: {
    from: { ref: [ 'Bookshop.Books' ] },
    where: [
      { ref: [ 'title' ] },
      '=',
      { val: 'Book 1' },
      'and',
      { ref: [ 'title' ] },
      'like',
      { val: 'Book%' }
    ]
  }
}

The .where(...) does not replace, but append - notice that both predicates:

title = 'Book 1'
title like 'Book%'

are present, and joined with a logical and.

The result of this query is not what we want:

> await db.run(q1)
[ { ID: 1, title: 'Book 1' } ]

Modifying the query (right)

We can replace the WHERE clause completely, supplying a new predicate, like this:

> q1.SELECT.where = cds.ql.predicate `title like 'Book%'`
[ { ref: [ 'title' ] }, 'like', { val: 'Book%' } ]

And indeed, this is what the entire query object looks like now:

> q1
cds.ql {
  SELECT: {
    from: { ref: [ 'Bookshop.Books' ] },
    where: [ { ref: [ 'title' ] }, 'like', { val: 'Book%' } ]
  }
}

This does what we want:

> await db.run(q1)
[
  { ID: 1, title: 'Book 1' },
  { ID: 2, title: 'Book 2' }
]

Modifying a copy of the query

It's probably a good idea to think about modifying a copy of the query, so you always have the original. We can use cds.ql.clone() for that. Here's how it would work, in this example context:

> q2 = cds.ql.clone(q1)

then we can operate on q2 and keep q1 intact.

Wrapping up

Nice!

For more info, check out the Querying in JavaScript topic in Capire, which, in the upcoming January 2026 release (that should be out in the next few days) will be expanded and fully constructed.