Skip to content

The WHERE clause consists of 1..n filters combined with AND and OR operators.

A filter consists of a filter type, an operator and 0..n filter values.

A filter value in MQL is an arbitrary sequence of characters bounded by single quotes ('). Integer values, Long values, Double values, date values, or keywords, such as ME, TODAY, and YESTERDAY etc. can be used as filter values without the single quotes.

To include a single-quote character within a filter value, write two adjacent single quotes.

Filter Types

The following is a list of supported filter types defined by MQL.

Filter type Description Supported operators Examples
Text [WITHIN (search_properties)] Simple word, quoted phrase or advanced search text. See subsequent section on search text filter. An optional WITHIN search properties clause can be used to specify the search scope. =, = ... WITHIN (...) Search properties valid values: 'Name', 'Physical Name', 'Description' or 'Others' text='+phone +number' WITHIN ('Name', 'Physical Name', 'Description')
Semantic_search_text [WITHIN (search_properties)] Semantic search phrase. Refer to Meta Integration Search Help. An optional WITHIN search properties clause can be used to specify the search scope. =, = ... WITHIN (...) Search properties valid values: 'Name', 'Physical Name', 'Description' or 'Others' semantic_search_text='customer tables'
category Category of the search scope, such as 'Glossary.Term', or 'Database' =, !=, = ANY (...), != ANY (...) category='Glossary.Term'
Profile attribute name A profile attribute name can be qualified by a profile name to prevent ambiguity. =, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS, <, <=, >, >= and BETWEEN "Data Type"='int' OR "Data Modeling (Business)"."Data Type"='STRING'
Custom attribute name Custom attributes of the BLOB data type cannot be used as filter types. =, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS, <, <=, >, >= and BETWEEN "Custom Attribute Int" = ANY (2000, 2001)
Custom relationship name Complex custom relationships or simple custom relationships. =, !=, = ANY (...), != ANY (...), =ALL(…), !=ALL(…), = WHERE (...), != WHERE (...), "Is Defined By" = WHERE(Category='Glossary.Term' AND Name CONTAINS 'Account')
UDP name =, !=, = ANY (...), != ANY (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS "2PC Option_1092616415"='N' AND "Schema prfx_1092616409" EXISTS
System attribute name Not all system attributes can be used as filter types. Depends on the system attribute id='Customer ID'
Data profiling attribute name A data profiling attribute is qualified by "Data Profiling". =, !=, = ANY (...), != ANY (...), [NOT] EXISTS, <, <=, >, >= and BETWEEN etc. "Data Profiling"."Min Length"=2
Source History attribute name A source history attribute is qualified by "Source History". Depends on the attribute "Source History"."Popularity Count" >= 10
Related object type.Property Property can be profile attribute name, custom attribute name, UDP name or system attribute Depends on the property Children."Object Type"='Column'
Function Currently only ExpandedMembersOfRole and count functions are supported. Depends on the function ExpandedMembersOfRole('Workflow Editor')=ANY(ME, 'John'), count('Columns') > 100

Value Types

The following value types are supported:

  • String: quoted by single quote (') characters. Can be used as search property name, category name, model name, and attribute filter value name, etc. For example, text = 'Customer'. If the value may differ by the user's locale, use the value in the English locale always. For example, "Workflow State"='Published'.

  • Number: no need to be quoted. Can be used as profile attribute filter value, custom attribute filter value, data profiling attribute filter value, and last period value etc. For example, Length = 10 AND "Data Profiling".Max = 3.123456E30 AND "AND "Updated Date" = last 10 minutes.

  • Date: no need to but can be quoted. Can be used as profile attribute or custom attribute filter value, and "Updated Date" system attribute filter value etc. Meta Integration supports 'yyyy-MM-dd' as the date format. For example, "Updated Date" BETWEEN 2014-10-26 AND TODAY.

  • Datetime: quoted by single quote (') characters. Can be used as profile attribute or custom attribute filter value, and "Updated Date" system attribute filter value etc. Meta Integration supports yyyy-MM-dd hh:mm:ss as the datetime format. For example, "Final Offer Timestamp" before '2019-07-01 15:00:00'.

Operators

Following is a list of operators defined by MQL:

Operator name Symbol Supported by Filter Types
EQUALS = All
EQUALS WITHIN = WITHIN (search properties) text, semantic_search_text
NOT EQUALS != or <> Model, category, profile attribute, custom attribute, UDP, system attribute, data profiling attribute, source history attribute, related object property, ExpandedMembersOfRole
EQUALS ANY = ANY (v1, v2, …, vn) Model, category, profile attribute, custom attribute, custom relationship, UDP, system attribute, data profiling attribute, source history attribute, related object property, ExpandedMembersOfRole
NOT EQUALS ANY != ANY (v1, v2, …, vn) Model, category, profile attribute, custom attribute, custom relationship, UDP, system attribute, data profiling attribute, source history attribute, related object property, ExpandedMembersOfRole
EQUALS ALL = ALL (v1, v2, …, vn) profile attribute, custom attribute, UDP, system attribute, "Data Profiling"."Inferred Data Types", related object property, ExpandedMembersOfRole
NOT EQUALS ALL != ALL (v1, v2, …, vn) profile attribute, custom attribute, UDP, system attribute, "Data Profiling"."Inferred Data Types", related object property, ExpandedMembersOfRole
EQUALS WHERE =WHERE (…) System attribute Model, "Mentioned Objects" and custom relationship
NOT EQUALS WHERE !=WHERE (…) System attribute Model, "Mentioned Objects" and custom relationship
EXISTS EXISTS profile attribute, custom attribute, custom relationship, UDP, system attribute, data profiling attribute, source history attribute, related object property, ExpandedMembersOfRole
NOT EXISTS NOT EXISTS profile attribute, custom attribute, custom relationship, UDP, system attribute, data profiling attribute, source history attribute, related object property, ExpandedMembersOfRole
STARTS WITH STARTS WITH profile attribute, custom attribute, UDP, system attribute, related object property
NOT STARTS WITH NOT STARTS WITH profile attribute, custom attribute, UDP, system attribute, related object property
ENDS WITH ENDS WITH profile attribute, custom attribute, UDP, system attribute, related object property
NOT ENDS WITH NOT ENDS WITH profile attribute, custom attribute, UDP, system attribute, related object property
CONTAINS CONTAINS profile attribute, custom attribute, UDP, system attribute, related object property
NOT CONTAINS NOT CONTAINS profile attribute, custom attribute, UDP, system attribute, related object property
BEFORE BEFORE (exclusive) "Created Date", "Updated Date", profile attribute, custom attribute, data profiling attribute, source history attribute, related object property with a date or datetime data type
AFTER AFTER (exclusive) "Created Date", "Updated Date", profile attribute, custom attribute, data profiling attribute, source history attribute, related object property with a date or datetime data type
BETWEEN BETWEEN AND (inclusive) "Created Date", "Updated Date", data profiling attribute, source history attribute, profile attribute, custom attribute, system attribute, related object property with a date, datetime or numeric data type
LESS THAN < Data profiling attribute, source history attribute, profiling attribute, custom attribute, system attribute, related object property with a numeric data type
LESS THAN OR EQUAL TO <= Data profiling attribute, source history attribute, profiling attribute, custom attribute, system attribute, related object property with a numeric data type
GREATER THAN > Data profiling attribute, source history attribute, profiling attribute, custom attribute, system attribute, related object property with a numeric data type
GREATER THAN OR EQUAL TO >= Data profiling attribute, source history attribute, profiling attribute, custom attribute, system attribute, related object property with a numeric data type

Note:

When applied to a profile attribute, custom attribute, custom relationship, UDP, system attribute, data profiling attribute, or a related object property, a filter with the =, !=, <, <=, >, >=, = any (...), != any (...), = all (...), != all (...), [not] starts with, [not] ends with, [not] contains, before, after or between operator would only return any results if the profile attribute, custom attribute, custom relationship, UDP, system attribute, data profiling attribute, or related object property is applicable to the objects and the filter condition is satisfied. NOT EXISTS returns objects that the attribute is not applicable to.

In other words, if an object does not have any attribute Physical Name, any "Physical Name" filter with the operator =, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS, BEFORE, AFTER or BETWEEN will never return this object. But NOT EXISTS will return this object.

There is no need to add the wildcard character (*) to the value of the CONTAINS, STARTS WITH and ENDS WITH operators, also known as the LIKE operators. For example, Name CONTAINS 'ssn' means the value of the attribute Name has a substring 'ssn'.

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).

Keywords For Filter Values

User keywords: ME

A user keyword can be used as a value of ExpandedMembersOfRole, any system attribute filters which data type is Grantee or Array of Grantees, custom attribute filter or related object property filter which data type is User or Users.

Date keywords: TODAY, YESTERDAY

A date keyword can be used as a value of any system attribute filter, profile attribute filter, custom attribute filter or related object property filter which data type is Date or DateTime.

Date And Datetime Filter Values

Meta Integration supports the following date or datetime filter values:

  • LAST DAYS | HOURS | MINUTES, where is a positive integer

  • Date constants:

  • Date keywords: TODAY, YESTERDAY

  • Date value in the form of yyyy-MM-dd, such as 2010-10-05

  • Datetime value in the form of yyyy-MM-dd hh:mm:ss, such as '1998-01-01 01:00:00'

All date and datetime values are treated as UTC (Coordinated Universal Time).

LAST DAYS | HOURS | MINUTES can only be used as the filter value of = operator.

All date constants can be used as the filter value of =, BEFORE, AFTER and BETWEEN operators.

Note that the date keywords TODAY and YESTERDAY may have a different meaning used with different operators. For example:

  • "Updated Date" = TODAY means the last modification time is between '00:00:00' and '23:59:59' of the current calendar day.

  • "Updated Date" BEFORE TODAY means the last modification time is before '00:00:00' of the current calendar day.

  • "Updated Date" AFTER YESTERDAY means the last modification time is after '23:59:59' of the last calendar day.

Setting The Precedence of Operators

Filters are applied from left to right. AND is applied before OR.

You can use parentheses in complex MQL WHERE clause to enforce or override the precedence of operators.

For example, to search for cash flow reports or equity reports in the PAYTRANS model, you can use the following MQL WHERE clause: (semantic_search_text = '"cash flow" reports' OR semantic_search_text = 'equity reports') AND Model='PAYTRANS'.

The parentheses are necessary to allow the OR filters to be executed before the AND predicate.

System Attribute Filters Not Working With OR

Some system attribute filters are not evaluated by the search index. They are evaluated after the search. These filters do not work with the OR operator. They must be ANDed with other filters. Performance may suffer if a query contains any of these filters. Make sure you have more restrictive filters, such as a Category filter or Model system attribute filter in the same query.

  • Collections when used as a related object property

  • Context

  • Documentation

  • "Open In Tool"

  • "Has Data Impact"

  • "Has Data Lineage"

  • "Has Semantic Definition"

  • "Has Semantic Usage"

  • "Inferred Documentation"

  • "Mapped Documentation"

  • "My Workflow Tasks"

  • "Object Image"

  • "Sensitivity Label"

  • "Sensitivity Label Lineage Proposed"

  • Used

  • Watchers when used as a related object property

  • "Workflow Assignees"

  • "Workflow Deprecation Requested"

  • "Workflow Published"

These system attributes do not support the OR operator either when they are used as a related object type or related object property.

Search Text Filter

The value of the search text can be a simple word, a quoted phrase or an advanced search text which combines words and phrases.

The advance search text supports any words, exact name, exact phrase, all words, exclude words, wildcard, and parent and child searches. You can append these together into a search text.

The text filter can be followed by an optional WITHIN (search_properties) clause which specifies the search scope, such as 'Name', 'Physical Name', and 'Description' etc. For example, text='Accounts Payable' within ('Name', 'Physical Name'). If the text filter does not have the search properties WITHIN clause the default search scope will be ('Name', 'Physical Name').

Where Clause Examples

  • semantic_search_text='simon''s term'

  • text='customer -payment' within ('Name', 'Physical Name')

  • ("Endorsed By" != me OR "Certified By"='Administrator') AND "Warned By" NOT EXISTS

  • (customer_attr1 NOT EXISTS) AND (custom_attr2 != -200)

  • (("Updated Date" BETWEEN '1990-01-01' AND '1991-01-01') AND ("Updated Date" NOT BETWEEN '1990-10-01' AND '1992-01-02'))

  • ExpandedMembersOfRole('Workflow Editor') = ANY (ME, 'John')

  • "Updated Date"=YESTERDAY OR "Updated Date"=LAST 5 HOURS

  • "Object Type" = 'Database.Table' AND Children.Name CONTAINS 'ssn'