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.

Here’s a simple count query:

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

Output:

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

Query selecting specific attributes and using aliases.

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

Output:

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

Query using a filter condition.

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

Output:

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

Query with a limit statement.

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

Output:

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

Query with an order statement.

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

Output:

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

Query with a union statement.

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

Output:

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

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

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

Example of grouping by a specific dataset attribute.

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

Output:

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