Select

from, where, fields, order, limit and offset.

A query starts with from and its steps are chained with .. A ; ends the statement. A script can hold several statements; F5 runs them in order and stops at the first error.

NarQL

from('members', 'm')
    .where({'m.age': {gte: 30}})
    .fields('m.name', 'm.surname', 'm.age')
    .order({'m.age': 'desc', 'm.name': 'asc'})
    .limit(10)
    .offset(0)
    .get();

The second argument of from is an alias ('m'). Field names can be written as 'm.name', 'members.name' or just 'name'. .get() is optional; a select returns rows without it too.

fields#

Written as	Result
no `fields`, or `fields('*')`	every field, in schema order
`fields('name', 'age')`	only these fields, in this order
`fields({'m.name', 'm.age'})`	the same; the list can also be written in braces
`fields('*', CAST('name', 'first_name'))`	every field, then extra columns

NarQL

from('members').fields({'name', CAST('surname', 'last_name'), FORMAT('birth_date', 'dd-mm-yyyy')});

Functions: Functions.

where#

All conditions in one object must hold (AND).

NarQL

from('members').where({'age': 31, 'gender': 1}).fields('name');

Operator	Meaning
`{'age': 31}`	equal
`{'age': {ne: 31}}`	not equal
`{'age': {gt: 30}}` · `gte` · `lt` · `lte`	greater, greater or equal, less, less or equal
`{'name': {in: ['Ayşe', 'Osman']}}`	one of the list
`{'password': null}`	empty (`null`)
`{'password': {ne: null}}`	not empty

Several operators can apply to one field:

NarQL

from('members').where({'age': {gte: 30, lt: 40}}).fields('name', 'age');

Values are converted to the field's type. A date field can be searched with its own format or with ISO 8601:

NarQL

from('members').where({'birth_date': '23.09.1981'}).fields('name');
from('members').where({'birth_date': {gte: '1990-01-01'}}).fields('name');

Text without quotes is a name, not a value:

NarQL

# error: 'Osman' is a name, not a value; put text in quotes: 'Osman'
from('members').where({'name': Osman});

order, limit, offset#

NarQL

from('members').order({'age': 'asc', 'name': 'desc'}).limit(2).offset(1).fields('name', 'age');

null values sort first. decimal fields are stored as text but sort as numbers.

How rows are read#

When where searches the primary key or an indexed field with equality, NarDB reads only those rows. Every other condition is evaluated by scanning the table. Sorting and limit currently happen in memory, so on very large tables limit does not shorten the scan.

Parameters#

In the Go API values are sent separately as $1, $2; they are never embedded in the query text:

res, err := db.Exec(ctx, `from('members').where({'email': $1}).fields('name');`, email)