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 Country

Example 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 Products

Example 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();
from index 'Companies/ByContact' 
select Name, Phone
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
    }
}

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.