You are currently browsing legacy 4.1 version of documentation. Click here to switch to the newest 4.2 version.

We can help you with migration to the latest RavenDB

Contact Us Now
see on GitHub

Querying: Filtering

One of the most basic functionalities of querying is the ability to filter out data and return records that match a given condition. There are couple of ways to do this.

The following examples demonstrate how to add simple conditions to a query using all of those methods.

Where

List<Employee> results = session
    .query(Employee.class, Employees_ByFirstAndLastName.class) // query 'Employees/ByFirstAndLastName' index
    .whereEquals("FirstName", "Robert") // filtering predicates
    .andAlso()   // by default OR is between each condition
    .whereEquals("LastName", "King") // materialize query by sending it to server for processing
    .toList();
public class Employees_ByFirstAndLastName extends AbstractIndexCreationTask {
    public Employees_ByFirstAndLastName() {
        map = "docs.Employees.Select(employee => new {" +
            "    FirstName = employee.FirstName," +
            "    LastName = employee.LastName" +
            "})";
    }
}
from index 'Employees/ByFirstAndLastName'
where FirstName = 'Robert' and LastName = 'King'

Where - Numeric Property

List<Product> results = session
    .query(Product.class, Products_ByUnitsInStock.class) // query 'Products/ByUnitsInStock' index
    .whereGreaterThan("UnitsInStock", 50) // filtering predicates
    .toList(); // materialize query by sending it to server for processing
public class Products_ByUnitsInStock extends AbstractIndexCreationTask {
    public Products_ByUnitsInStock() {
        map = "docs.Products.Select(product => new {" +
            "        UnitsInStock = product.UnitsInStock" +
            "    })";
    }
}
from index 'Products/ByUnitsInStock'
where UnitsInStock > 50

Where - Nested Property

// return all orders that were shipped to 'Albuquerque'
List<Order> results = session
    .query(Order.class)
    .whereEquals("ShipTo_city", "Albuquerque")
    .toList();
from Orders
where ShipTo.City = 'Albuquerque'

List<Order> results = session
    .query(Order.class, Order_ByOrderLinesCount.class) // query 'Order/ByOrderLinesCount' index
    .whereGreaterThan("Lines_count", 50) // filtering predicates
    .toList();   // materialize query by sending it to server for processing
public class Order_ByOrderLinesCount extends AbstractIndexCreationTask {
    public Order_ByOrderLinesCount() {
        map = "docs.Orders.Select(order => new {" +
            "    Lines_count = order.Lines.Count" +
            "})";
    }
}
from index 'Order/ByOrderLinesCount'
where Lines.Count > 50

Where + Any

Any is useful when you have a collection of items (e.g. Order contains OrderLines) and you want to filter out based on values from this collection. For example, let's retrieve all orders that contain an OrderLine with a given product.

session
    .query(Order.class, Order_ByOrderLines_ProductName.class) // query 'Order/ByOrderLines/ProductName' index
    .whereEquals("Lines_productName", "Teatime Chocolate Biscuits") // filtering predicates
    .toList(); // materialize query by sending it to server for processing
public class Order_ByOrderLines_ProductName extends AbstractIndexCreationTask {
    public Order_ByOrderLines_ProductName() {
        map = "docs.Orders.Select(order => new {" +
            "    Lines_productName = order.Lines.Select(x => x.ProductName)" +
            "})";
    }
}
from index 'Order/ByOrderLinesCount'
where Lines_ProductName = 'Teatime Chocolate Biscuits'

Where + In

When you want to check a single value against multiple values, the In operator can become handy. To retrieve all employees where FirstName is either Robert or Nancy, we can issue the following query:

List<Employee> results = session
    .query(Employee.class, Employees_ByFirstAndLastName.class) // query 'Employees/ByFirstAndLastName' index
    .whereIn("FirstName", Arrays.asList("Robert", "Nancy")) // filtering predicates
    .toList();// materialize query by sending it to server for processing
public class Employees_ByFirstAndLastName extends AbstractIndexCreationTask {
    public Employees_ByFirstAndLastName() {
        map = "docs.Employees.Select(employee => new {" +
            "    FirstName = employee.FirstName," +
            "    LastName = employee.LastName" +
            "})";
    }
}
from index 'Employees/ByFirstAndLastName'
where FirstName IN ('Robert', 'Nancy')

Where + ContainsAny

To check if enumeration contains any of the values from a specified collection, you can use the containsAny method.

Let's assume that we want to return all BlogPosts that contain any of the specified tags.

List<BlogPost> results = session
    .query(BlogPost.class, BlogPosts_ByTags.class)  // query 'BlogPosts/ByTags' index
    .containsAny("tags", Arrays.asList("Development", "Research")) // filtering predicates
    .toList(); // materialize query by sending it to server for processing
public class BlogPosts_ByTags extends AbstractIndexCreationTask {
    public BlogPosts_ByTags() {
        map = "docs.BlogPosts.Select(post => new {" +
            "    tags = post.tags" +
            "})";
    }
}
from index 'BlogPosts/ByTags'
where Tags IN ('Development', 'Research')

Where + ContainsAll

To check if an enumeration contains all of the values from a specified collection, you can use the containsAll method.

Let's assume that we want to return all the BlogPosts that contain all of the specified tags.

List<BlogPost> results = session
    .query(BlogPost.class, BlogPosts_ByTags.class) // query 'BlogPosts/ByTags' index
    .containsAll("tags", Arrays.asList("Development", "Research")) // filtering predicates
    .toList(); // materialize query by sending it to server for processing
public class BlogPosts_ByTags extends AbstractIndexCreationTask {
    public BlogPosts_ByTags() {
        map = "docs.BlogPosts.Select(post => new {" +
            "    tags = post.tags" +
            "})";
    }
}
from index 'BlogPosts/ByTags'
where Tags ALL IN ('Development', 'Research')

Where - StartsWith

// return all products which name starts with 'ch'
List<Product> results = session
    .query(Product.class)
    .whereStartsWith("Name", "ch")
    .toList();
from Products 
where startsWith(Name, 'ch')

Where - EndsWith

// return all products which name ends with 'ra'
List<Product> results = session
    .query(Product.class)
    .whereEndsWith("Name", "ra")
    .toList();
from Products 
where endsWith(Name, 'ra')

Remarks

Information

Underneath, Query is converting predicates to the IndexQuery class so they can issue a query from a low-level operation method.