json - JOLT 将 JSON 分成更小的部分 - 更新 2021/06/08

Question

我在 Nifi 中获取了一些收集 JSON 的数据，但它需要插入的表具有不同的格式。
我无法将 JSON 结构解构为更小的部分 - 陷入第二个似乎是两部分或三部分的转变。有什么想法可以让我进入下一步吗？
原始 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 规范:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "timestamp": "[&1].timestamp",
        "inverterId": "[&1].inverterId",
        "systemKey": "[&1].systemKey",
        "I_DC*_*": "[&1].&"
      }
    }
  }
]

以上让我至少过滤了我需要的字段，但现在需要从 I_DC*_* 字段创建一个新字段(stringId)，并将原始 json 中的值放入“值”字段。
这是最后所需的输出:

[
  {
    "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  
  }
]

如果您在 Nifi 中没有 Jolt 的情况下有任何提示可以做到这一点，请告诉我 - 现在忙于尝试，因为今天放弃了 Jolt。
谢谢!
编辑 2021/06/08:
我设法使用这个规范来提取 I_DC*_* 字段并将它们放在我需要的位置。我仍在努力将inverterId、timestamp 和systemKey 保存在每个数组中，就像上面显示的所需输出一样。任何提示？
规范:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "I_DC*_*": "[].&"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "$": "[#3].stringId",
          "@": "[#3].value"
        }
      }
    }
  }
]

输出:

[
  {
    "stringId": "I_DC1_1",
    "value": 7.191999999999999
  },
  {
    "stringId": "I_DC1_2",
    "value": 6.869999999999999
  },
  {
    "stringId": "I_DC2_1",
    "value": 7.194
  }
]

Answer 1

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "timestamp": "[&1].timestamp",
        "inverterId": "[&1].inverterId",
        "systemKey": "[&1].systemKey",
        "I_DC*_*": "[&1].&"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "timestamp": "[&1].timestamp",
        "inverterId": "[&1].inverterId",
        "systemKey": "[&1].systemKey",
        "*": "[&1].keysToPivot.&"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "keysToPivot": {
          "*": {
            "$": "[&3].[#2].stringId",
            "@": "[&3].[#2].value",
            "@(2,timestamp)": "[&3].[#2].timestamp",
            "@(2,inverterId)": "[&3].[#2].inverterId",
            "@(2,systemKey)": "[&3].[#2].systemKey"
          }
        }
      }
    }
  }
]

请测试它，如果有任何问题，请告诉我。