Skip to Navigation
Session: Querying: How to Project Query Results
Instead of pulling full documents in query results you can just grab some pieces of data from documents. You can also transform the projected results. The projections are defined with the usage of:
SelectFields
The most common way to perform a query with projection is to use the selectFields method. You can specify what fields from a document you want to retrieve.
Example I - Projecting Individual Fields of the Document
// request name, city and country for all entities from 'Companies' collection
QueryData queryData = new QueryData(
new String[] { "Name", "Address.city", "Address.country"},
new String[] { "Name", "City", "Country"});
List<NameCityAndCountry> results = session
.query(Company.class)
.selectFields(NameCityAndCountry.class, queryData)
.toList();from Companies
select Name, Address.City as City, Address.Country as CountryExample II - Projecting Arrays and Objects
QueryData queryData = new QueryData(new String[]{ "ShipTo", "Lines[].ProductName" },
new String[]{"ShipTo", "Products"});
List<ShipToAndProducts> results = session.query(Order.class)
.selectFields(ShipToAndProducts.class, queryData)
.toList();from Orders
select ShipTo, Lines[].ProductName as ProductsExample III - Projection with Expression
List<FullName> results = session.advanced().rawQuery(FullName.class, "from Employees as e " +
"select {" +
" FullName : e.FirstName + \" \" + e.LastName " +
"}").toList();from Employees as e
select {
FullName : e.FirstName + " " + e.LastName
}Example IV - Projection with declared function
List<Employee> results = session.advanced().rawQuery(Employee.class, "declare function output(e) { " +
" var format = function(p){ return p.FirstName + \" \" + p.LastName; }; " +
" return { FullName : format(e) }; " +
"} " +
"from Employees as e select output(e)").toList();declare function output(e) {
var format = function(p){ return p.FirstName + " " + p.LastName; };
return { FullName : format(e) };
}
from Employees as e select output(e)Example V - Projection with Calculation
List<Total> results = session.advanced().rawQuery(Total.class, "from Orders as o " +
"select { " +
" Total : o.Lines.reduce( " +
" (acc , l) => acc += l.PricePerUnit * l.Quantity, 0) " +
"}").toList();from Orders as o
select {
Total : o.Lines.reduce(
(acc , l) => acc += l.PricePerUnit * l.Quantity, 0)
}Example VI - Projection Using a Loaded Document
List<OrderProjection> results = session.advanced().rawQuery(OrderProjection.class, "from Orders as o " +
"load o.Company as c " +
"select { " +
" CompanyName: c.Name," +
" ShippedAt: o.ShippedAt" +
"}").toList();from Orders as o
load o.Company as c
select {
CompanyName: c.Name,
ShippedAt: o.ShippedAt
}Example VII - Projection with Dates
List<EmployeeProjection> results = session.advanced().rawQuery(EmployeeProjection.class, "from Employees as e " +
"select { " +
" DayOfBirth : new Date(Date.parse(e.Birthday)).getDate(), " +
" MonthOfBirth : new Date(Date.parse(e.Birthday)).getMonth() + 1, " +
" Age : new Date().getFullYear() - new Date(Date.parse(e.Birthday)).getFullYear() " +
"}").toList();from Employees as e
select {
DayOfBirth : new Date(Date.parse(e.Birthday)).getDate(),
MonthOfBirth : new Date(Date.parse(e.Birthday)).getMonth() + 1,
Age : new Date().getFullYear() - new Date(Date.parse(e.Birthday)).getFullYear()
}Example VIII - Projection with Raw JavaScript Code
List<EmployeeProjection> results = session.advanced().rawQuery(EmployeeProjection.class, "from Employees as e " +
"select { " +
" Date : new Date(Date.parse(e.Birthday)), " +
" Name : e.FirstName.substr(0,3) " +
"}").toList();from Employees as e
select {
Date : new Date(Date.parse(e.Birthday)),
Name : e.FirstName.substr(0,3)
}Example IX - Projection with Metadata
List<Employee> results = session.advanced().rawQuery(Employee.class, "from Employees as e " +
"select {" +
" Name : e.FirstName, " +
" Metadata : getMetadata(e)" +
"}").toList();from Employees as e
select {
Name : e.FirstName,
Metadata : getMetadata(e)
}This method overload retrieves all public fields of the class given in generic and uses them to perform projection to the requested type.
You can use this method instead of using selectFields together with all fields of the projection class.
Example X
List<ContactDetails> results = session.query(Company.class, Companies_ByContact.class)
.selectFields(ContactDetails.class)
.toList();from index 'Companies/ByContact'
select Name, Phoneprivate class Companies_ByContact extends AbstractIndexCreationTask {
public Companies_ByContact() {
map = "from c in docs.Companies select new { Name = c.Contact.Name, Phone = c.Phone } ";
storeAllFields(FieldStorage.YES); // name and phone fields can be retrieved directly from index
}
}public static class ContactDetails {
private String name;
private String phone;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
OfType - simple projection
ofType is a client-side projection. The easiest explanation of how it works is to take the results that the server returns and map them to given type. This may become useful when querying an index that contains fields that are not available in mapped type.
Example
// query index 'Products_BySupplierName'
// return documents from collection 'Products' that have a supplier 'Norske Meierier'
// project them to 'Products'
List<Product> results = session.query(Products_BySupplierName.Result.class, Products_BySupplierName.class)
.whereEquals("Name", "Norske Meierier")
.ofType(Product.class)
.toList();
Note
Projected entities (even named types) are not tracked by the session.
Note
If the projected fields are stored inside the index itself (FieldStorage.YES in the index definition), then the query results will be created directly from there instead of retrieving documents in order to project.
Related Articles