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:
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.
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
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()?;
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
.
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"