JSON Template Transformation
A Transform describes what the MDE should do with a value before writing it into the
destination property: replace it with a constant, extend it, translate it, or expand it into a list of
entities.
The five operations
| Operation | What it does | Applies to |
|---|---|---|
force |
Ignores the data file and writes a constant. | Any property |
append |
Adds to the value coming from the data file. | Any property |
convert |
Translates an incoming value into another one — an alias into a code, a label into a list of claims. | Any property |
query |
Treats the value as a custom filter expression and fills the destination with everything the filter matches. | opUser.Sites and opUser.Sources only |
list |
Treats the value as a delimited list of business keys and fills the destination with the matching entities. | opUser.Sites and opUser.Sources only |
Golden rule
query and list cannot be mixed in the same template.
Both fill a user's site or source list, and a template commits to one approach or the other. Decide up front: list when the data file names the entities explicitly, query when it carries a rule that selects them.
Note
query and list both combine with the
UserSitesAndSourcesMergeMode, so their result can extend an existing list
rather than replace it.
The Transform object
{
"Transform" : {
"Operation": "[Mandatory] The transformation operation",
"Value": "[Optional] A single value",
"Array": "[Optional] An array of values",
"KeyValuePairs": "[Optional] An array of KeyValuePair objects",
"Claims": "[Optional] An array of Claim objects"
}
}
{
"Value" : "[Mandatory] The value"
}
{
"Array" : ["[Mandatory] Comma-separated values"]
}
{
"KeyValuePair" : {
"Key": "[Mandatory] The key",
"Value": "[Mandatory] The value"
}
}
{
"Claim" : {
"Name": "[Mandatory] The claim name",
"Value": "[Mandatory] The claim value"
}
}
Valid operations are Force, Append, Convert, Query or List
Force operation
Unconditionally set the value of the property specified in OpIdentifier to the value(s) specified in one of the value property, Value, Array or Claims depending on the target property.
| Target property | Transform property |
|---|---|
| system or form | Value |
| User's claims | Claim |
| User's roles | Array |
Forcing the SourceTypeId of any created/updated source
{
"OpIdentifier": "opSource.SourceTypeId",
"Transform": {
"Operation": "Force",
"Value": "92"
}
}
Giving 2 claims to any created/updated user
{
"OpIdentifier": "opUser.Claims",
"Transform": {
"Operation": "Force",
"Claims": [
{
"Name": "http://opisense.opinum.com/sensors",
"Value": "NONE"
},
{
"Name": "http://opisense.opinum.com/buildingproperties",
"Value": "WRITE"
}
]}
}
Setting the role "User" to any created/updated user
{
"OpIdentifier": "opUser.Roles",
"Transform": {
"Operation": "Force",
"Array": [
"User"
]
}
}
Append operation
Append a single value
Append the value specified in Value to the content of the element's value identified by the Path prior to set the property specified in OpIdentifier.
{
"OpIdentifier": "opSource.Tags",
"Transform": {
"Operation": "Append",
"Value": "POSTFIX"
}
}
Append claims
Append a static list of claims specified in Claims to some user-specific claims specified in user's property UserSpecificClaims of the data file.
{
"OpIdentifier": "opUser.Claims",
"Path": "UserSpecificClaims",
"Transform": {
"Operation": "Append",
"Claims": [
{
"Name": "http://opisense.opinum.com/buildingproperties",
"Value": "WRITE"
},
{
"Name": "http://opisense.opinum.com/schedule",
"Value": "READ"
}
]
}
}
Considering that the user's part of the data file is:
{
"Users": [
{
"FirstName": "Acme",
"LastName": "User1",
"EmailAddress": "acme.user1@gmail.com",
"Language": "EN",
"UserSpecificClaims": "[{\"Name\":\"SomeClaimTypeForUser1\",\"Value\":\"SomeClaimValue\"}]"
},
{
"FirstName": "Acme",
"LastName": "User2",
"EmailAddress": "acme.user2@gmail.com",
"Language": "EN",
"UserSpecificClaims": "[{\"Name\":\"OtherClaimTypeForUser2\",\"Value\":\"SomeClaimValue\"}]"
}
]
}
When created, acme.user1@gmail.com will get claims
"http://opisense.opinum.com/buildingproperties", "http://opisense.opinum.com/schedule" and "SomeClaimTypeForUser1"
When created, acme.user2@gmail.com user will get claims
"http://opisense.opinum.com/buildingproperties", "http://opisense.opinum.com/schedule" and "OtherClaimTypeForUser2"
Convert operation
Convert the element's value identified by the Path using the key/value pairs specified in KeyValuePairs
Important
When converting user's claims, the value of each key/value pair must be a string that is the result of the serialization of and array of Claim object. Since this serialization is the value of a JSON element, all ''"'' chars must be escaped ''"'' (see samples below)
Convert an energy name into an EnergyTypeId
{
"OpIdentifier": "opsource.EnergyTypeId",
"Path": "ENERGY_NAME",
"Transform": {
"Operation": "Convert",
"KeyValuePairs": [
{
"Key": "ELECTRICITY",
"Value": "1"
},
{
"Key": "GAS",
"Value": "2"
},
{
"Key": "WATER",
"Value": "3"
}
]
}
}
Convert a job title into a role
{
"OpIdentifier": "opUser.Roles",
"Path": "ROLE",
"Transform": {
"Operation": "Convert",
"KeyValuePairs": [
{
"Key": "Data analyst",
"Value": "Manager"
},
{
"Key": "Maintenance",
"Value": "User"
}
]
}
}
Convert a profile name into a list of claims
{
"OpIdentifier": "opUser.Claims",
"Path": "PROFILE",
"Transform": {
"Operation": "Convert",
"KeyValuePairs": [
{
"Key": "Admin",
"Value": "[{\"Name\":\"http://opisense.opinum.com/buildingproperties\",\"Value\":\"ADMIN\"},{\"Name\":\"http://opisense.opinum.com/sensors\",\"Value\":\"ADMIN\"}]"
},
{
"Key": "Basic",
"Value": "[{\"Name\":\"http://opisense.opinum.com/buildingproperties\",\"Value\":\"NONE\"},{\"Name\":\"http://opisense.opinum.com/sensors\",\"Value\":\"ADMIN\"}]"
}
]
}
}
Query operation
This operation tells the MDE to treat the value provided in the Master Data Upload file as an Insights custom filter expression and to fill the destination list with the result of the filter evaluation.
- The QUERY mode is compatible with UserSitesAndSourcesMergeMode mode, meaning that the query result can be appended to an already existing list.
- If more than one of a user's sites or sources carries a query, the queries are combined with an ''OR'' operator
- LIST and QUERY modes can't be mixed in a template.
Important
This transformation is only valid for the ''opUser.Sites'' and ''opUser.Sources'' identifiers
Warning
There are some limits for this query
- The complexity is limited but unpredictable because it depends on the query itself. As an example, a query like ''Name = xxx OR Name = yyy OR ...'' will fail, let's say, after 200 terms (number of ''OR'' occurrences) but the failure could happen earlier for a more complex query
- The total query length may not exceed 1 megabyte. A query longer than this limit may produce unpredictable results
{
"template": {
"account": {
"id": 0,
"Name": ""
},
"config": {
"whatif": false,
"UserSitesAndSourcesMergeMode": "Merge"
},
"opSite": {
"rootPath": "sites[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "siteKey"
},
{
"opIdentifier": "opSite.Name",
"path": "siteKey"
},
{
"opIdentifier": "opSite.TimeZoneId",
"transform": {
"operation": "force",
"value": "Romance Standard Time"
}
},
{
"opIdentifier": "opSite.TypeId",
"transform": {
"operation": "force",
"value": 15
}
},
{
"opIdentifier": "opSource",
"path": "sources"
}
]
},
"opSource": {
"rootPath": "sites[*].sources[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.Name",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.EanNumber",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.GatewayId",
"transform": {
"operation": "force",
"value": "168552"
}
},
{
"opIdentifier": "opSource.SourceTypeId",
"transform": {
"operation": "force",
"value": "92"
}
},
{
"opIdentifier": "opSource.GatewayTypeId",
"transform": {
"operation": "force",
"value": "36"
}
},
{
"opIdentifier": "opVariable",
"path": "variables"
},
{
"opIdentifier": "opSource.TimeZoneId",
"transform": {
"operation": "force",
"value": "UTC"
}
}
]
},
"opUser": {
"rootPath": "users[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.email",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.UserName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.FirstName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.LastName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.Roles",
"transform": {
"operation": "force",
"array": [
"User"
]
}
},
{
"opIdentifier": "opUser.Language",
"path": "language"
},
{
"opIdentifier": "opUser.Sites",
"path": "sites",
"transform": {
"operation": "query"
}
},
{
"opIdentifier": "opUser.Sources",
"path": "sources[*].sourceEan",
"transform": {
"operation": "query"
}
}
]
}
}
}
{
"sites": [
{
"siteKey": "Site1",
"sources": [
{
"energyTypeId": 1,
"eanNumber": "SRC001"
},
{
"energyTypeId": 2,
"eanNumber": "SRC002"
},
{
"energyTypeId": 2,
"eanNumber": "SRC003"
}
]
},
{
"siteKey": "Site2"
}
],
"users": [
{
"emailAddress": "user1@gmail.com",
"language": "FR",
"sites": "Name = Site1",
"sources": [
{
"sourceEan": "Name = SRC001"
},
{
"sourceEan": "EanNumber = SRC002"
}
]
}
]
}
List operation
This operation tells the MDE to treat the values provided in the Master Data Upload file as a discrete list of business keys separated by the string specified in Value and to fill the destination list with the corresponding Insights entities. If Value is empty, MDE will use '','' as separator.
- The separator is case sensitive
- The separator must be a string that does not appear in any of the business keys provided in the list
- The separator may contain more than one character: the separator “my_sep” will correctly split “SITE1 my_sep SITE2”
- Pay attention not to surround the business keys by single or double quotes (e.g. ‘Site1’,’Site 2’) because the quotes will be considered as being part of the business key
- The LIST mode is compatible with UserSitesAndSourcesMergeMode mode, meaning that the list content can be appended to an already existing list.
- LIST and QUERY modes can't be mixed in a template
Important
This transformation is only valid for ''opUser.Sites'' and ''opUser.Sources'' identifiers
{
"template": {
"account": {
"id": 0,
"Name": ""
},
"config": {
"whatif": false,
"UserSitesAndSourcesMergeMode": "Merge"
},
"opSite": {
"rootPath": "sites[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "siteKey"
},
{
"opIdentifier": "opSite.Name",
"path": "siteKey"
},
{
"opIdentifier": "opSite.TimeZoneId",
"transform": {
"operation": "force",
"value": "Romance Standard Time"
}
},
{
"opIdentifier": "opSite.TypeId",
"transform": {
"operation": "force",
"value": 15
}
},
{
"opIdentifier": "opSource",
"path": "sources"
}
]
},
"opSource": {
"rootPath": "sites[*].sources[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.Name",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.EanNumber",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.GatewayId",
"transform": {
"operation": "force",
"value": "168552"
}
},
{
"opIdentifier": "opSource.SourceTypeId",
"transform": {
"operation": "force",
"value": "92"
}
},
{
"opIdentifier": "opSource.GatewayTypeId",
"transform": {
"operation": "force",
"value": "36"
}
},
{
"opIdentifier": "opVariable",
"path": "variables"
},
{
"opIdentifier": "opSource.TimeZoneId",
"transform": {
"operation": "force",
"value": "UTC"
}
}
]
},
"opUser": {
"rootPath": "users[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.email",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.UserName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.FirstName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.LastName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.Roles",
"transform": {
"operation": "force",
"array": [
"User"
]
}
},
{
"opIdentifier": "opUser.Language",
"path": "language"
},
{
"opIdentifier": "opUser.Sites",
"path": "sites",
"transform": {
"operation": "list",
"value": ","
}
},
{
"opIdentifier": "opUser.Sources",
"path": "sources[*].sourceEan",
"transform": {
"operation": "list"
}
}
]
}
}
}
{
"sites": [
{
"siteKey": "Site1",
"sources": [
{
"energyTypeId": 1,
"eanNumber": "SRC001"
},
{
"energyTypeId": 2,
"eanNumber": "SRC002"
},
{
"energyTypeId": 2,
"eanNumber": "SRC003"
}
]
},
{
"siteKey": "Site2"
}
],
"users": [
{
"emailAddress": "user1@gmail.com",
"language": "FR",
"sites": "Site1,Site4",
"sources": [
{
"sourceEan": "SRC001"
},
{
"sourceEan": "SRC002"
}
]
}
]
}
Implicit List mode
The implicit list mode is the default mode when no transformation ([Query](template_transformation#Query operation) or [List](template_transformation#List operation)) is specified. All values retrieved from the Path are interpreted as entity business key.
The path is relative to the //user[*]// and indicates the property name holding the business keys.
{
"template": {
"account": {
"id": 0,
"Name": ""
},
"config": {
"whatif": false,
"UserSitesAndSourcesMergeMode": "Merge"
},
"opSite": {
"rootPath": "sites[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "siteKey"
},
{
"opIdentifier": "opSite.Name",
"path": "siteKey"
},
{
"opIdentifier": "opSite.TimeZoneId",
"transform": {
"operation": "force",
"value": "Romance Standard Time"
}
},
{
"opIdentifier": "opSite.TypeId",
"transform": {
"operation": "force",
"value": 15
}
},
{
"opIdentifier": "opSource",
"path": "sources"
}
]
},
"opSource": {
"rootPath": "sites[*].sources[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.Name",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.EanNumber",
"path": "eanNumber"
},
{
"opIdentifier": "opSource.GatewayId",
"transform": {
"operation": "force",
"value": "168552"
}
},
{
"opIdentifier": "opSource.SourceTypeId",
"transform": {
"operation": "force",
"value": "92"
}
},
{
"opIdentifier": "opSource.GatewayTypeId",
"transform": {
"operation": "force",
"value": "36"
}
},
{
"opIdentifier": "opVariable",
"path": "variables"
},
{
"opIdentifier": "opSource.TimeZoneId",
"transform": {
"operation": "force",
"value": "UTC"
}
}
]
},
"opUser": {
"rootPath": "users[*]",
"items": [
{
"opIdentifier": "opKey",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.email",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.UserName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.FirstName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.LastName",
"path": "emailAddress"
},
{
"opIdentifier": "opUser.Roles",
"transform": {
"operation": "force",
"array": [
"User"
]
}
},
{
"opIdentifier": "opUser.Language",
"path": "language"
},
{
"opIdentifier": "opUser.Sites",
"path": "sites[*].siteKey"
},
{
"opIdentifier": "opUser.Sources",
"path": "sources[*].sourceEan"
}
]
}
}
}
{
"sites": [
{
"siteKey": "Site1",
"sources": [
{
"energyTypeId": 1,
"eanNumber": "SRC001"
},
{
"energyTypeId": 2,
"eanNumber": "SRC002"
},
{
"energyTypeId": 2,
"eanNumber": "SRC003"
}
]
},
{
"siteKey": "Site2"
}
],
"users": [
{
"emailAddress": "user1@gmail.com",
"language": "FR",
"sites": [
{
"siteKey": "Site1"
}
],
"sources": [
{
"sourceEan": "SRC001"
},
{
"sourceEan": "SRC002"
}
]
}
]
}