Queries

An HTTP POST to /data/<ns>/<api>/query freely searches the records of an API. The body has the following structure:

interface IQueryRequest {
    /** Restriction of the records. */
    filter?: unknown
    /** Sorting of the result. */
    sort?: Record<string, 'ascending' | 'descending'>
    /** Zero-based page. */
    pageIndex?: number
    /** Maximum number of records, 1 to 10000, default 100. */
    pageSize?: number
    /** Case-insensitive comparisons. */
    noCase?: boolean
}

• pageSize is an integer between 1 and 10,000, 100 if not specified.
• pageIndex pages based on pageSize, an integer from 0, 0 if not specified.
• sort is applied before paging.
• noCase: filter and sorting are case-insensitive.

curl -X POST -u "$USER" -H 'Content-Type: application/json' \
  -d '{ "filter": { "age": { "$gte": 18 } }, "sort": { "name": "ascending" }, "pageSize": 20, "pageIndex": 0 }' \
  "$CMS/data/tutorial/people/query"

With ?exported, the query returns the records for display, see Data interface.

Field names

The same rules for field names apply to sorting and filters:

• A direct field is specified by its technical name, e.g. age.
• This also applies to lists (array), e.g. hobbies. Sorting by them is not very meaningful, however; the elements themselves are not sorted.
• Subfields of a substructure are specified with a dot, e.g. address.city.
• For localizable fields, the language code is appended with a dot, e.g. title.de.
• The rules can be combined, e.g. images.de.title for the caption title in a list images of localizable substructures.

Sorting is only possible by fields whose metadata set sortable. Filters have no such restriction, but the available comparisons depend on the data type (tables below).

CMS fields

In addition, the metadata of the CMS are available:

Field	Kind	Meaning
_id	Identifier	Identifier of the record (filter only, e.g. { "_id": { "$in": ["…", "…"] } })
_cms.created	Point in time	Time of creation
_cms.createdBy	Identifier	Account that created the record
_cms.lastModified	Point in time	Time of the last change, initially equal to created
_cms.modifiedBy	Identifier	Account that last changed the record

The editing application initially sorts lists in descending order by _cms.lastModified, so the most recently changed records are at the top.

A point in time allows the same comparisons as a number, but must be specified in a special form: ISODate("2020-11-03T11:44:59.62512Z"). The inner text is always an ISO 8601 value in UTC; fractions of a second are optional (one to five digits).

{
    "_cms.lastModified": { "$gt": "ISODate(\"2026-10-21T08:00:00Z\")" }
}

This is intended to retrieve specifically the records changed since a point in time, for example for a sync. This currently does not work, see Known limitations. Deleted records cannot be detected this way anyway; for that, the existing identifiers must be compared.

Filter

In the simplest case, results are restricted by the values of a field:

{
    "age": { "$exists": true }
}

The result contains only records whose field age has a value. $exists with true or false is available for all data types.

Most data types allow comparison with one or more values:

{
    "address.city": { "$in": ["Bonn", "Rheinbreitbach"] },
    "age": 57,
    "hobbies": { "$nin": ["Oper", "Freeclimbing"] },
    "married": { "$ne": true },
    "name": { "$eq": "Jochen" }
}

This finds records whose address.city is Bonn or Rheinbreitbach ($in), whose age is 57, whose name equals ($eq) Jochen, whose married is not ($ne) true, and whose list hobbies contains neither Oper nor Freeclimbing ($nin).

Data types with an ordering allow magnitude comparisons:

{
    "age": { "$gte": 18 }
}

This returns records with age at least ($gte) 18. Further comparisons: strictly greater ($gt), strictly less ($lt), at most ($lte).

Strings

Strings additionally offer pattern matching ($regex with $options) and a full-text search ($text). For the full-text search, fulltext must be set in the field's metadata.

{
    "first": { "$regex": "^J.che[a-z]", "$options": "i" },
    "vita": { "$text": { "$search": "React", "$caseSensitive": true } }
}

