SET_UNIONS function

The SET_UNIONS aggregation function takes a multi-assign attribute and constructs a set that is the union of all of the values from that attribute.

The syntax of the SET_UNIONS function is:

SET_UNIONS(<multi-assign_attribute>)

where the data type of the attribute must be a set data type (such as mdex:string-set for a multi-assign string attribute).

This function can be used only in SELECT clauses.

SET_UNIONS example

In this example, Body is a multi-assign string attribute:

RETURN results AS
SELECT SET_UNIONS(Body) AS bodyUnion
FROM WineState
GROUP BY WineType
ORDER BY WineType

The result of this statement might be:

WineType        bodyUnion
-------------------------------------------
| Bordeaux   | { Silky, Tannins }         |
| Brut       | { Robust }                 |
| Chardonnay | { }                        |
| Merlot     | { Fruity }                 |
| Pinot Noir | { Supple }                 |
| Red        | { Robust, Silky, Tannins } |
| White      | { Firm, Fresh, Robust }    |
| Zinfandel  | { Robust, Tannins }        |
-------------------------------------------

The sets are derived as follows:

WineType	bodyUnion
Bordeaux	Assigned on three records, with each record having two Body assignments of"Silky" and "Tannins". Therefore, the union returns a two-element set of the two assignments.
Brut	Assigned on two records, with each record having one Body assignment of "Robust". Therefore, the union returns a one-element set with "Robust".
Chardonnay	Assigned on two records, but neither record has a Body assignment. Therefore, the union is empty.
Merlot	Assigned on two records, with one record having one Body assignment of "Fruity" and the other record having no Body assignment. Therefore, there is a union of the single assignment on the one record.
Pinot Noir	Assigned on only one record, which has one Body assignment of "Supple". Therefore, there is a union on that record.
Red	Assigned on eight records, with six records having two Body assignments of "Silky" and "Tannins", one record with two Body assignments of "Robust" and "Tannins", and the eighth record with one Body assignment of "Robust". Therefore, the resulting union produces a three-element set of the three distinct assignments.
White	Assigned on four records, with the first record having two Body assignments of "Fresh" and "Robust", the second record with two Body assignments of "Firm" and "Robust", and the third and fourth records with no Body assignments. Therefore, there is a union of the "Firm", "Fresh", and "Robust" assignments.
Zinfandel	Assigned on only one record with two Body assignments of "Robust" and "Tannins". Therefore, there is a union on that record.