niscDocsGitHub →
Vex · Query DSL

Optional filters (optional)

A condition wrapped in optional applies only when the request supplies its key. When the key is absent, the condition is removed before the SQL is compiled, so no caller passes a % wildcard to match every row. Example: one stored query lists every member, or only the members a search matches.

Query
{
  from: ['members'],
  fields: ['members.id', 'members.name'],
  filter: {
    optional: {
      key: 'q',
      then: { ilike: ['members.name', { $context: 'q' }] },
    },
  },
  sort: [{ field: 'members.name', dir: 'asc' }],
}
SQL, key absent
SELECT m1.id AS "id", m1.name AS "name"
FROM members AS m1
ORDER BY m1.name ASC
LIMIT 100
SQL, key present
SELECT m1.id AS "id", m1.name AS "name"
FROM members AS m1
WHERE m1.name ILIKE $1
ORDER BY m1.name ASC
LIMIT 100
Parameters
[{ key: 'q', kind: 'context', type: 'string' }]
Output matches the expected value

Runs in this page on the installed package. The input and the expected output are defined by the example.