Querying

Ditto provides a robust query engine that supports different filter operations. At a high-level, all queries work on a specific collection and are used to filter the collection. In addition, since Ditto works with data represented in JSON-compatible documents, the query syntax offers dot notation to reference keys within the document tree as shown below:

Select a tabJavaScriptSwiftObjective-CKotlinJavaC#C++RustHTTP

const collection = await ditto.store.collection('people').find("favoriteBook.title == 'The Great Gatsby'")

let collection = ditto.store["people"]    .find("favoriteBook.title == 'The Great Gatsby'")    .exec()

NSArray *docs = [[[ditto.store collection:@"people"]                    find:@"favoriteBook.title == 'The Great Gatsby'"] exec];

val results = ditto.store.collection("people")    .find("favoriteBook.title == 'The Great Gatsby'")    .exec()

List<DittoDocument> results = ditto.store.collection("people")        .find("favoriteBook.title == 'The Great Gatsby'")        .exec();

var results = ditto.Store.Collection("people")    .Find("favoriteBook.title == 'The Great Gatsby'")    .Exec();

std::vector<Document> results =    ditto.get_store()        .collection("people")        .find("favoriteBook.title == 'The Great Gatsby'")        .exec();

collection    .find("favoriteBook.title == \'The Great Gatsby\'")    .exec()?;

curl -X POST 'https://<CLOUD_ENDPOINT>/api/v3/store/find' \--header 'X-DITTO-CLIENT-ID: AAAAAAAAAAAAAAAAAAAABQ==' \--header 'Content-Type: application/json' \--data-raw '{    "collection": "people",    "query": "favoriteBook.title == 'The Great Gatsby'"  }'

Using query variables with $args

Often, you will query with runtime variables. Instead of building or interpolating query strings, the query system will accept variables through an $args dictionary object.

Select a tabJavaScriptSwiftObjective-CKotlinJavaC#C++RustHTTP

const query = 'name == $args.name && age <= $args.age'const documents = await ditto.store.collection('people').find(query, {  age: 32,  name: 'Max',})

let documents = ditto.store["users"].find("name == $args.name && age <= $args.age", args: [    "age": 32,    "name": "Max"]).exec()

NSArray *documents = [[[ditto.store collection:@"people"] find:@"name == $args.name && age <= $args.age" withArgs:@{@"age": @32, @"name": @"Max"}] exec];

val foundDocs = ditto.store.collection("people")    .find("name == \$args.name && age <= \$args.age", mapOf("name" to "max", "age" to 32))

Map<String, Object> queryArgs = new HashMap<>();queryArgs.put("name", "max");queryArgs.put("age", 32);
List<DittoDocument> foundDocs = ditto.store.collection("users")        .find("name == $args.name && age <= $args.age", queryArgs)        .exec();

var docs = ditto.Store    .Collection("users")    .Find(        "name == $args.name && age <= $args.age",        new Dictionary<string, object> { { "name", "max" }, { "age", 32 } })    .Exec();

json args = json({{"age", 32}, {"name", "max"}});std::vector<Document> big_c_values =    ditto.get_store()        .collection("people")        .find("name == $args.name && age <= $args.age", args)        .exec();

let args = json!({"name": "Susan", "age": 32});collection    .find_with_args("name == $args.name && age <= $args.age", args)    .exec()?;

curl -X POST 'https://<CLOUD_ENDPOINT>/api/v3/store/find' \--header 'X-DITTO-CLIENT-ID: AAAAAAAAAAAAAAAAAAAABQ==' \--header 'Content-Type: application/json' \--data-raw '{    "args": {      "name": "max",      "age": 32    },    "collection": "people",    "query": "name == $args.name && age <= $args.age"  }'

Find by _id

Every document contains it's own unique _id property. This property can be set or randomly assigned. When updating a single document within a collection you will most often find that document by it's _id property.

// finds the document with the specified _idfindByID("1234")

Sort

Before executing a query, you can specify to sort on a specific property. Call sort before a query is executed by specifying a specific property and a sort direction.

Note: Queries that do not specify a sort will assume to sort on the _id property.

The following example will sort on documents that have a mileage property

Select a tabJavaScriptSwiftObjective-CKotlinJavaC#C++RustHTTP

const sortedRedCars = await ditto.store.collection('cars').find("color == 'red'").sort('miles', 'ascending')

let sortedRedCars = ditto.store.collection("cars")    .find("color == 'red'")    .sort("miles", direction: .ascending)    .exec()

NSArray *sortedRedCars = [[[[ditto.store collection:@"cars"]                  find:@"color == 'red'"]                  sort:@"miles" direction:DITSortDirectionAscending] exec];

val sortedRedCars = ditto.store.collection("cars")    .find("color == 'red'")    .sort("miles", DittoSortDirection.Ascending)    .exec()

List<DittoDocument> sortedRedCars = ditto.store.collection("cars")        .find("color == 'red'")        .sort("miles", DittoSortDirection.Ascending)        .exec();

var sortedCars = ditto.Store.Collection("cars")    .Find("color == 'red'")    .Sort("miles", direction: DittoSortDirection.Ascending)    .Exec();

std::vector<Document> sorted_red_cars =    ditto.get_store()        .collection("cars")        .find("color == 'red'")        .sort("miles", SortDirection::ascending)        .exec();

let sort_param = ffi_sdk::COrderByParam {    query_c_str: c!("miles"),    direction: ffi_sdk::QuerySortDirection::Ascending,};collection    .find("color == \'red\'")    .sort(vec![sort_param])    .exec()?;

