MCPS Query Language Definition
The Oracle Unity platform is able to query large data sets for the purpose of segmentation, cube, analytics, etc. This document outlines a metadata query language used to describe these query types. The goals of this design are:
-
UI Friendly - Easy to construct (and import) via a query builder tool.
-
Flexible - Implementation agnostic. Can be fed into different query engines and technologies.
-
Secure - No SQL injection possible.
In this topic:
Objects
This section describes the different objects of the query language for an MCPSQuery, which is a relational query used for segmentation and reporting use cases. These objects are passed as JSON through the REST API.
MCPSQuery
Top object. Used to name and store the query. Contains a SetOperation defining the query.
- String uniqueId - A unique id for this object (per tenant). This only needs to be set for CRUD calls on this object. It is not required for this object to be passed to query/count or query/execute methods.
- SetOperation operation - The SetOperation for this Query.
SetOperation
An object representing a Union, Intersection or Exclusion of 2 SetOperands. If only 1 SetOperand is provided, the operator is ignored. SetOperation is a SetOperand.
- String name - A name for this operation.
- String ctype - Always should be set to ".SetOperation".
- boolean distinct <false>/true - Whether or not the output of this query should return only distinct instances.
- SetOperator operator - UNION (Union of the 2 operands), INTERSECTION (intersection of 2 operands) or EXCLUSION (subtract the second operand from the first).
- List<SetOperand> operands - 1 or 2 SetOperands. A SetOperand is currently either a SetOperation, ObjectSet or ObjectSetReference.
- List<JoinCondition> joinConditions - Pairs of 2 ReferenceAttributes to join the 2 operands. Eg. C1.ID = E1.CustomerID (where C1 is Customer alias and E1 is Event alias). ReferenceAttributes used in a joinCondition must be exposed as outputAttributes in their respective SetOperation or ObjectSet.
- List<AttributeValue> outputAttributes - The attributes to return from this SetOperation. These attributes are raised and then available for upper-level parts of the query (for joins, or export).
ObjectSet
An object representing a query on an entity. Basically a select from table statement. An ObjectSet is a SetOperand.
- String name - A name for this object. This name is used as a alias for this table throughout the query.
- String ctype - Always should be set to ".ObjectSet".
- String uniqueId - A unique id for this ObjectSet (per tenant) for persisting it. This attribute is optional if this ObjectSet is inline in a MCPSQuery. It is used when persisting the ObjectSet for reuse in other/many MCPSQuerys.
- String objectName - The name of the object (entity) this ObjectSet is querying. (eg. Customer, Event, Materialized Views or Data Source Views).
- List<Parameter> parameters - A list of definitions of parameters that this ObjectSet accepts/requires. The parameters are then used anywhere an AttributeValue is allowed using a ParameterValue. This allows for saved, template queries to be re-used by passing in unique parameters to an ObjectSet. Can be NULL.
- boolean distinct <false>/true - Whether or not the output of this query should return only distinct instances.
- Criteria criteria - Any criteria for this part of the query. Basically the where clause of this part of the query. Can be NULL in cases where this ObjectSet is used to join to another which will have Criteria.
- Criteria havings - The having portion of the query. AggregateAttributes can only be used in the having part of the query (and outputAttributes), not in criteria. havings needs to be coordinated with the groupBy attribute to ensure the proper results. For example performing a having with MAX(Order.Total) would normally be accompanied by a groupBy of Event.CustomeID. Can be NULL.
- List<AttributeValue> groupBy - What attributes to group by. Can be NULL.
- List<SortedAttribute> sortBy - A list of sorted attributes to sort by. Can be NULL.
- Occurrence occurrence - Properties to allow the selection of particular occurrences of an object. Such as FIRST, LAST, etc. Can be NULL.
- ObjectJoin objectJoin - A join condition to join this ObjectSet with another. If not joining another object, then NULL.
- List<AttributeValue> outputAttributes - The attributes to return from this ObjectSet. Must be set for at least one attribute.
ObjectSetReference
An object representing a persisted ObjectSet. If you create an ObjectSet and want to reuse it in different queries, you can persist it and use an ObjectSetReference to reference that ObjectSet. The ObjectSet will be loaded and inserted inline before processing on the server. An ObjectSetReference is a SetOperand.
- String name - Any string to identify this object.
- String ctype - Always should be set to ".ObjectSetReference".
- String osId - A unique identifer of the ObjectSet this is referencing. This should match the uniqueId attribute on the ObjectSet.
- List<Parameter> parameters - A list of definitions of parameters that this ObjectSet accepts/requires. The parameters are then used anywhere an AttributeValue is allowed.
Criteria
Query criteria to be added to the query.
- CriteriaOperator operator - Operation list. Eg, AND, OR, EQUALS, NOT_EQUALS, IN, NOT_IN, GREATER_THAN, BETWEEN, LESS_EQUAL_THAN, NULL, NOT_NULL, MINUS, etc.
- String ctype - Always should be set to ".Criteria".
- List<CriteriaOperand> operands - Currently a CriteriaOperand is either a Criteria or an AttributeValue. The number of operands in the list is dictated by the operator. Equals would have 2 for example, while IN could have many with the first being the AttributeValue and the rest being the things to add to the IN evaluation.
Usage Examples:
Email NOT_NULL
Email NOT_EQUALS "john.doe@oracle.com"
ObjectJoin
Contained in an ObjectSet and used to join that set with another ObjectSet.
- JoinType type - One of LEFT, FULL_OUTER, RIGHT_OUTER, LEFT_OUTER.
- List<JoinCondition> condition - One or more JoinConditions to join these two ObjectSets.
- ObjectSet targetSet - The other ObjectSet to join to.
AttributeValue
An interface representing any of the instances of values used in the query. Each AttributeValue must have one of the attributes atype or ctype set. atype is set when this attribute is being used as an AttributeValue. ctype is set when this attribute is being used as a CriteriaOperand Instances are described below: An AttributeValue is also a CriteriaOperand.
ReferenceAttribute
Representing an attribute of a table/alias.column/alias, such as 'customer.ID' or 'E1.CustomerID as ID'.
You can find instances of .ReferenceAttribute inside DSVs, promotion rules, aggregatedattributes, and MCPSqueries.
"atype|ctype": ".ReferenceAttribute",
"tableName": "CA_OrderItemCube",
"attributeName": "total_returns_amt",
"alias": "total_returns_amt"
-
atype or ctype – ".ReferenceAttribute".
-
Use atype when the attribute is being used as an AttributeValue.
-
Use ctype when the attribute is being used as a CriteriaOperand.
-
Determining whether to use atype or ctype is determined by the parent object containing the attribute.
-
- String tableName - The name of the cube or table being referenced.
- String attributeName - The name of the column or alias being referenced. Must exist in the context of tableName.
-
String alias - (Optional) A name that this attribute will be known by and can be used by others.
TypedReferenceAttribute
Overwrites "type" in outputAttributes, for example FirstName.
{ "atype": ".TypedReferenceAttribute",
"tableName": "c1",
"attributeName": "FirstName", "alias": null,
"type": { "columntype": ".ColumnPrimitiveType", "dataType": "TEXT" } }
-
atype – ".TypedReferenceAttribute".
- String tableName - The name of the table being referenced.
- String attributeName - The name of the column or alias being referenced. Must exist in the context of tableName.
-
String alias - (Optional) A name that this attribute will be known by and can be used by others.
By default, a QUERYEXPORT job generates a table whose attribute columns can hold a default maximum of 999 characters. If the queried data includes data written to a TEXT data type, then use TypeReferenceAttribute to set the QUERYEXPORT attribute data type to TEXT. If you don't then the QUERYEXPORT job will fail when it encounters an attribute that is longer than 999 characters.
To avoid this, create QUERYEXPORT job with MCPSQuery with "atype": ".TypedReferenceAttribute" as shown below. When you run the export job, the data type of the attribute > 999 characters is persisted as "TEXT" with 8000 characters.
Sample MCPSQUERY with TypedReferenceAttribute:
{
"atype": ".TypedReferenceAttribute",
"tableName": "c1",
"attributeName": "FirstName",
"alias": null,
"type": {
"columntype": ".ColumnPrimitiveType",
"dataType": "TEXT"
}
},
{
"atype": ".ReferenceAttribute",
"tableName": "c1",
"attributeName": "LastName",
"alias": null
}
],
"joinConditions": []
},
"type": "DW"
}
}
StaticAttribute
Any static value representing this AttributeValue.
You can find instances of .StaticAttribute inside DSVs, buckets, and aggregatedattributes.
-
T data - The data holding this value. Can be one of Number, String, Date.
One of:
- String atype - Always should be set to ".StaticAttribute".
- String ctype - Always should be set to ".StaticAttribute".
FunctionAttribute
FuctionAttribute are used to perform a database function.
You can find .FunctionAttributes inside DSVs and aggregatedattributes.
"atype": ".FunctionAttribute",
"type": see list below
"attributes": []
"alias": "an_alias"
-
atype or ctype – ".FunctionAttribute".
-
Use atype when the attribute is being used as an AttributeValue.
-
Use ctype when the attribute is being used as a CriteriaOperand.
-
Determining whether to use atype or ctype is determined by the parent object containing the attribute.
-
- type enum - A supported ADW database function. These functions are typical database functions and can be Googled for more information.
ADD
CASE
CAST
COMPLEX_FUNCTION
CONCATENATE
CUSTOM
DAY
DISTINCT
DIVIDE
DOM (day of month)
HOUR
LOWER
MINUTE
MOD
MONTH
MULTIPLY
SUBTRACT
TIMESTAMP
TODATE
UNIX_TIME
UPPER
YEAR
- attributes - Can include a CriteriaAttribute, StaticAttribute, ReferenceAttribute, or another FunctionAttribute.
-
String alias - (Optional) A name that this attribute will be known by and can be used by others.
If the type is CUSTOM, then also include the functionName parameter to identify the custom operation you are specifying. These custom database functions can be Googled for more information.
- functionName enum - One of the function types supported by the FunctionAttribute in ADW. These functions are typical database functions and can be Googled for more information.
- ADD_MONTHS
- CEILING
- COALESCE
- CONCAT
- CURRENT_DATE
- DATEDIFF
- DATE_ADD
- DATE_FORMAT
- DAY
- FLOOR
- GET_JSON_OBJECT
- GREATEST
- INITCAP
- INSTR
- LAST_DAY
- LENGTH
- LOWER
- LPAD
- MONTH
- MONTHS_BETWEEN
- NVL
- REFLECT
- ROUND
- SIZE
- SPLIT
- SUBSTR
- TIMESTAMPDIFF
- UPPER
You can nest FunctionAttributes inside other Function or Reference Attributes to perform layered operations.
ParameterValue
Representing a passed parameter. Must reference a Parameter defined in the contain object (ObjectSet).
-
String identifier - The identifier of the Parameter object this value references.
One of:
- String atype - Always should be set to ".ParameterValue".
- String ctype - Always should be set to ".ParameterValue".
AggregateAttribute
An attribute representing a ReferenceAttribute and an aggregate operation.
- AggregateType type - One of SUM, COUNT, MIN, MAX.
- ReferenceAttribute attribute - The attribute to perform the operation on.
One of: - String atype - Always should be set to ".AggregateAttribute".
- String ctype - Always should be set to ".AggregateAttribute".
SortedAttribute
An attribute representing a ReferenceAttribute and a sort direction.
- Order order - One of ASC, DESC.
- ReferenceAttribute attribute - The attribute to perform the sort on.
One of: -
String atype - Always should be set to ".SortedAttribute".
-
String ctype - Always should be set to ".SortedAttribute".
Occurence
An object describing a particular occurrence of an object in an ObjectSet query.
-
OccurrenceType type - One of:
FOREMOST- first occurrence in the full data set. if the given criteria doesn't cover the period of when the first occurrence is, the row is omitted.FIRST- first occurrence in the set returned using the criteria of the query. As long as 1 instance is in the returned set, the row will be returned.LAST- last occurrence in the set returned using the criteria of the query. As long as 1 instance is in the returned set, the row will be returned.LATEST- last occurrence in the full data set. If the given criteria doesn't cover the period of when the latest occurrence is, the row is omitted.
- ReferenceAttribute orderAttribute - The attribute determining the order.
- int limit - The threshold of how many to return if more than one instance matches the type criteria. (eg, multiple events with the same timestamp).
- int count - How many instances (within a limit) to return.
- ReferenceAttribute partitionAttribute - The attribute to partition the data on. For example if object was Event, orderAttribute would be EventTS and partitionAttribute could be CustomerID, to partition the last XX event by each customer.
Parameter
A parameter passed into a reusable query.
- String identifier - How to uniquely identify this parameter. This value must match the identifier value in ParameterValue any time this parameter is used in a query.
- ParameterType type - One of TIMESTAMP, STRING, NUMBER
- Description description - Description of parameter, helpful (along with type) for the UI
- CriteriaOperand data - The value of this parameter (passed in). It can be any of the AttributeValues (most common is StaticAttribute) or actual Criteria (eg, $NOW - 3600000) for a time window.
Examples
EXAMPLE 1
--Profile + Behavioral, multiple conditions, and/or:
[(Email_Deliverability_Status_ = D)
AND (Email_Permission_Status_ = Y)
AND (Country_ = US)
AND (Fav_City in (Oslo, New York, Hong Kong))
AND (Email Domain not equal to "yahoo.com")
AND (Income >5000)
AND (has ordered * SKU_Name in(iCar, iMac) between 2/1/2015 and 12/1/2015)
AND (Last Order_Date** is <=6/1/2015)
]
OR [
((is not a member of the "Loyalty" table)
AND (Hasn't been sent the campaign of Marketing_Strategy "Cross Sell" in the last 1 month))]
Markup For Example 1
{
"MCPSQuery": {
"tenantId": 10,
"name": "CX_AUD_1",
"active": true,
"operation": {
"ctype": ".SetOperation",
"tenantId": 10,
"name": "TopOp",
"active": true,
"operands": [
{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "C1",
"active": true,
"objectName": "Customer",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}
],
"distinct": true,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [
{
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "c_EmailDeliverabilityStatus"
},
{
"ctype": ".StaticAttribute",
"data": "D"
}
]
},
{
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "okToEmail"
},
{
"ctype": ".StaticAttribute",
"data": "I"
}
]
},
{
"ctype": ".Criteria",
"operator": "GREATER_EQUAL_THAN",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "c_LastOrderTS"
},
{
"ctype": ".StaticAttribute",
"data": 1302332400
}
]
},
{
"ctype": ".Criteria",
"operator": "IN",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "c_FavCity"
},
{
"ctype": ".StaticAttribute",
"data": "Oslo"
},
{
"ctype": ".StaticAttribute",
"data": "New York"
},
{
"ctype": ".StaticAttribute",
"data": "Hong Kong"
}
]
},
{
"ctype": ".Criteria",
"operator": "NOT_EQUALS",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "c_EmailDomain"
},
{
"ctype": ".StaticAttribute",
"data": "yahoo.com"
}
]
},
{
"ctype": ".Criteria",
"operator": "GREATER_THAN",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "c_Income"
},
{
"ctype": ".StaticAttribute",
"data": 5000
}
]
}
]
},
"objectJoin": {
"type": "LEFT",
"condition": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "OI1",
"attributeName": "CustomerID"
}
}
],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "OI1",
"active": true,
"objectName": "OrderItem",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "OI1",
"attributeName": "CustomerID"
}
],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "BETWEEN",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "OI1",
"attributeName": "OrderEntryTS"
},
{
"ctype": ".StaticAttribute",
"data": 1446361200
},
{
"ctype": ".StaticAttribute",
"data": 1454313600
}
]
},
"objectJoin": {
"type": "LEFT",
"condition": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "OI1",
"attributeName": "ProductID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "P1",
"attributeName": "ID"
}
}
],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "P1",
"active": true,
"objectName": "Product",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "P1",
"attributeName": "ID"
}
],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "IN",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "P1",
"attributeName": "Name"
},
{
"ctype": ".StaticAttribute",
"data": "iCar"
},
{
"ctype": ".StaticAttribute",
"data": "iMac"
}
]
}
}
}
}
}
},
{
"ctype": ".SetOperation",
"tenantId": 10,
"name": "C2",
"active": true,
"operands": [
{
"ctype": ".SetOperation",
"tenantId": 10,
"name": "CLOP",
"active": true,
"operands": [
{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "C4",
"active": true,
"objectName": "Customer",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C4",
"attributeName": "ID"
}
],
"distinct": false
},
{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "L1",
"active": true,
"objectName": "c_Loyalty",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "L1",
"attributeName": "c_CustomerID",
"alias": "CustomerID"
}
],
"distinct": false
}
],
"operator": "EXCLUSION",
"distinct": true,
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C4",
"attributeName": "ID"
}
],
"joinConditions": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C4",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "L1",
"attributeName": "CustomerID"
}
}
]
},
{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "E1",
"active": true,
"objectName": "Event",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "CustomerID"
}
],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "BETWEEN",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "EventTS"
},
{
"ctype": ".StaticAttribute",
"data": 1451203200
},
{
"ctype": ".StaticAttribute",
"data": 1453795200
}
]
},
"objectJoin": {
"type": "LEFT",
"condition": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "LaunchID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "LAU1",
"attributeName": "ID"
}
}
],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "LAU1",
"active": true,
"objectName": "Launch",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "LAU1",
"attributeName": "ID"
}
],
"distinct": false,
"objectJoin": {
"type": "LEFT",
"condition": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "LAU1",
"attributeName": "CampaignID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "CAMP1",
"attributeName": "ID"
}
}
],
"targetSet": {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "CAMP1",
"active": true,
"objectName": "Campaign",
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "CAMP1",
"attributeName": "ID"
}
],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [
{
"ctype": ".ReferenceAttribute",
"tableName": "CAMP1",
"attributeName": "Source"
},
{
"ctype": ".StaticAttribute",
"data": "Cross Sell"
}
]
}
}
}
}
}
}
],
"operator": "EXCLUSION",
"distinct": false,
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "CLOP",
"attributeName": "ID",
"alias": "ID"
}
],
"joinConditions": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "CLOP",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "E1",
"attributeName": "CustomerID"
}
}
]
}
],
"operator": "UNION",
"distinct": false,
"outputAttributes": [
{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}
],
"joinConditions": [
{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "C2",
"attributeName": "ID"
}
}
]
}
}
}
Simple example selecting ID from customer table
{
"MCPSQuery": {
"tenantId": 10,
"name": "example",
"uniqueId": "example",
"operation": {
"ctype": ".SetOperation",
"name": "example",
"tenantId": 10,
"uniqueId": "example",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "C1",
"description": "C1",
"objectName": "Customer",
"uniqueId": "C1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}],
"distinct": false
}, null],
"operator": "INTERSECTION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}],
"joinConditions": []
}
}
}
Simple example selecting ID from customer table where FirstName = 'John' and LastName = 'Smith'
{
"MCPSQuery": {
"tenantId": 10,
"name": "example",
"uniqueId": "example",
"operation": {
"ctype": ".SetOperation",
"name": "example",
"tenantId": 10,
"uniqueId": "example",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "C1",
"description": "C1",
"objectName": "Customer",
"uniqueId": "C1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "FirstName"
}, {
"ctype": ".StaticAttribute",
"data": "John"
}]
}, {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "LastName"
}, {
"ctype": ".StaticAttribute",
"data": "Smith"
}]
}]
}
}, null],
"operator": "INTERSECTION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}],
"joinConditions": []
}
}
}
Simple example selecting ID from customer table, State from address table, joining the two tables on customer ID where customer FirstName = 'John' and customer LastName = 'Smith'
{
"MCPSQuery": {
"tenantId": 10,
"name": "example",
"uniqueId": "example",
"operation": {
"ctype": ".SetOperation",
"name": "example",
"tenantId": 10,
"uniqueId": "example",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "C1",
"description": "C1",
"objectName": "Customer",
"uniqueId": "C1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "FirstName"
}, {
"ctype": ".StaticAttribute",
"data": "John"
}]
}, {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "LastName"
}, {
"ctype": ".StaticAttribute",
"data": "Smith"
}]
}]
}
}, {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "A1",
"description": "A1",
"objectName": "Address",
"uniqueId": "A1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "CustomerID"
}, {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "State"
}],
"distinct": false
}],
"operator": "INTERSECTION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}, {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "State"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "CustomerID"
}
}]
}
}
}
Simple example selecting ID from customer table, State from address table, joining the two tables on customer ID where customer FirstName = 'John' and customer LastName = 'Smith' and address is in state 'CA'
{
"MCPSQuery": {
"tenantId": 10,
"name": "example",
"uniqueId": "example",
"operation": {
"ctype": ".SetOperation",
"name": "example",
"tenantId": 10,
"uniqueId": "example",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "C1",
"description": "C1",
"objectName": "Customer",
"uniqueId": "C1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "AND",
"operands": [{
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "FirstName"
}, {
"ctype": ".StaticAttribute",
"data": "John"
}]
}, {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "LastName"
}, {
"ctype": ".StaticAttribute",
"data": "Smith"
}]
}]
}
}, {
"ctype": ".ObjectSet",
"tenantId": 10,
"name": "A1",
"description": "A1",
"objectName": "Address",
"uniqueId": "A1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "CustomerID"
}, {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "State"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "State"
}, {
"ctype": ".StaticAttribute",
"data": "CA"
}]
}
}],
"operator": "INTERSECTION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}, {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "State"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "CustomerID"
}
}]
}
}
}
Simple example selecting ID from customer table, State and City from address table, joining the two tables on customer ID where customer Gender = 'M' and address is in city 'New York'
{
"MCPSQuery": {
"tenantId": 21,
"name": "NewYorkCity",
"uniqueId": "NewYorkCity",
"operation": {
"ctype": ".SetOperation",
"name": "NewYorkCity",
"tenantId": 21,
"uniqueId": "NewYorkCity",
"operands": [{
"ctype": ".ObjectSet",
"tenantId": 21,
"name": "C1",
"description": "C1",
"objectName": "Customer",
"uniqueId": "C1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "Gender"
}, {
"ctype": ".StaticAttribute",
"data": "M"
}]
}
}, {
"ctype": ".ObjectSet",
"tenantId": 21,
"name": "A1",
"description": "A1",
"objectName": "Address",
"uniqueId": "A1",
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "CustomerID"
}, {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "State"
}, {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "City"
}],
"distinct": false,
"criteria": {
"ctype": ".Criteria",
"operator": "EQUALS",
"operands": [{
"ctype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "City"
}, {
"ctype": ".StaticAttribute",
"data": "New York"
}]
}
}],
"operator": "INTERSECTION",
"distinct": false,
"outputAttributes": [{
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
}, {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "State"
}, {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "City"
}],
"joinConditions": [{
"left": {
"atype": ".ReferenceAttribute",
"tableName": "C1",
"attributeName": "ID"
},
"right": {
"atype": ".ReferenceAttribute",
"tableName": "A1",
"attributeName": "CustomerID"
}
}]
}
}
}
How does metadata filter work for MCPSQueries?
MCPSQuery metadata can be searched by metadata attributes.