SETS Function

The SETS function allows aggregate query groups to be created from arbitrary query expressions. The general syntax is:

SETS(<query 1> [AS <name>], <query 2> [AS <name>], ..., <query n> [AS <name>]) [AS <name>])

Where each <query> is a DQL query expression that selects a set of objects from the perspective table. The queries can overlap such that multiple expressions can select the same objects. For example, the following query selects messages with overlapping Size values:

GET /Email/Message/_aggregate?range=0&m=COUNT(*)
&f=SETS(Size < 1000, Size > 100 AND Size < 100000, Size > 10000)

This returns a result such as the following:

<results>

<aggregate metric="COUNT(*)" query="*" group="SETS(Size < 1000, Size > 100 AND Size < 100000, Size > 10000)"/>

<totalobjects>6030</totalobjects>

<summary>6030</summary>

<totalgroups>3</totalgroups>

<groups>

<group>

<metric>125</metric>

<field name="SETS(Size < 1000, Size > 100 AND Size < 100000, Size > 10000)">Size IN {null - 1000}</field>

</group>

<group>

<metric>5925</metric>

<field name="SETS(Size < 1000, Size > 100 AND Size < 100000, Size > 10000)">(Size IN {100 - null}) AND (Size IN {null - 100000})</field>

</group>

<group>

<metric>1254</metric>

<field name="SETS(Size < 1000, Size > 100 AND Size < 100000, Size > 10000)">Size IN {10000 - null}</field>

</group>

</groups>

</results>

By default, each group uses the corresponding query expression in both the field name and value. The query expression text may not match the original query text exactly since expressions may be transformed internally. A custom field name can be used by using the AS syntax after the SETS function. Example:

GET /Email/Message/_aggregate?range=0&m=COUNT(*)
&f=SETS(Size < 1000, Size > 100 AND Size < 100000, Size > 10000) AS Sizes

This creates the simpler output format:

<results>

<aggregate metric="COUNT(*)" query="*" group="SETS(Size < 1000, Size > 100 AND Size < 100000, Size > 10000) AS Sizes"/>

<totalobjects>6030</totalobjects>

<summary>6030</summary>

<totalgroups>3</totalgroups>

<groups>

<group>

<metric>125</metric>

<field name="Sizes">Size IN {null - 1000}</field>

</group>

<group>

<metric>5925</metric>

<field name="Sizes">(Size IN {100 - null}) AND (Size IN {null - 100000})</field>

</group>

<group>

<metric>1254</metric>

<field name="Sizes">Size IN {10000 - null}</field>

</group>

</groups>

</results>

The field values can also be customized by using the AS syntax after each query expression. For example:

GET /Email/Message/_aggregate?range=0&m=COUNT(*)
&f=SETS(Size < 1000 AS Littles, Size > 100 AND Size < 100000 AS Middles,
Size > 10000 AS Biggies) AS Sizes

This creates an even simpler format:

<results>

<aggregate metric="COUNT(*)" query="*" group="SETS(Size < 1000 AS Littles, Size > 100 AND Size < 100000 AS Middles, Size > 10000 AS Biggies) AS Sizes"/>

<totalobjects>6030</totalobjects>

<summary>6030</summary>

<totalgroups>3</totalgroups>

<groups>

<group>

<metric>125</metric>

<field name="Sizes">Littles</field>

</group>

<group>

<metric>5925</metric>

<field name="Sizes">Middles</field>

</group>

<group>

<metric>1254</metric>

<field name="Sizes">Biggies</field>

</group>

</groups>

</results>

Note that if objects are selected by the aggregate query but not included in any query expressions defined by the SETS function, those objects are automatically included in a null set. For example:

GET /Email/Message/_aggregate? range=0&m=COUNT(*)
&f=SETS(Size < 10 AS BittyEmails, Size > 100000 AS MegaEmails) AS Sizes

Messages that don’t fit either of the two query expressions are included in a group named (null) as shown below:

<results>

<aggregate metric="COUNT(*)" query="*" group="SETS(Size < 10 AS BittyEmails, Size > 100000 AS MegaEmails) AS Sizes"/>

<totalobjects>6030</totalobjects>

<summary>6030</summary>

<totalgroups>3</totalgroups>

<groups>

<group>

<metric>5925</metric>

<field name="Sizes">(null)</field>

</group>

<group>

<metric>0</metric>

<field name="Sizes">BittyEmails</field>

</group>

<group>

<metric>105</metric>

<field name="Sizes">MegaEmails</field>

</group>

</groups>

</results>

As shown, the majority of messages (5925) did not match either query expression and were therefore included in the (null) group. No messages matches the BittyEmails query expression, hence the metric value for that group is 0.

Note that the query expressions do not need to use the same fields. Each query expression can use any criteria to create its groups. Example:

GET /Email/Message/_aggregate?m=COUNT(*)&range=0
&f=SETS(Size < 1000, Tags=Customer, Sender.Person.Department:support)