ObjectBox Queries | ObjectBox Docs (original) (raw)

ObjectBox Queries

Discover how to use the Query API to create queries with ObjectBox DB. By utilizing these queries, you can retrieve stored objects that meet user-defined criteria.

Use box.query(condition) and supply a condition on one or more properties to start building a query.

Create a condition by accessing a property via the underscore class of the entity, e.g. User_.firstName.equal("Joe").

Use build() to create a re-usable query instance. To then retrieve all results use find() on the query. More options on retrieving results are discussed later in Run a query.

Once done, close() the query to clean up resources.

Here is a full example to query for all users with the first name “Joe”:

Query<User> query = userBox.query(User_.firstName.equal("Joe")).build();
List<User> joes = query.find();
query.close();

To combine multiple conditions use and(condition) and or(condition). This implicitly adds parentheses around the combined conditions, e.g. cond1.and(cond2) is logically equivalent to (cond1 AND cond2).

For example to get users with the first name “Joe” that are born later than 1970 and whose last name starts with “O”:

Query<User> query = userBox.query(
        User_.firstName.equal("Joe")
                .and(User_.yearOfBirth.greater(1970))
                .and(User_.lastName.startsWith("O")))
        .build();
List<User> youngJoes = query.find();
query.close();

To nest conditions pass a combined condition to and() or or():

// equal AND (less OR oneOf)
Query<User> query = box.query(
        User_.firstName.equal("Joe")
                .and(User_.age.less(12)
                        .or(User_.stamp.oneOf(new long[]{1012}))))
        .order(User_.age)
        .build();

Apart from the standard conditions like equal(), notEqual(), greater() and less() there are also additional conditions available:

See the API for a full list:

Dart only: DateTime caveat

For Dart DateTime is stored as time in milliseconds internally in the database (or nanoseconds for @Property(type: PropertyType.dateNano)).

To avoid having to manually convert to int when creating a query condition, extra methods that accept DateTime exist.

For example, to query an Order entity with a date field to find all orders in 2023:

final query = box
    .query(Order_.date.betweenDate(DateTime.utc(2023),
        DateTime.utc(2024).subtract(Duration(milliseconds: 1))))
    .build();

Nearest neighbor vector search

A special condition is available for vector properties with an HNSW index. See the dedicated page for details:

In addition to specifying conditions, you can order the returned results using the order() method. By default this sorts ASCII characters in alphabetical order while ignoring case and numbers in ascending order.

Query<User> query = userBox
    .query(User_.firstName.equal("Joe"))
    .order(User_.lastName) // in ascending order, ignoring case
    .build();

You can also pass flags to order() to sort in descending order, to sort case sensitive or to specially treat null values. For example to sort the above results in descending order and case sensitive instead:

.order(User_.lastName, QueryBuilder.DESCENDING | QueryBuilder.CASE_SENSITIVE)

Query<User> query = builder.build();

Once the query is created, it allows various operations, which we will explore in the following sub sections.

There are a couple of find methods to retrieve objects matching the query:

// return all entities matching the query
List<User> joes = query.find();

// return only the first result or null if none
User joe = query.findFirst();

// return the only result or null if none, throw if more than one result
User joe = query.findUnique();

To return all entities matching the query simply call find().

To only return the first result, use findFirst().

If you expect a unique result, call findUnique() instead. It will give you a single result or null, if no matching entity was found and throw an exception if there was more than one result.

To remove all objects matching a query, call query.remove() .

Reuse Queries and Parameters

If you frequently run the same query you should cache the Query object and re-use it. To make a Query more reusable you can change the values, or query parameters, of each condition you added even after the Query is built. Let's see how.

Query is not thread safe. To use a query in a different thread, either build a new query or synchronize access to it. Alternatively, in Java use query.copy() or a QueryThreadLocal to obtain an instance for each thread.

Assume we want to find a list of User with specific firstName values. First, we build a regular Query with an equal() condition for firstName. Because we have to pass an initial parameter value to equal() but plan to override it before running the Query later, we just pass an empty string:

