Metadata Management (MM)
Metadata Query Language (MQL) Help

Table Of Contents

  1. Overview
  2. Copyright Notice
  3. Release Changes
    1. New Or Improved Features
    2. Deprecated Features
    3. Removed Features
  4. MQL General Rules
  5. Attributes
    1. System Attributes
      1. Object-valued System Attributes
    2. Related Object Type, Related Object Property And Related Object Relationship
      1. Multi-valued Related Object Types
    3. Attribute Resolution
  6. Functions
    1. ExpandedMembersOfRole
    2. MembersOfRole
  7. Select
  8. From
    1. From ID
    2. Scope Filters
  9. Context
  10. Where
    1. Filter Types
    2. Value Types
    3. Operators
    4. Multi-valued Related Object Type In Where
    5. Keywords For Filter Values
    6. Date And Datetime Filter Values
    7. Setting The Precedence Of Operators
    8. System Attribute Filters Not Working With OR
    9. Search Text Filter
    10. Where Clause Examples
  11. Order By
  12. Restricted Words and Characters
    1. Reserved Characters
    2. Reserved Words
  13. Case Sensitivity
  14. Unknown/Inapplicable Attributes And Unknown Filter Values
  15. Limitations

1. Overview

This document describes the Metadata Management (MM) Metadata Query Language (MQL).

2. Copyright Notice

Copyright © Meta Integration Technology, Inc. 1997-2021.
All Rights Reserved.

Meta Integration® is a registered trademark of Meta Integration Technology, Inc.
Other product and company names (or logos) mentioned herein may be the trademarks of their respective owners.
http://www.metaintegration.com

3. Release Changes

3.1 New Or Improved Features

  1. Profile relationships and related object relationships can be used in the SELECT list.
  2. Scope filters in the FROM clause can be used to reduce the scope of a MQL query.
  3. New system attributes are added: "Data Profiling", "Data Sampling", "Data Classifications", "Data Classifications Matched", "Data Classifications Approved", "Data Classifications Rejected", "Workflow State" and "Workflow Published".
  4. System attribute "Workflow Actions" is supported on all business model objects which have workflow enabled.
  5. Object-valued system attributes can be used in the FROM or WHERE clause with the EXISTS or NOT EXISTS operator.
  6. Attribute notation marks #..#, [...], {...}, and <...> are no longer necessary. You can use the attribute name as is.
  7. A new ExpandedMembersOfRole function can be used to filter users and groups of the object roles.
  8. A new MembersOfRole function can be used to select users and groups of the object roles.

3.2 Deprecated Features

Deprecated features do not require any action before upgrading to MM 11.0. They will continue to work as normal in MM 11.0. We plan to remove them in MM 12.0.

Deprecation of features

Replacements

More information

Attribute notation marks

Improve the usability. Removal is considered for MM 12.0

"Semantic Types" system attribute

"Data Classifications" system attribute

Rebranding. Removal is considered for MM 12.0

Term system attribute

"Term Classification" system attribute

Rebranding. Removal is considered for MM 12.0

3.3 Removed Features

Please ensure your application does not use any of these features before upgrading to MM 11.0.

Removal of features

Replacements

More information

steward (or Stewards system attribute) filter

ExpandedMembersOfRole function filter

Steward is replaced by object roles

Stewards system attribute in SELECT list

MembersOfRole function

Steward is replaced by object roles

object_type filter

"Object Type" filter

Deprecated with MM 10.1.0 to reduce the number of reserved words

last_modified filter

"Last Modified Date" filter

Deprecated with MM 10.1.0 to reduce the number of reserved words

label filter

Labels filter

Deprecated with MM 10.1.0 to reduce the number of reserved words

semantic_type filter

"Data Classifications" filter

Deprecated with MM 10.1.0 to reduce the number of reserved words

endorsed_by filter

Endorsements filter

Deprecated with MM 10.1.0 to reduce the number of reserved words

certified_by filter

Certifications filter

Deprecated with MM 10.1.0 to reduce the number of reserved words

commented_by filter

Comments filter

Deprecated with MM 10.1.0 to reduce the number of reserved words

