I'm getting some data in Nifi that collects JSON but the table that it needs to be inserted in has a different format.
I'm having trouble to deconstruct a JSON structure into smaller pieces - getting stuck with the second what seems like a two or three-part shift. Any ideas to get me to the next step?
Original JSON:
[
{
"inverterId": "Id159946.3",
"timestamp": "2021-06-03 10:35:00.000",
"COS_PHI": 0.998,
"E_DAY": 85.6419916666667,
"E_INT": 1.9674496666666663,
"E_INT_N": 0.05,
"E_TOTAL": 1142,
"ERROR1": 0,
"F_AC": 50.05200000000001,
"I_AC": 96.96799999999999,
"I_AC1": 32.322,
"I_AC2": 32.318,
"I_AC3": 32.327999999999996,
"I_DC": 42.013999999999996,
"I_DC1": 14.184000000000001,
"I_DC1_1": 7.191999999999999,
"I_DC1_2": 6.869999999999999,
"I_DC2": 13.898,
"I_DC2_1": 7.194,
"I_DC2_2": 6.593999999999999,
"I_DC3": 13.931999999999999,
"I_DC3_1": 7.03,
"I_DC3_2": 6.798,
"I_DC4": null,
"I_DC4_1": null,
"I_DC4_2": null,
"I_DC5": null,
"I_DC5_1": null,
"I_DC5_2": null,
"OT_AC_TOTAL": 94,
"P_AC": 23609.395999999997,
"P_AC_N": 0.62,
"P_DC": 24096.873999999996,
"Q_AC": 953.1700000000001,
"QS_CI": 100,
"QS_RX": 1116,
"QS_TX": 1116,
"R_ISO": 20000,
"STATE1": 0,
"STATE2": 131073,
"T_WR": 35.394,
"U_AC": 243.76000000000002,
"U_AC1": 243.628,
"U_AC2": 244.3,
"U_AC3": 243.35199999999998,
"U_DC": 573.1573333333333,
"U_DC1": 570.628,
"U_DC2": 572.452,
"U_DC3": 576.3919999999999,
"U_DC4": null,
"U_DC5": null,
"systemKey": "K9V2Q"
},
{
"inverterId": "Id159946.2",
"timestamp": "2021-06-03 10:40:00.000",
"COS_PHI": 0.998,
"E_DAY": 118.47494700000001,
"E_INT": 1.7862204999999998,
"E_INT_N": 0.03,
"E_TOTAL": 1270,
"ERROR1": 0,
"F_AC": 50.038,
"I_AC": 91.154,
"I_AC1": 30.296,
"I_AC2": 30.392000000000003,
"I_AC3": 30.465999999999998,
"I_DC": 31.753999999999998,
"I_DC1": 4.226,
"I_DC1_1": 3.852,
"I_DC1_2": 0.378,
"I_DC2": 4.088,
"I_DC2_1": 4.074,
"I_DC2_2": 0.04000000000000001,
"I_DC3": 8.074,
"I_DC3_1": 4.138,
"I_DC3_2": 3.898,
"I_DC4": 7.584,
"I_DC4_1": 3.806,
"I_DC4_2": 3.7119999999999997,
"I_DC5": 7.782,
"I_DC5_1": 3.972,
"I_DC5_2": 3.7439999999999998,
"OT_AC_TOTAL": 79,
"P_AC": 21434.645999999997,
"P_AC_N": 0.34,
"P_DC": 21971.543999999998,
"Q_AC": 901.4119999999999,
"QS_CI": 100,
"QS_RX": 1119,
"QS_TX": 1119,
"R_ISO": 20000,
"STATE1": 0,
"STATE2": 131073,
"T_WR": 41.766000000000005,
"U_AC": 240.552,
"U_AC1": 241.614,
"U_AC2": 239.54,
"U_AC3": 240.50199999999998,
"U_DC": 692.9512,
"U_DC1": 806.782,
"U_DC2": 599.358,
"U_DC3": 631.958,
"U_DC4": 817.722,
"U_DC5": 608.936,
"systemKey": "K9V2Q"
},
{
"inverterId": "Id159946.3",
"timestamp": "2021-06-03 10:40:00.000",
"COS_PHI": 0.998,
"E_DAY": 86.9375756666667,
"E_INT": 1.295584,
"E_INT_N": 0.03,
"E_TOTAL": 1143,
"ERROR1": 0,
"F_AC": 50.038,
"I_AC": 65.03800000000001,
"I_AC1": 21.674,
"I_AC2": 21.678,
"I_AC3": 21.686,
"I_DC": 28.126,
"I_DC1": 9.486,
"I_DC1_1": 4.824,
"I_DC1_2": 4.548,
"I_DC2": 9.234,
"I_DC2_1": 4.822,
"I_DC2_2": 4.2940000000000005,
"I_DC3": 9.406,
"I_DC3_1": 4.77,
"I_DC3_2": 4.524,
"I_DC4": null,
"I_DC4_1": null,
"I_DC4_2": null,
"I_DC5": null,
"I_DC5_1": null,
"I_DC5_2": null,
"OT_AC_TOTAL": 94,
"P_AC": 15547.008,
"P_AC_N": 0.41,
"P_DC": 15971.436000000002,
"Q_AC": 810.6179999999999,
"QS_CI": 100,
"QS_RX": 1122,
"QS_TX": 1122,
"R_ISO": 20000,
"STATE1": 0,
"STATE2": 131073,
"T_WR": 35.664,
"U_AC": 242.69133333333335,
"U_AC1": 242.72,
"U_AC2": 243.30200000000002,
"U_AC3": 242.05199999999996,
"U_DC": 566.1546666666667,
"U_DC1": 561.5440000000001,
"U_DC2": 567.3480000000001,
"U_DC3": 569.572,
"U_DC4": null,
"U_DC5": null,
"systemKey": "K9V2Q"
}
]
JOLT Spec:
[
{
"operation": "shift",
"spec": {
"*": {
"timestamp": "[&1].timestamp",
"inverterId": "[&1].inverterId",
"systemKey": "[&1].systemKey",
"I_DC*_*": "[&1].&"
}
}
}
]
The above lets me at least filter the fields I need but now needs a new field (stringId) to be created from the I_DC*_* fields and the value of that in the original json put into the "value" field.
This is the desired output at the end:
[
{
"timestamp": "2021-06-03 10:35:00.000",
"systemKey": "K9V2Q",
"inverterId": "Id159946.3",
"stringId": "I_DC1_1",
"value": 7.191999999999999
},{
"timestamp": "2021-06-03 10:35:00.000",
"systemKey": "K9V2Q",
"inverterId": "Id159946.3",
"stringId": "I_DC1_2",
"value": 6.869999999999999
},{
"timestamp": "2021-06-03 10:35:00.000",
"systemKey": "K9V2Q",
"inverterId": "Id159946.3",
"stringId": "I_DC2_1",
"value": 7.194
},{
"timestamp": "2021-06-03 10:35:00.000",
"systemKey": "K9V2Q",
"inverterId": "Id159946.3",
"stringId": "I_DC2_2",
"value": 6.593999999999999
}
]
If you have any tips to do this without Jolt in Nifi let me know - busy trying that now as gave up on Jolt for the day.
Thanks!
Edit 2021/06/08: I managed to use this spec to extract the I_DC*_* fields and get them in the position I need. I'm still struggling to keep the inverterId, timestamp and systemKey inside each of these arrays like the desired output shown above. Any hints?
Spec:
[
{
"operation": "shift",
"spec": {
"*": {
"I_DC*_*": "[].&"
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"*": {
"$": "[#3].stringId",
"@": "[#3].value"
}
}
}
}
]
Output:
[
{
"stringId": "I_DC1_1",
"value": 7.191999999999999
},
{
"stringId": "I_DC1_2",
"value": 6.869999999999999
},
{
"stringId": "I_DC2_1",
"value": 7.194
}
]