Not supported by HTTP.

Limit

There are times where you need to limit the number of results that a query returns. Call limit before the query is executed to trim the number of results. This is best used with sort.

Select a tabJavaScriptSwiftObjective-CKotlinJavaC#C++RustHTTP

const sortedAndLimitedRedCars = await ditto.store.collection('cars').find("color == 'red'").sort('miles', 'ascending').limit(100)

let sortedAndLimitedRedCars = ditto.store.collection("cars")    .find("color == 'red'")    .sort("miles", direction: .ascending)    .limit(100)    .exec()

NSArray *sortedAndLimitedRedCars = [[[[[ditto.store collection:@"cars"]                  find:@"color == 'red'"]                  sort:@"miles" direction:DITSortDirectionAscending]                  limit:100] exec];

val sortedAndLimitedRedCars = ditto.store.collection("cars")    .find("color == 'red'")    .sort("miles", DittoSortDirection.Ascending)    .limit(100)    .exec()

List<DittoDocument> sortedAndLimitedRedCars = ditto.store.collection("cars")        .find("color == 'red'")        .sort("miles", DittoSortDirection.Ascending)        .limit(100)        .exec();

var sortedAndLimitedRedCars = ditto.Store.Collection("cars")    .Find("color == 'red'")    .Sort("miles", direction: DittoSortDirection.Ascending)    .Limit(100).Exec();

std::vector<Document> sorted_and_limited_red_cars =    ditto.get_store()        .collection("cars")        .find("color == 'red'")        .sort("miles", SortDirection::ascending)        .limit(100)        .exec();

let sort_param = ffi_sdk::COrderByParam {    query_c_str: c!("rank"),    direction: ffi_sdk::QuerySortDirection::Ascending,};collection    .find("color == \'red\'")    .sort(vec![sort_param])    .limit(100)    .exec()?;

curl -X POST 'https://<CLOUD_ENDPOINT>/api/v3/store/find' \  --header 'X-DITTO-CLIENT-ID: AAAAAAAAAAAAAAAAAAAABQ==' \  --header 'Content-Type: application/json' \  --data-raw '{    "collection": "people",    "query": "color == 'red'",    "limit": 100  }'

Creating query strings

The Ditto query language is very similar to what you'd write in most if statements. In addition, we offer standard, easy-to-understand query condition operators that most developers should understand.

info

On the small peer, there are no secondary indexes. Queries such as status == 'Active' will have to perform a full table scan.

Navigating Document Properties

To refer to keys within the document's property tree, Ditto uses dot notation that should be familiar to most developers. Let's say we have a document like so:

{   "_id": "123abc",   "name": { "first": "Alan", "last": "Turing" },   "contact": { "phone": { "type": "cell", "number": "111-222-3333" } },   "work": { "street-line": "678 Johnson Street"}}

If you wanted to query for the last property nested in name, you will need to do the following:

"name.last == 'Turing'"

Keys in the query syntax by default must be alphanumeric or include underscore (a-zA-Z0-9). In addition, the key must start with an alpha characters first (a-zA-Z). If your key uses another character, such as a hyphen, you must use a brack syntax. To query for the "street-line" property underneath "work", you will need to do the following:

"work['street-line'] == '678 Johnson Street'"

Equality ==, Inequality

// finds documents which have a title equal to Harry Potter."title == 'Harry Potter'"
// finds documents which that are not of the title Lord of the Rings"title != 'Lord of the Rings'"

Comparisons - Greater Or Less Than >=, >, <, <=

// finds documents where the age is less than or equal to 18"age <= 18"// finds documents where the age is less than to 18"age < 18"// finds documents where the age is greater than or equal to 18"age >= 18"// finds documents where the age is greater than to 18"age > 18"

Compound - And &&, Or ||, Not !, Contains contains

Use && for a logical and Predicate; similar to SQLite's AND

// finds documents that have a theme property equal to "Dark" and a name property equally to "Light""theme == 'Dark' && name == 'Light'"

Use || for a logical or predicate; similar to SQL OR statements

// finds documents that are "Tom" or "Arthur""name == 'Tom' || name == 'Arthur'"

Use ! for a logical not predicate; similar to SQL NOT statements

// finds documents that are neither "Hamilton" nor "Morten""!(name == 'Hamilton' || name == 'Morten')"

Use contains(array, value) to check if an array contains a value.

// finds documents who have a `connectionType` property and checks if it equals any of the values in a defined array"contains(['bluetooth', 'wifidirect'], connectionType)"

String Operations

Use starts_with(property, test) to test if a property with a string value starts with a test string

// finds documents with a title property that begins with "Lord""starts_with(title, 'Lord')"

Use ends_with(property, test) to test if a property with a string value ends with a test string

// finds documents with a title property that ends with "Rings""ends_with(title, 'Rings')"

Use regex(property, test) to see if a property with a string value passes a Regular Expression. Click here for a reference.

// finds documents which has a title property that only comprises of upper and lowercase letters, numbers, and underscores. "regex(title, '^([A-Za-z]|[0-9]|_)+$')"
// A title property of "abc129_24A" will pass// A title property of "abc129_24A  3" will not pass

Dates

Queries also parse ISO-8601 date strings as comparable dates:

"created_at >= '2022-04-29T00:55:31.859Z'"

Booleans

Use when your property is of type Boolean, use value == false or value == true explicitly.

//finds documents which have an 'isDeleted' boolean propety set to `true`"isDeleted == true"

//finds documents which have an 'isDeleted' boolean propety set to `false`"isDeleted == false"