// build a query
Query<User> query = userBox.query(User_.firstName.equal("")).build();

Now at some later point, we want to run the Query with an actual value for the equals condition onfirstName :

// Change firstName parameter to "Joe" and get results
List<User> joes = query.setParameter(User_.firstName, "Joe").find();

// Change firstName parameter to "Jake" and get results
List<User> jakes = query.setParameter(User_.firstName, "Jake").find();

You might already be wondering what happens if you have more than one condition using firstName? For this purpose you can assign each parameter an alias while specifying the condition:

// assign alias "name" to the equal query parameter
Query<User> query = userBox
    .query(User_.firstName.equal("").alias("name"));

Then, when setting a new parameter value pass the alias instead of the property:

// Change parameter with alias "name" to "Joe", get results
List<User> joes = query.setParameter("name", "Joe").find();

Limit, Offset, and Pagination

Sometimes you only need a subset of a query, for example, the first 10 elements to display in your user interface. This is especially helpful (and resource-efficient) when you have a high number of entities and you cannot limit the result using query conditions only. 

// offset by 10, limit to at most 5 results
List<User> joes = query.find(10, 5);

offset: The first offset results are skipped.

limit: At most limit results are returned.

Instead of reading the whole result (list of objects) using find() you can stream it using stream() :

Query<User> query = userBox.query().build();
Stream<User stream = query.stream();
await stream.forEach((User user) => print(user));
query.close();

Observe or listen to changes

To learn how to observe or listen to changes to the results of a query, see the data observers page:

For example, instead of getting all Users, to just get their email addresses:

String[] emails = userBox.query().build()
    .property(User_.email)
    .findStrings();
    
// or use .findString() to return just the first result

Note: the returned array of property values is not in any particular order, even if you did specify an order when building the query.

By default, null values are not returned. However, you can specify a replacement value to return if a property is null:

// includes 'unknown' for each null email
String[] emails = userBox.query().build()
    .property(User_.email)
    .nullValue("unknown")
    .findStrings();

Distinct and unique results

The property query can also only return distinct values:

PropertyQuery pq = userBox.query().build().property(User_.firstName);

// returns ['joe'] because by default, the case of strings is ignored.
String[] names = pq.distinct().findStrings();

// returns ['Joe', 'joe', 'JOE']
String[] names = pq.distinct(StringOrder.CASE_SENSITIVE).findStrings();

// the query can be configured to throw there is more than one value
String[] names = pq.unique().findStrings();

Assume there is a Person that can be associated with multiple Address entities:

@Entity
public class Person {
    @Id long id;
    String name;
    ToMany<Address> addresses;
}

@Entity
public class Address {
    @Id long id;
    String street;
    String zip;
}

To get a Person with a certain name that also lives on a specific street, we need to query the associated Address entities of a Person. To do this, use the link() method of the query builder to tell that the addresses relation should be queried. Then add a condition for Address:

// get all Person objects named "Elmo"...
QueryBuilder<Person> builder = personBox
    .query(Person_.name.equal("Elmo"));
// ...which have an address on "Sesame Street"
builder.link(Person_.addresses)
    .apply(Address_.street.equal("Sesame Street"));
List<Person> elmosOnSesameStreet = builder.build().find();

What if we want to get a list of Address instead of Person? If you know ObjectBox relations well, you would probably add a @Backlink relation to Address and build your query using it with link() as shown above:

@Entity
public class Address {
    // ...
    @Backlink(to = "addresses")
    ToMany<Person> persons;
}

// get all Address objects with street "Sesame Street"...
QueryBuilder<Address> builder = addressBox
    .query(Address_.street.equal("Sesame Street"));
// ...which are linked from a Person named "Elmo"
builder.link(Address_.persons)
    .apply(Person_.name.equal("Elmo"));
List<Address> sesameStreetsWithElmo = builder.build().find();