warned_by filter

Warnings filter

Deprecated with MM 10.1.0 to reduce the number of reserved words

The deprecated filter will be treated as the attribute if an attribute with the same name exists.

4. General Rules

A MQL query has a SELECT list, a FROM clause, an optional CONEXT clause, an optional WHERE clause, and an optional ORDER BY clause.

MQL is case-insensitive except for attribute names used in the SELECT list, FROM or WHERE clause, and certain filter values in the FROM or WHERE clause.

5. Attributes

An attribute defines a property of a metadata element or a relationship with another metadata element. Attributes can be used in the SELECT list, FROM clause and WHERE clause.

MM supports the following attribute types:

If an attribute name contains any space, you need to quote it with the double quote (") characters.

5.1 System Attributes

The following system attributes can be used in the SELECT list, FROM clause and WHERE clause of a MQL Query.

System attribute name

Description

Value examples

Value data type

Supported operators in WHERE

"Business Description"

Business Description

A unique number for each customer

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Business Description Inferred"

Business Description Inferred. Warning: Using this attribute may cause bad performance.

Dollar amount remaining in fund account, calculated as: Account Balance Amount - Account Amount Expended - Account Encumbered Amount

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Business Description Inferred Origin"

Name of the Business Description Inferred Origin object. Warning: Using this attribute may cause bad performance.

Account Amount Available

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Business Name"

Business Name

Custom Number

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Business Name Inferred"

Business Name Inferred. Warning: Using this attribute may cause bad performance.

Unified_Dollar_Amount

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Business Name Inferred Origin"

Name of the Business Name Inferred Origin object. Warning: Using this attribute may cause bad performance.

Unified_Dollar_Amount

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

Certifications

Names of the users who certified the object

["Ed", "Susan"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

Certified

Whether or not the object is certified

true

Boolean

=, !=

Children

Names of the children

["EmployeeId", "FirstName","LastName","Address"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

Collections

Collections

["Hot Reports", "New Tables"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Column Histogram"

Column histogram

[{"count": 1,
  "lower": "1930-01-01 00:00:00",
  "upper": "1940-01-01 00:00:00"
  }, {
  "count": 1,
  "lower": "1940-01-01 00:00:00",
  "upper": "1950-01-01 00:00:00"
  }, {
  "count": 3,
  "lower": "1950-01-01 00:00:00",
  "upper": "1960-01-01 00:00:00"
  }, {
  "count": 4,
  "lower": "1960-01-01 00:00:00",
  "upper": "1970-01-01 00:00:00"}]

Array of DataProfile_Histogram

[NOT] EXISTS

"Column Statistics"

Column statistics

{ "DistinctRows": 9,
  "DuplicateRows": 0,
  "ValidRows": 9,
  "EmptyRows": 0,
  "InvalidRows": 0}

DataProfile_Statistics

[NOT] EXISTS

"Comment Count"

Comment count

2

Integer

=, !=

Comments

Names of the users who commented on the object

["Ed", "Susan"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"Content Description"

Description on the model content

Accounts Payable

String

None

"Content Name"

Name of the model content

Accounts Payable

String

None

Context

Context (or model path)

[{
    "id": "204_1",
    "entityType": {
      "name": "Meta Integration (Glossary).Glossary"
    },
    "name": "Finance Glossary"
  }, {
    "id": "204_2",
    "entityType": {
      "name": "Meta Integration (Glossary).Category"
    },
    "name": "Domains"}]

Array of Entity

[NOT] EXISTS

"Data Classifications"

Names of the matched or approved but not rejected data classes. Run GET /repository/dataClasses/{objectId} to get a list of available data classes

["Airport Code", "Airport Name"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"Data Classifications Approved"

Names of the approved data classes. Run GET /repository/dataClasses/{objectId} to get a list of available data classes

["Airport Code", "Airport Name"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"Data Classifications Matched"

Names of the matched data classes. Run GET /repository/dataClasses/{objectId} to get a list of available data classes

["Airport Code", "Airport Name"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"Data Classifications Rejected"

Names of the rejected data classes. Run GET /repository/dataClasses/{objectId} to get a list of available data classes

["Airport Code", "Airport Name"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"Data Profiling"

Data profiling data

{ "totalProfiledRows": 9,
  "totalSemanticTypeRows": 9,
  "profilingDate": "2020-01-23 23:18:49",
  "statistics": {
    "distinctRows": 4,
    ...,
    "maxLength": 24
  },
  "inferredDataTypes": {
    "string": 9
  },
  "invalidDataTypeValues": [],
  "invalidSemanticTypeValues": [],
  "inferredSemanticTypes": {
    "Job Title": 7
  },
  "patterns": {
    "Aaaaa Aaaaaaa": 1,
    ...,
    "Aaaaa Aaaaaaaaaaaaaa": 6
  },
  "values": {
    "Vice President, Sales": 1,
    "Sales Representative": 6,
    "Inside Sales Coordinator": 1,
    "Sales Manager": 1
  },
  "histogram": []}

DataProfile

[NOT] EXISTS

"Data Sampling"

Data sampling data

{ "header": ["city", "birthdate"],
  "samples": [["Seattle", "New York", "San Francisco"], 
              ["1970-01-01", "2001-10-20", "1990-10-02"]]
}

DataSample

[NOT] EXISTS

"Data Type"

Data Type Name

Integer

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

Description

Description

Custom's identifier

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Endorsement Count"

Endorsement count

4

Integer

=, !=

Endorsements

Names of the users who endorsed the object

["Ed", "Susan"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"External Url"

The URL which opens a report in the 3rd Party tool

null

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Hide Data"

Hide Data

true

String

=, !=

Labels

Labels

["Jons Tasks", "Obsolete"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"Last Modified Date"

Last Modified Date

2018-09-24 13:25:17

String

=, BEFORE, AFTER, BETWEEN ... AND ...

"Model Id"

Model Id

35913

Integer

=, = ANY (...)

Name

Name

Custom ID

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Native Id"

Native ID

{799A0437-D91A-4CC5-998A-7C23E2130F39}+00000001

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Object Id"

Object Identifier

123_45678

String

=, = ANY (...)

"Object Profile"

Profile Name. Run GET /types/listProfiles to get a list of profile names

Tableau (Business)

String

=, = ANY (...)

"Object Stable Id"

Object stable identifier encoded in Base 64

AAAC0AAa1sJ7wyKdA0EUkZYCu9hE_-v4uQ

String

=, = ANY (...)

"Object System Type"

Entity System Type: Entity Group, Relationship Group, Classifier, Feature, Diagram, Connection, Schema, Content, System, Hierarchy, Model, Namespace, Key, or Custom Attribute

Classifier

String

=, = ANY (...)

"Object Type"

Entity Type. Run GET /types/listEntityTypes to get a list of entity types of a profile

Table

String

=, = ANY (...)

"Object Type Id"

Entity Type ID

277000016

String

=, = ANY (...)

Parent

The name of the parent

dbo

String

=, = ANY (...)

"Physical Name"

Physical Name

CustomID

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Term Classification"

Name of the business term used to classify the object

EmployeeID

String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

Used

Whether or not a business term is used, i.e. has semantic usage

true

Boolean

=, !=

"Warning Count"

Warning count

1

Integer

=, !=

Warnings

Names of the users who put a warning on the object

["Ed", "Susan"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"Workflow Actions"

Workflow step-based actions on a business model object available to you with the workflow role assignments you have

["Discard", "Mark for Deprecation", "Start Review", "Submit for Approval"]

Array of String

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

"Workflow State"

Current workflow state of a business model object

Approved

String

=, !=, = ANY (...), != ANY (...), [NOT] EXISTS

"Workflow Published"

Return value true or false indicates whether or not an object belongs to the editable version of a workflow enabled business model and the object exists in the published version of the model. Null is returned if the object belongs to a non-editable version or the model is not workflow enabled.

true

String

=, !=, [NOT] EXISTS

Unlike profile attributes, custom attributes and UDPs, which normally hold scalar values (except for multi-valued custom attributes), the value of a system attribute can be an object or an array of scalar values or objects. For example, the value of Context is an array of Entity objects.

System attributes can be used in the SELECT list, FROM clause and WHERE clause of any MQL query, which can be used to define a Worksheet in MM Explorer or used as an input parameter or part of the body parameter to some REST API methods.

The following REST API methods take a SELECT query parameter, or a QueryInput body parameter which contains a SELECT clause. For more information about the REST API methods, refer to the MM REST API documentation.

5.1.1 Object-valued System Attributes

The following system attributes are object-valued:

The examples in the previous section show such values in Json format. The schema of the objects Entity, DataSample, DataProfile, DataProfile_Statistics, and DataProfile_Histogram, etc. are defined in the MM REST API documentation.

When used in the FROM or WHERE clause, the filters with the object-valued system attributes can only be ANDed with other filters and only be used with the EXISTS and NOT EXISTS operators.

5.2 Related Object Type, Related Object Property AND Related Object Relationship

In MM one object may relate to some other object(s). We call the latter related object(s) of the current object. The relationship between the current object and its related objects is called the related object type.

For example, a table Customer may have columns such as CustomId, Name, Address, etc. These columns are the table Customer's related objects. The related object type is Children.

The related object types are a subset of the system attributes. These are the related object types currently available:

In some cases, the user wants to select or filter on the properties of the related objects. We call the property of a related object a related object property. For example, "Term Classification"."Workflow State" refers to the workflow state of the business term used to classify the current object.

Here is a list of properties defined on each of the related object type:

Related Object Type

Related Object Properties

"Business Description Inferred Origin"

System Attribute, profile attribute, custom attribute, and udp

"Business Name Inferred Origin"

System Attribute, profile attribute, custom attribute, and udp

Children

System Attribute, profile attribute, custom attribute, and udp

Parent

System Attribute, profile attribute, custom attribute, and udp

"Data Classifications"

Name, Description, "Hide Data"

"Data Classifications Approved"

Name, Description, "Hide Data"

"Data Classifications Matched"

Name, Description, "Hide Data"

"Data Classifications Rejected"

Name, Description, "Hide Data"

"Term Classification"

System Attribute, profile attribute, and custom attribute

The user may also select on the relationships of the related objects. We call the relationship of a related object a related object relationship. For example, "Term Classification".Synonyms refers to the synonyms of the business term used to classify the current object. Relationships and related object relationships can appear only in the SELECT list but not the FROM or WHERE clause. The value of a relationship or related object relationship is an array of objects of the type Entity.

A related object type can be used as a system attribute by itself or as a related object property of another related object type or the same related object type. When a related object type is used as a system attribute or a related object property, its value is the same as the Name system attribute of the related object(s). For example, the system attribute Term of the EmployeeId column can be "Employee ID", which is the name of the "Employee ID" business term.

We only support related objects of one level. I.e. we do not support the following: "Business Name Inferred Origin"."Data Classifications"."Hide Data".

5.2.1 Multi-valued Related Object Types

One object may have multiple related objects of the same type. For example, a table may have multiple columns. This kind of related object types is multi-valued.

The following related object types are multi-valued:

The value of any multi-valued related object property is an array of strings or other object types.

If the multi-valued related object property is a custom attribute of the Enumeration (multi-value) data type, or a multi-valued system attributes, such as Children, "Data Classifications", "Data Classifications Approved", "Data Classifications Matched", "Data Classifications Rejected", Labels, Collections, "Workflow Actions", Context, "Column Histogram", the value of the property is an array of arrays of strings or objects. For example, Children.Children of a database schema is an array of arrays. The outer array is an array of tables of the schema. The inner array is an array of column names of each table of the outer array.

The value of any multi-valued related object relationship is an array of arrays of objects of the type Entity.

5.3 Attribute resolution

A name can identify multiple property types especially if the configuration contains models with many different profiles. To prevent ambiguity the user may qualify a profile attribute or profile relationship with a profile name. Custom attributes, UDPs and system attributes also share the same name space. MM uses the following precedence rules in resolving ambiguity among the custom attributes, UDPs, system attributes and any unqualified profile attributes or profile relationships. The rules are listed in descending order:
  1. System attributes
  2. Custom attributes
  3. Profile attributes
  4. Profile relationships
  5. UDPs

6. Functions

A function is like an attribute which renders a property or a list of properties of a metadata element. The difference between a function and an attribute is that the function may take parameter(s) whereas the attribute cannot take any parameter(s).
MM supports the following functions:

6.1 ExpandedMembersOfRole

The ExpandedMembersOfRole function takes one role name parameter and returns the locally assigned and inherited users and groups of a role that an object has including the users of those groups as well. The ExpandedMembersOfRole function can be used along with the following operators as a filter in the FROM or WHERE clause.
=, !=, = ANY (...), != ANY (...), = ALL(...), != ALL (...), EXISTS and NOT EXISTS.

For example, the Object1 object has the User1 user and Group1 user group assigned locally to the role Role1 that inherits the Group2 assignment. The Group2 group has the User2 user. The following filters would find the object: For any name specified as a value of the filter, the name is treated as a user if there exist a user and a group by the same name. To avoid ambiguity, avoid naming users and groups by the same names.

The ExpandedMembersOfRole function cannot be used in the SELECT list.

6.2 MembersOfRole

The MembersOfRole function takes one role name parameter and returns the locally assigned and inherited users and groups of a role that an object has (which are shown in the UI Responsibilities tab).
For the example above the membersOfRole('Role1') function returns 3 items for the Object1 object, inherited Group2 user group, locally assigned User1 user, and Group1 user group.

The MembersOfRole function can only appear in the SELECT list.

7. Select

The SELECT list consists of a list of attribute names and/or function calls delimited by comma (,).

8. From

The FROM clause is the source of metadata elements from which the other clauses of the query can access attributes. The FROM clause consists of a FROM id and an optional scope filter(s).

8.1 From ID

The FROM id may take one of the following forms: The FROM id may specify only one path or id of a version, content, model or metadata element. If you need to specify multiple sources, use the path or id of the configuration as the FROM clause and add scope filter(s) to include the metadata elements that belong to a subset of the models, mappings or glossaries of the configuration.

8.2 Scope Filters

The scope filter(s) consists of 1..n filters combined with AND and OR operators. They are similar to the filters in the WHERE clause except that the scope filters are operated on the FROM id to restrict the source of the query.

The objects that satisfy the scope filters can be models (single models, multi-models, or internal models within multi-models) or metadata elements of the models.

If an object is a single model, the single model itself and all metadata elements that belong to the single model are included in the search scope.

If an object is a multi-model, the multi-model itself, all the internal models and all metadata elements that belong to the internal models are included in the search scope.

If an object is an internal model within a multi-model, the current metadata element and all the metadata elements below the current metadata element in the model hierarchy to the lowest level of granularity (e.g., fields in a file, or columns in a table) without stopping at the internal model level are included in the search scope.

Here are some examples of using the scope filter in a query.

"from": "/Configuration/Published",
"scope": "category = 'Database.Databases' AND Name contains 'Finance'"
Find objects in Database models which contain "Finance" in the model names in the context of the Published configuration.

"from": "/Configuration/Published",
"scope": "\"Object Type\" = 'Delimited File' AND Name CONTAINS 'address'"
Find objects from the delimited files which name contains "address" in the context of the Published configuration.

"from": "/Configuration/Data Lake",
"scope": "category = 'File.Folders' AND Name CONTAINS 'business'"
Find objects under the folders which name contains "business" in the context of the "Data Lake" File System model.

"from": "AAABggAi7fOWkSYsvqBtp5-Kjx_WysUqRw",
"scope": "semantic_search_text='address files'",
"where": "semantic_search_text='id field' WITHIN ('Name', 'Physical Name')"
Find all fields having "id" in the name in all the files which have "address" in the name under a folder "DataCataloging" which stable id is "AAABggAi7fOWkSYsvqBtp5-Kjx_WysUqRw".

Note the difference between the scope filters and WHERE filters.

If the filters "category = 'Database.Databases' AND Name contains 'Finance'" appear in the WHERE clause, the result set will include only the databases models that contain "Finance" in their names.

However, if the filters "category = 'Database.Databases' AND Name contains 'Finance'" are used as the scope filters, then the databases models that contain "Finance" in their names and all the metadata elements that belong to the database models, such as schemas, table, and columns, are included. These metadata elements do not need to have "Finance" in their names.

9. Context

The CONTEXT clause can be used to define the scope to look up the inferred business name and description attributes for data elements. Specify the path or object id of a content or version of a configuration, for example, /Configuration/Published, as the CONTEXT clause if you have the following system attributes in the SELECT or WHERE clause.

If not specified, the scope defined by the FROM clause will be used for the CONTEXT.

10. Where

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

10.1 Filter Types

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'

content

The value(s) should be the name(s) of valid content(s) in the current configuration.To specify a nested model content, use "<multi-model-content>.<single-model-content>".

=, = ANY (...)

content = ANY ('Insights Lab Integrated', 'Provider Business Model V25', '"Data Lake"."Address.csv"')

category

Category of the search scope, such as 'Business Glossary.Business Term', or 'Database'

=, = ANY (...)

category='Business Glossary.Business 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

"Data Type"='int' OR "Data Modeling (Business)"."Data Type"='STRING'

Custom attribute name

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS, [NOT] STARTS WITH, [NOT] ENDS WITH, [NOT] CONTAINS

"Custom Attribute Int" = ANY (2000, 2001)

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'

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 function is supported.

=, !=, = ANY (...), != ANY (...), = ALL (...), != ALL (...), [NOT] EXISTS

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

10.2 Value Types

The following values types are supported:

10.3 Operators

Following is a list of operators defined by MQL:

Operator name

Symbol

Supported by Filter Types

EQUALS

= <value>

All

EQUALS WITHIN

= <value> WITHIN (search properties)

text, semantic_search_text

NOT EQUALS

!= <value> or <> <value>

profile attribute, custom attribute, UDP, system attribute, related object property, ExpandedMembersOfRole

EQUALS ANY

= ANY (v1, v2, …, vn)

Content, category, profile attribute, custom attribute, UDP, system attribute, related object property, ExpandedMembersOfRole

NOT EQUALS ANY

!= ANY (v1, v2, …, vn)

profile attribute, custom attribute, UDP, system attribute, related object property, ExpandedMembersOfRole

EQUALS ALL

= ALL(v1, v2, …, vn)

profile attribute, custom attribute, UDP, system attribute, related object property, ExpandedMembersOfRole

NOT EQUALS ALL

!= ALL (v1, v2, …, vn)

profile attribute, custom attribute, UDP, system attribute, related object property, ExpandedMembersOfRole

EXISTS

EXISTS

profile attribute, custom attribute, UDP, system attribute, related object property, ExpandedMembersOfRole

NOT EXISTS

NOT EXISTS

profile attribute, custom attribute, UDP, system attribute, related object property, ExpandedMembersOfRole

STARTS WITH

STARTS WITH <value>

profile attribute, custom attribute, UDP, system attribute, related object property

NOT STARTS WITH

NOT STARTS WITH <value>

profile attribute, custom attribute, UDP, system attribute, related object property

ENDS WITH

ENDS WITH <value>

profile attribute, custom attribute, UDP, system attribute, related object property

NOT ENDS WITH

NOT ENDS WITH <value>

profile attribute, custom attribute, UDP, system attribute, related object property

CONTAINS

CONTAINS <value>

profile attribute, custom attribute, UDP, system attribute, related object property

NOT CONTAINS

NOT CONTAINS <value>

profile attribute, custom attribute, UDP, system attribute, related object property

BEFORE

BEFORE <value> (exclusive)

"Last Modified Date", profile attribute, custom attribute, related object property

AFTER

AFTER <value> (exclusive)

"Last Modified Date", profile attribute, custom attribute, related object property

BETWEEN

BETWEEN <value> AND <value> (inclusive)

"Last Modified Date", profile attribute, custom attribute, related object property

Note:

When applied to a profile attribute, custom attribute, UDP, system 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, UDP, system attribute or related object property is applicable to the objects and the filter condition is satisfied.

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.

10.4 Multi-valued Related Object Type In Where

What does "Children.\"Data Type\" NOT ENDS WITH 'char'" mean? Does it mean there is at least one child's data type that does not end with "char" or there is no children's data type that ends with "char"?

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

For single valued operators, a related object property filter is true if the operator yields true for any related objects. The result is false if no true result is found (including the case where the related object type returns no result).

Therefore "Children.\"Data Type\"" NOT ENDS WITH 'char'" returns true if there is at least one children's data type that does not end with "char".

For the = ALL (...) operator, the predicate returns true if the related object property 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 FistName and LastName as its children.

For the = ANY (...) operator, the predicate returns true if the related object property 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.

If a multi-valued related object property 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 predicate returns true if the combined related object property of all related objects contains all values on the right hand side.

For the = ANY (...) operator, the predicate returns true if the combined related object property of all related objects contains 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 varchar(100).

10.5 Keywords For Filter Values

User keywords: ME

A user keyword can be used as a value of ExpandedMembersOfRole, Endorsements, Certifications, Comments and Warnings filters.

Date keywords: TODAY, YESTERDAY

A date keyword can be used as a value of a "Last Modified Date" system attribute filter, profile attribute filter, custom attribute filter or related object property filter.

10.6 Date And Datetime Filter Values

Meta Integration supports the following date or datetime filter values:

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

LAST <n> 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:

10.7 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 content='PAYTRANS'.

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

10.8 System Attribute Filters Not Working With OR

Some system attribute filters do not work with the OR operator either. They must be ANDed with other filters.

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

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

10.10 Where Clause Examples

11. Order By

An optional ORDER BY clause can be specified to enforce the sort order of the results.

MQL supports three sort order:

If a text filter or semantic_search_text filter is present, the default sort order is relevance; otherwise the default sort order is name ascending.

12. Restricted Words And Characters

12.1 Reserved Characters

MQL has a list of reserved characters:

12.2 Reserved Words

MQL also has a list of reserved words. These words need to be surrounded by double quote character(") if you wish to use them in filter values.

"After", "all". "and", "any", "before", "between", "category", "contains", "content", "day", "days", "distinct", "ends", "exists", "expandedMembersOfRole", "hour", "hours","last", "me", "membersOfRole", "minute", "minutes", "not", "or", "semantic_search_text", "starts", "text", "today", "with", "within", "yesterday".

The semantic_search_text filter has its own set of reserved words, such as term(s), report(s), and file(s). Refer to Meta Integration Search Help for more information.

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

13. Case sensitivity

The following are case-insensitive:

The following are case-sensitive:

14. Unknown/Inapplicable Attributes And Unknown Filter Values

Meta Integration (MM) may execute a MQL Query even if the query contains unknown/inapplicable attribute types, object types, content names, labels, data classes and/or user names etc. However, the results of the query may not match your expectation. Be sure to validate the MQL Query before you execute it.

The following is a list of behaviors how MM would act when it encounters an unknown/inapplicable attribute or an unknown filter value:

Here are some examples that demonstrate the search behavior when a unknown type or name is used as a filter value.

  1. If an unknown content name, say "ETL Model 2", is used in a content filter "content = ANY ('DB Model1', 'ETL Model 2')", the search will be limited to the content "DB Model1".
  2. The filter "object_type = ANY ('Business Glosary', 'Business Term')" will return objects of type Business Term. The object type "Business Glosary" has a typo and will be ignored.
  3. If John does not exist in the list of users or John is not a workflow editor of any object, the filter "ExpandedMembersOfRole('Workflow Editor') = 'John'" will return no result; but the filter "ExpandedMembersOfRole('Workflow Editor') != 'John'" will return all objects in the search scope.
  4. If "Workflow Editor" is not a defined object role, the filter "ExpandedMembersOfRole('Workflow Editor') = 'John'" will be ignored.

15. Limitations

  1. Some system attributes cannot be used with the OR operator in the FROM or WHERE clause. Refer to System Attribute Filters Not Working With OR for a list of such system attributes.