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, which let's you 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
const queryData = new QueryData(
[ "Name", "Address.City", "Address.Country"],
[ "Name", "City", "Country"]);
const results = await session
.query({ collection: "Companies" })
.selectFields(queryData)
.all();from Companies
select Name, Address.City as City, Address.Country as CountryExample II - Projecting Arrays and Objects
const queryData = new QueryData(
[ "ShipTo", "Lines[].ProductName" ],
[ "ShipTo", "Products" ]);
const results = await session.query({ collection: "Orders" })
.selectFields(queryData)
.all();from Orders
select ShipTo, Lines[].ProductName as ProductsExample III - Projection with Expression
const results = await session.advanced
.rawQuery(
`from Employees as e select {
FullName: e.FirstName + " " + e.LastName
}`)
.all();from Employees as e
select {
fullName : e.FirstName + " " + e.LastName
}Example IV - Projection with declared function
const results = await session.advanced
.rawQuery(
`declare function output(e) {
var format = function(p){ return p.FirstName + " " + p.LastName; };
return { FullName : format(e) };
}
from Employees as e select output(e)`)
.all();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
const results = await session.advanced
.rawQuery(`from Orders as o select {
Total : o.Lines.reduce(
(acc , l) =>
acc += l.PricePerUnit * l.Quantity, 0)
}`)
.all();from Orders as o
select {
Total : o.Lines.reduce(
(acc , l) => acc += l.PricePerUnit * l.Quantity, 0)
}Example VI - Projection Using a Loaded Document
const results = await session.advanced
.rawQuery(`from Orders as o
load o.Company as c
select {
CompanyName: c.Name,
ShippedAt: o.ShippedAt
}`)
.all();from Orders as o
load o.Company as c
select {
CompanyName: c.Name,
ShippedAt: o.ShippedAt
}Example VII - Projection with Dates
const results = await session.advanced
.rawQuery(
`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()
}`)
.all();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
const results = session.advanced
.rawQuery(
`from Employees as e
select {
Date : new Date(Date.parse(e.Birthday)),
Name : e.FirstName.substr(0,3)
}`)
.all();from Employees as e
select {
Date : new Date(Date.parse(e.Birthday)),
Name : e.FirstName.substr(0,3)
}Example IX - Projection with Metadata
const results = await session.advanced
.rawQuery(
`from Employees as e
select {
Name : e.FirstName,
Metadata : getMetadata(e)
}`)
.all();from Employees as e
select {
Name : e.FirstName,
Metadata : getMetadata(e)
}Example X
const results = await session.query({ indexName: "Companies/ByContact" })
.selectFields([ "Name", "Phone" ])
.all();class Companies_ByContact extends AbstractIndexCreationTask {
constructor() {
super();
this.map = `from c in docs.Companies
select new {
Name = c.Contact.Name,
Phone = c.Phone
}`;
this.storeAllFields("Yes"); // name and phone fields can be retrieved directly from index
}
}from index 'Companies/ByContact'
select Name, Phone
OfType
ofType() is a client-side projection - in JS it only sets the type of the result entries. The easiest explanation of how it works is to take the results that the server returns and assign them to instance of the type indicated by the parameter.
Example
// query index 'Products_BySupplierName'
// return documents from collection 'Products' that have a supplier 'Norske Meierier'
// project them to instance of Product
const results = await session.query({ indexName: "Products/BySupplierName" })
.whereEquals("Name", "Norske Meierier")
.ofType(Product)
.all();
assert.ok(results[0] instanceof Product);
Note
Projected entities (even named types) are not tracked by the session.
Note
If the projected fields are stored inside the index itself ("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