But actually, you do not have to modify the Address entity (you still can if you need the @Backlink elsewhere). Instead, we can use the backlink() method to create a backlink to the addresses relation from Person just for that query:

// get all Address objects with street "Sesame Street"...
QueryBuilder<Address> builder = addressBox
    .query(Address_.street.equal("Sesame Street"));
// ...which are linked from a Person named "Elmo"
builder.backlink(Person_.addresses)
    .apply(Person_.name.equal("Elmo"));
List<Address> sesameStreetsWithElmo = builder.build().find();

List<Customer> customers = customerBox.query().build().find();
// Customer has a ToMany called orders.
// First access: this will cause a database lookup.
Order order = customers.get(0).orders.get(0);
List<Customer> customers = customerBox.query()
    .eager(Customer_.orders) // Customer has a ToMany called orders.
    .build()
    .find();
// First access: this will cause a database lookup.
Order order = customers.get(0).orders.get(0);

Eager loading only works one level deep. If you have nested relations and you want to prefetch relations of all children, you can instead add a query filter as described below. Use it to simply access all relation properties, which triggers them to lookup there values as described above.

Query filters come into play when you are looking for objects that need to match complex conditions, which cannot be fully expressed with the QueryBuilder class. Filters are written in Java and thus can express any complexity. Needless to say, that database conditions can be matched more efficiently than Java-based filters. Thus you will get the best results when you use both together:

  1. Narrow down results using standard database conditions to a reasonable number (use QueryBuilder to get “candidates”)

A QueryFilter implementation looks at one candidate object at a time and returns true if the candidate is a result or false if not.

Example:

// Reduce object count to reasonable value.
songBox.query(Song_.bandId.equal(bandId))
        // Filter is performed on candidate objects.
        .filter((song) -> song.starCount * 2 > song.downloads);

Notes on performance: 1) ObjectBox creates objects very fast. 2) The virtual machine is tuned to garbage collect short-lived objects. Notes 1) and 2) combined makes a case for filtering because ObjectBox creates candidate objects of which some are not used and thus get garbage collected quickly after their creation.

Query filters and ToMany relation

The ToMany class offers additional methods that can be convenient in query filters:

To see what query is actually executed by ObjectBox:

// Set the LOG_QUERY_PARAMETERS debug flag
BoxStore store = MyObjectBox.builder()
    .debugFlags(DebugFlags.LOG_QUERY_PARAMETERS)
    .build();
    
// Execute a query
query.find();

Then in your console (or logcat on Android) you will see log output like:

Parameters for query #2:
(firstName ==(i) "Joe"
 AND age < 12)

Last updated 5 months ago

Order directives can also be chained. Check the method documentation () for details.

are first created (and not yet executed) by calling build() on the QueryBuilder.

To avoid loading query results right away, Query offers findLazy() and findLazyCached() which return a of the query results.

is a thread-safe, unmodifiable list that reads entities lazily only once they are accessed. Depending on the find method called, the lazy list will be cached or not. Cached lazy lists store the previously accessed objects to avoid loading entities more than once. Some features of the list are limited to cached lists (e.g. features that require the entire list). See the for more details.

If you only want to return the values of a particular property and not a list of full objects you can use a . After building a query, simply call property(Property) to define the property followed by the appropriate find method.

Property queries ( and ) also offer aggregate functions to directly calculate the minimum, maximum, average, sum and count of all found values:

After creating a relation between entities, you might want to add a query condition for a property that only exists in the related entity. In SQL this is solved using JOINs. But as ObjectBox is not a SQL database we built something very similar: links. Links are based on - see the doc page for the introduction.

By default are loaded lazily: when you first access a ToOne or ToMany property it will perform a database lookup to get its data. On each subsequent access it will use a cached version of that data.

While this initial lookup is fast, you might want to prefetch ToOne or ToMany values before the query results are returned. To do this call the method when building your query and pass the RelationInfo objects associated with the ToOne and ToMany properties to prefetch:

Only Java/Kotlin. For Dart, use the built-in method.

Now filter those candidates using the Java interface to identify final results