first is compared with the regular expression ^J.che[a-z], case-insensitively ($options). vita must contain the term React in exact spelling in the full-text search. $text also supports $language and $diacriticSensitive.

GEO coordinates

For GEO coordinates, besides $exists there is only $near. The two cannot be combined. You specify a reference point ($geometry, in coordinates the longitude comes first as usual) as well as a maximum ($maxDistance) and minimum ($minDistance) distance in meters.

{
    "location": {
        "$near": {
            "$geometry": { "coordinates": [9.2150267, 49.1443855], "type": "Point" },
            "$maxDistance": 280000,
            "$minDistance": 250000
        }
    }
}

$maxDistance and $minDistance are optional. $near must be at the top level of the filter, not inside $and, $or or $not.

Without sort, a query with $near returns the records by distance, nearest first. With sort, the specified sorting applies instead.

Combinations

Multiple conditions in the filter are combined with logical AND:

{
    "age": 42,
    "address.city": "Bonn"
}

With $and, $or and $not, complex searches can also be formulated:

{
    "$and": [
        { "age": 42 },
        { "$not": { "name": "Jochen" } },
        {
            "$or": [
                { "gender": "male", "address.city": "Bonn" },
                { "gender": "female", "address.city": "Köln" }
            ]
        }
    ]
}

This searches for all records with age 42 whose name is not Jochen and that have either gender male and address.city Bonn or gender female and address.city Köln. The $and could also be omitted here, since the conditions of a filter are combined with AND anyway.

Comparisons per data type

Data type	$exists	Value	$eq $ne $in $nin	$gt $gte $lt $lte
boolean	yes	yes	yes	no
email	yes	yes	yes	yes
file	yes	no	no	no
html	yes	no	no	no
number	yes	yes	yes	yes
string	yes	yes	yes	yes
geo-point	yes	no	no	no
Point in time	yes	yes	yes	yes
Identifier	yes	yes	yes	yes¹

¹ technically possible, but rarely useful for identifiers.

Data type	$regex $options $text	$near
email	yes	no
string	yes	no
geo-point	no	yes

Response

Besides the requested slice, a query returns further information:

interface IQueryResponse<T = unknown> {
    /** Number of records matching the filter. */
    filterCount: number
    /** Records in the requested slice. */
    items: T[]
    /** Number of all records of the API. */
    totalCount: number
    /** Page size actually used. */
    pageSize: number
    /** Page actually used. */
    pageIndex: number
}

filterCount is at least as large as the number in items, totalCount at least as large as filterCount. pageSize and pageIndex are the values actually used, even if they were missing in the request.

Known limitations

The following bugs affect CMS version 11.0.0 and earlier. They are fixed in the source code; the fixes take effect with the next version. The same applies to $near: in 11.0.0 it does not sort by distance, fails without $minDistance, and fails for APIs with more than one GEO coordinate.

• Filters on points in time return no matches. The value ISODate("…") is validated but not converted into a point in time. The comparison with the stored point in time therefore always fails, whether with $gt, $lt or $eq. $exists and sorting by _cms.created or _cms.lastModified work. Workaround for "changed since point in time X": sort in descending order by _cms.lastModified and page until an older record appears.

  { "sort": { "_cms.lastModified": "descending" }, "pageSize": 100 }

• noCase and filterCount: With "noCase": true, items contains the expected matches, but filterCount is counted case-sensitively and can therefore be too small (down to 0). For paging with noCase, filterCount is therefore not reliable.

Reading all records

To read all records, page until (pageIndex + 1) * pageSize >= filterCount:

async function queryAll(url, filter) {
    const all = []
    for (let pageIndex = 0; ; pageIndex++) {
        const page = await call(`${url}/query`, { method: 'POST', body: JSON.stringify({ filter, pageIndex, pageSize: 1000 }) })
        all.push(...page.items)
        if ((page.pageIndex + 1) * page.pageSize >= page.filterCount) return all
    }
}