Multi-valued Attribute Filter in Where

Custom attributes, custom relationships, and system attributes can be multi-valued. Related object properties can also be multi-valued if the related object type is multi-valued. We spell out the semantics of the multi-valued attribute filter in the WHERE clause in detail.

There are two types of operators, single valued operators, such as =, !=, Starts With, Not Starts With, Ends With, and Not Ends With, etc. and multi-valued operators, such as = ANY (...), != ANY (...), = ALL (...) and != ALL (...).

For single valued operators, and the operator is not a NOT operator, a multi-valued attribute filter is true if the operator yields true for any value. The result is false if no true result is found (including the case where the attribute value is empty).

For single valued operators, and the operator is a NOT operator, a multi-valued attribute filter is false if the operator yields false for any value. The result is true if all values evaluate to true (including the case where the attribute value is empty).

Single valued operator that is not a NOT operator: =, <, <=, >, >=, STARTS WITH, ENDS WITH, CONTAINS, BEFORE, AFTER, BETWEEN. Even though the operator BETWEEN takes two values we still consider it as a single valued operator in this context.

Single valued operator that is a NOT operator: !=, NOT STARTS WITH, NOT ENDS WITH, NOT CONTAINS.

For example, a file Address.csv has the following data classifications: City, "US Postal Code" and "Address Line". The attribute filter "Data Classifications" = 'City' will find this object. But the attribute filter "Data Classifications" != 'City' will not return this object.

"Children.\"Data Type\"" NOT ENDS WITH 'char'" returns true if there is no children's data type that ends with "char".

For the = ALL (...) operator, the filter returns true if the multi-valued attribute contains all values on the right-hand side. For example, "Children.Name = ALL ('FirstName', 'LastName')" for a table returns true if the table has both FirstName and LastName as its children.

For the =! ALL (...) operator, the filter returns true if the multi-valued attribute does not contain all values on the right-hand side. For example, "Children.Name != ALL ('FirstName', 'LastName')" for a table returns true if the table does not have both FirstName and LastName as its children.

For the = ANY (...) operator, the filter returns true if the multi-valued attribute contains any value on the right-hand side. For example, "Children.Name = ANY ('FirstName', 'LastName')" for a table returns true if the table has FirstName, LastName or both as its children.

For the != ANY (...) operator, the filter returns true if the multi-valued attribute does not contain any value on the right-hand side. For example, "Children.Name != ANY ('FirstName', 'LastName')" for a table returns true if the table has neither FirstName nor LastName as its children.

If a multi-valued attribute is used along with a multi-valued related object type, the value of the property is an array of arrays of strings or objects.

For the = ALL (...) operator, the filter returns true if the combined related object property of all related objects contains all values on the right-hand side.

For the != ALL (...) operator, the filter returns true if the combined related object property of all related objects does not contain all values on the right-hand side.

For the = ANY (...) operator, the filter returns true if the combined related object property of all related objects contains any value on the right-hand side.

For the != ANY (...) operator, the filter returns true if the combined related object property of all related objects does not contain any value on the right-hand side.

For example, if the custom attribute "Business Domain" is a multi-valued Enumeration type custom attribute, which may take one or more of the following values: ["Finance", "CRM", "Personnel", "Payroll", "Sales", "Marketing", "Partnerships", "Investments"], the filter "Children."Business Domain" = ALL ('CRM', 'Payroll')" will return true for a table object if some column A of the table has a custom attribute value "CRM" and some column B of the table has a custom attribute value "Payroll". The column A and column B can be the same column or different columns.

If there is more than one filter specified with the same related object type, those filters will be applied to the same related object.

For example, "Children.\"Object Type\" = 'Database.Column' AND Children.\"Data Type\" starts with 'varchar' AND Children.\"Length\" = 100" returns true if a table has at least one column of the type of varchar(100).