CRM Analytics Apex QueryBuilder Examples

Build simple or complex SAQL queries using QueryBuilder.

QueryBuilder is the core of the CRM Analytics Apex feature set, so let’s take a closer look.

1. Here’s a simple count query:

Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.count().alias('c')};
String query = Wave.QueryBuilder.load('datasetId', 'datasetVersionId').group().foreach(projs).build('q');

Output:

q = load "datasetId/datasetVersionId";
q = group q by all;
q = foreach q generate count as c;

2. Query selecting specific attributes and using aliases.

Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
String query = Wave.QueryBuilder.load('datasetId','datasetVersionId').foreach(projs).build('q');

Output:

q = load "datasetId/datasetVersionId";
q = foreach q generate Name,AnnualRevenue as Revenue;

3. Query using a filter condition.

Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
String query = Wave.QueryBuilder.load('datasetId','datasetVersionId').foreach(projs).filter('Name != \'My Name\'').build('q');

Output:

q = load "datasetId/datasetVersionId";
q = foreach q generate Name,AnnualRevenue as Revenue;
q = filter q by Name != 'My Name';

4. Query with a limit statement.

Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
String query = Wave.QueryBuilder.load('datasetId','datasetVersionId').foreach(projs).cap(10).build('q');

Output:

q = load "datasetId/datasetVersionId";
q = foreach q generate Name,AnnualRevenue as Revenue;
q = limit q 10;

5. Query with an order statement.

Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
List<List<String>> orders = new List<List<String>>{new List<String>{'Name', 'asc'}, new List<String>{'Revenue', 'desc'}};
String query = Wave.QueryBuilder.load('datasetId','datasetVersionId').foreach(projs).order(orders).cap(10).build('q');

Output:

q = load "datasetId/datasetVersionId";
q = foreach q generate Name,AnnualRevenue as Revenue;
q = order q by (Name asc, Revenue desc);
q = limit q 10;

6. Query with a union statement.

Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('AnnualRevenue').alias('Revenue')};
Wave.QueryNode nodeOne = Wave.QueryBuilder.load('dataseOne','datasetVersionOne').foreach(projs);
Wave.QueryNode nodeTwo = Wave.QueryBuilder.load('datasetTwo', 'datasetVersionTwo').foreach(projs);
String query = Wave.QueryBuilder.union(new List<Wave.QueryNode>{nodeOne, nodeTwo}).build('q');

Output:

qa = load "datasetOne/datasetVersionOne";
qa = foreach q generate Name,AnnualRevenue as Revenue;
qb = load "datasetTwo/datasetVersionTwo";
qb = foreach q generate Name,AnnualRevenue as Revenue;
q = union qa, qb;

7. Executing the query to get the result set via Query Builder.

Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.count().alias('c')};
ConnectApi.LiteralJson result = Wave.QueryBuilder.load('datasetId', 'datasetVersionId').group().foreach(projs).execute('q');

8. Example of grouping by a specific dataset attribute.

Wave.ProjectionNode[] projs = new Wave.ProjectionNode[]{Wave.QueryBuilder.get('Name'), Wave.QueryBuilder.get('Revenue').sum().alias('REVENUE_SUM')};
ConnectApi.LiteralJson result = Wave.QueryBuilder.load('datasetId', 'datasetVersionId').group(new String[]{'Name'}).foreach(projs).build('q');

Output:

q = load "datasetId/datasetVersionId";
q = group q by (Name);
q = foreach q generate Name,sum(Revenue) as REVENUE_SUM;