使用JOLT Transformation实现JSON数据Pivot行转列转换咨询

源数据结构 #

[
  {
    "name": "Buy",
    "List": [
      {
        "x": "7/8/2021",
        "y": 462853
      },
      {
        "x": "7/9/2021",
        "y": 462777
      },
      {
        "x": "7/10/2021",
        "y": 462701
      }
    ]
  },
  {
    "name": "Statistical",
    "List": [
      {
        "x": "7/8/2021",
        "y": 462853
      },
      {
        "x": "7/9/2021",
        "y": 462777
      },
      {
        "x": "7/10/2021",
        "y": 462701
      }
    ]
  },
  {
    "name": "Sell",
    "List": [
      {
        "x": "7/8/2021",
        "y": 462853
      },
      {
        "x": "7/9/2021",
        "y": 462777
      },
      {
        "x": "7/10/2021",
        "y": 462701
      }
    ]
  }
]

预期输出结构 #

[
  {
     "Date": "7/8/2021",
     "Buy": 462853,
     "Statistical": 462853,
     "Sell": 462853
  },
  {
     "Date": "7/9/2021",
     "Buy": 462777,
     "Statistical": 462777,
     "Sell": 462777
  },
  {
     "Date": "7/10/2021",
     "Buy": 462701,
     "Statistical": 462701,
     "Sell": 462701
  }
]

完整JOLT转换规则 #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "List": {
          "*": {
            "y": "@(1,x).@(2,name)",
            "x": "@(1,x).Date"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]

规则说明 #

• 第一步shift操作核心是按日期x分组，将外层的name字段作为分组内的键，对应y为值，同时为每个分组写入Date字段
• 第二步shift操作将分组后外层以日期为key的对象转换为数组格式，即得到目标结构

内容的提问来源于stack exchange，提问作者Sajad Hamreh