颠簸分组 [英] Jolt grouping together
问题描述
我在JSON中输入了此内容，但在将内容归类时遇到困难:
I have this input in JSON, I am having difficulty grouping things together:
[
{
"PK": "123",
"SURNAME": "CHEN",
"SEX": "F",
"DATE_OF_BIRTH": "1962-08-29 00:00:00.0",
"PHONE_TYPE": "05",
"PHONE_NO": "12312312",
"OPERATION": "INSERT",
}, {
"PK": "123",
"SURNAME": "CHEN",
"SEX": "F",
"DATE_OF_BIRTH": "1962-08-29 00:00:00.0",
"PHONE_TYPE": "04",
"PHONE_NO": "78787878",
"OPERATION": "UPDATE"
},{
"PK": "456",
"SURNAME": "DEV",
"SEX": "M",
"DATE_OF_BIRTH": "1953-06-06 00:00:00.0",
"PHONE_TYPE": "05",
"PHONE_NO": "34343434",
"OPERATION": "INSERT"
}, {
"CLIENT_ID": "456",
"SURNAME": "DEV",
"SEX": "M",
"DATE_OF_BIRTH": "1953-06-06 00:00:00.0",
"PHONE_TYPE": "02",
"PHONE_NO": "56565656",
"OPERATION": "DELETE",
}
]
这是预期的输出:
{
"Customers": [{
"MatchingProfile": {
"CustomerNumber": "", // leave blank
"DBType": "Oracle",
"DBKey": "123",
"LastName": "CHEN",
"Gender": "Female",
"Birthdate": "1962-08-29",
},
"Contacts": [{
"ContactType": "Fax",
"CountryCode": "", // leave blank
"Phone_Number": "12312312",
"Status": "Active"
}, {
"ContactType": "Mobile",
"CountryCode": "", // leave blank
"PhoneNumber": "78787878",
"Status": "Active"
}
]
},{
"MatchingProfile": {
"CustomerNumber": "", // leave blank
"DBType": "Oracle",
"DBKey": "456",
"LastName": "DEV",
"Gender": "Male",
"Birthdate": "1953-06-06",
},
"Contacts": [{
"ContactType": "Fax",
"CountryCode": "", // leave blank
"PhoneNumber": "34343434",
"Status": "Active"
}, {
"ContactType": "Office",
"CountryCode": "", // leave blank
"PhoneNumber": "56565656",
"Status": "Inactive"
}
]
}
]
}
输入的SEX为"M"，"F"，以及其他一些编码值.输出Gender的相应值是"Male"，"Female"，否则保留"(空白). (不要指责我性别偏见，我知道，这是一个项目要求，好吗?不是我的电话)
The SEX from input is "M", "F", plus some other coded values. Corresponding values for the output Gender is "Male", "Female" and left "" (blank) otherwise. (Don't accuse me of being gender-biased, I know, this is a project requirement, okay? Not my call)
来自输入的插入"和更新"的操作将是对应的状态:活动"；对于删除"，它将为状态:无效".
The OPERATION from input that is "INSERT" and "UPDATE" will be a corresponding Status: "Active" ; for "DELETE" it will be Status : "Inactive".
加上出生日期"输出将被截断为DATE_OF_BIRTH，减去时间.
Plus the Birthdate output is truncated equivalent of DATE_OF_BIRTH, minus the time.
PHONE_TYPE如下:02-办公室"，04-移动"，05-传真"(我特意省掉了其他人).
The PHONE_TYPE are the following: 02 - "Office", 04 - "Mobile", 05 - "Fax" (I purposedly left out the others).
是否可以在Jolt中对此进行映射?可以显示规格吗?我是Jolt的新手，我有点困惑.这比Excel Pivot难10倍.
Is it possible to have a mapping for this in Jolt? Can you show a spec? I'm new with Jolt and I am bit confused. This is 10x harder than Excel Pivot.
推荐答案
这几乎与OOTB Jolt可以得到的接近.注意Jolt用于更改数据的结构，而不是对"PHONE_TYPE"之类的内容进行自定义数据映射:"04"表示传真".
This is pretty much as close as OOTB Jolt can get. Note Jolt is for changing the structure of your data, not doing custom data mappings of things like "PHONE_TYPE": "04" means "Fax".
转换后的输出
{
"Customers" : [ {
"MatchingProfile" : {
"DBKey" : "123",
"Gender" : "F",
"LastName" : "CHEN",
"Birthdate" : "1962-08-29 00:00:00.0",
"Contacts" : [ {
"ContactType" : "05",
"Phone_Number" : "12312312",
"Status" : "INSERT"
}, {
"ContactType" : "04",
"Phone_Number" : "78787878",
"Status" : "UPDATE"
} ]
}
}, {
"MatchingProfile" : {
"DBKey" : "456",
"Gender" : "M",
"LastName" : "DEV",
"Birthdate" : "1953-06-06 00:00:00.0",
"Contacts" : [ {
"ContactType" : "05",
"Phone_Number" : "34343434",
"Status" : "INSERT"
}, {
"ContactType" : "02",
"Phone_Number" : "56565656",
"Status" : "DELETE"
} ]
}
} ]
}
震动规格
[
// first pivot by the value of SURNAME
{
"operation": "shift",
"spec": {
"*": { // for each item in the array
"SURNAME": { // match SURNAME
"*": { // match any value of SURNAME
"@2": "&[]" // copy the whole record from 2 levels up to the SURNAME as an array, so we know that in the next step it is always an array
}
}
}
}
},
{
"operation": "shift",
"spec": {
"*": { // match CHEN or DEV
"0": {
// only pull pk, sex, dob from the first entry of the SURNAME array so as to not duplicate output
"PK": "Customers[#3].MatchingProfile.DBKey",
"SEX": "Customers[#3].MatchingProfile.Gender",
"SURNAME": "Customers[#3].MatchingProfile.LastName",
"DATE_OF_BIRTH": "Customers[#3].MatchingProfile.Birthdate",
// this does mean that the PHONE_TYPE has to be dealt with twice
// once for the zeroth item, and then once again for the rest
"PHONE_TYPE": "Customers[#3].MatchingProfile.Contacts[0].ContactType",
"PHONE_NO": "Customers[#3].MatchingProfile.Contacts[0].Phone_Number",
"OPERATION": "Customers[#3].MatchingProfile.Contacts[0].Status"
},
"*": {
// handle PHONE_TYPE and friends for the other records
"PHONE_TYPE": "Customers[#3].MatchingProfile.Contacts[&1].ContactType",
"PHONE_NO": "Customers[#3].MatchingProfile.Contacts[&1].Phone_Number",
"OPERATION": "Customers[#3].MatchingProfile.Contacts[&1].Status"
}
}
}
}
]
如果您发现Jolt对于枢轴很有价值，并且结构发生了变化，那么最好的选择是修复"输入数据数组，也就是将映射"PHONE_TYPE":"04"映射为"Fax"，修剪00:00从生日开始:00，然后使用Jolt制作嵌套的"Customers [].MatchingProfile.Contacts []"结构.
If if you find Jolt valuable for the pivot and the structure change, then your best bet is to "fixup" your input data array, aka map "PHONE_TYPE": "04" to "Fax", trim the 00:00:00 from the birthday, and then use Jolt to make the nested "Customers[].MatchingProfile.Contacts[]" structure.
这篇关于颠簸分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
