请求编写JOLT Spec实现复杂JSON的表与列映射转换

需求概述 #

需要将包含源表、目标表及列映射的输入JSON，转换为包含双向表关联、双向列映射的结构化输出，具体要求：

• 源表归到source对象下，目标表归到target对象下
• 源表新增mappedTargetTableNames字段，记录关联的目标表ID（格式：schemaName-tableName）
• 目标表新增mappedSourceTables字段，记录关联的源表ID
• 源列与目标列双向映射：源列关联所有对应目标列定义，目标列关联对应源列定义

输入JSON示例 #

{"sourceTables":[{"sourceSchemaName":"vsc","sourceTableName":"user","sourceTableColumnDefinitions":[{"sourceColumnName":"userId","sourceColumnAttributes":"","sourceColumnType":"varchar"}]},{"sourceSchemaName":"npp","sourceTableName":"profile","sourceTableColumnDefinitions":[{"sourceColumnName":"profileId","sourceColumnAttributes":"","sourceColumnType":"int"}]}],"targetTables":[{"targetSchemaName":"chr","targetTableName":"userAccount","targetTableColumnDefinitions":[{"sourceSchemaName":"vsc","sourceTableName":"user","sourceColumnName":"userId","targetColumn":"id","targetColumnAttributes":"","targetColumnType":"varchar"},{"sourceSchemaName":"npp","sourceTableName":"profile","sourceColumnName":"profileId","targetColumn":"pId","targetColumnAttributes":"","targetColumnType":"int"}]},{"targetSchemaName":"chr","targetTableName":"pIds","targetTableColumnDefinitions":[{"sourceSchemaName":"npp","sourceTableName":"profile","sourceColumnName":"profileId","targetColumn":"pId","targetColumnAttributes":"","targetColumnType":"int"}]}]}

期望输出JSON示例 #

{"source":{"npp-profile":{"id":"npp-profile","schemaName":"npp","tableName":"profile","mappedTargetTableNames":["chr-pIds","chr-userAccount"],"sourceColumnMap":{"profileId":{"sourceColumnDefinition":{"schemaName":"npp","tableName":"profile","columnName":"profileId","columnType":"int","columnAttributes":null},"mappedTargetColumnDefinition":[{"schemaName":"chr","tableName":"userAccount","columnName":"pId","columnType":"int","columnAttributes":null},{"schemaName":"chr","tableName":"pIds","columnName":"id","columnType":"int","columnAttributes":null}]}}},"vsc-user":{"id":"vsc-user","schemaName":"vsc","tableName":"user","mappedTargetTableNames":["chr-userAccount"],"sourceColumnMap":{"userId":{"sourceColumnDefinition":{"schemaName":"vsc","tableName":"user","columnName":"userId","columnType":"varchar","columnAttributes":null},"mappedTargetColumnDefinition":[{"schemaName":"chr","tableName":"userAccount","columnName":"id","columnType":"varchar","columnAttributes":null}]}}}},"target":{"chr-pIds":{"id":"chr-pIds","schemaName":"chr","tableName":"pIds","mappedSourceTables":["npp-profile"],"targetColumnMap":{"id":{"targetColumnDefinition":{"schemaName":"chr","tableName":"pIds","columnName":"id","columnType":"int","columnAttributes":null},"mappedSourceColumnDefinition":{"schemaName":"npp","tableName":"profile","columnName":"profileId","columnType":"int","columnAttributes":null}}}},"chr-userAccount":{"id":"chr-userAccount","schemaName":"chr","tableName":"userAccount","mappedSourceTables":["npp-profile","vsc-user"],"targetColumnMap":{"pId":{"targetColumnDefinition":{"schemaName":"chr","tableName":"userAccount","columnName":"pId","columnType":"int","columnAttributes":null},"mappedSourceColumnDefinition":{"schemaName":"npp","tableName":"profile","columnName":"profileId","columnType":"int","columnAttributes":null}}},"id":{"targetColumnDefinition":{"schemaName":"chr","tableName":"userAccount","columnName":"id","columnType":"varchar","columnAttributes":null},"mappedSourceColumnDefinition":{"schemaName":"vsc","tableName":"user","columnName":"userId","columnType":"varchar","columnAttributes":null}}}}}}

JOLT转换规格 #

[
  // 第一步：提取所有目标列映射关系，方便后续关联
  {
    "operation": "shift",
    "spec": {
      "targetTables": {
        "*": {
          "targetSchemaName": "tempTargetTableMap.&1.targetSchemaName",
          "targetTableName": "tempTargetTableMap.&1.targetTableName",
          "targetTableColumnDefinitions": {
            "*": {
              // 源表ID
              "@(2,targetSchemaName)-@(2,targetTableName)": "tempColumnMap.@(sourceSchemaName)-@(sourceTableName).@(sourceColumnName).mappedTargetColumns[]",
              "@(2,targetSchemaName)": "tempColumnMap.@(sourceSchemaName)-@(sourceTableName).@(sourceColumnName).mappedTargetColumns[&1].schemaName",
              "@(2,targetTableName)": "tempColumnMap.@(sourceSchemaName)-@(sourceTableName).@(sourceColumnName).mappedTargetColumns[&1].tableName",
              "targetColumn": "tempColumnMap.@(sourceSchemaName)-@(sourceTableName).@(sourceColumnName).mappedTargetColumns[&1].columnName",
              "targetColumnType": "tempColumnMap.@(sourceSchemaName)-@(sourceTableName).@(sourceColumnName).mappedTargetColumns[&1].columnType",
              "targetColumnAttributes": "tempColumnMap.@(sourceSchemaName)-@(sourceTableName).@(sourceColumnName).mappedTargetColumns[&1].columnAttributes",
              // 反向映射：目标列到源列
              "@(sourceSchemaName)-@(sourceTableName)": "tempReverseColumnMap.@(2,targetSchemaName)-@(2,targetTableName).@(targetColumn).mappedSourceColumn.schemaName",
              "@(sourceTableName)": "tempReverseColumnMap.@(2,targetSchemaName)-@(2,targetTableName).@(targetColumn).mappedSourceColumn.tableName",
              "@(sourceColumnName)": "tempReverseColumnMap.@(2,targetSchemaName)-@(2,targetTableName).@(targetColumn).mappedSourceColumn.columnName",
              "@(sourceColumnType)": "tempReverseColumnMap.@(2,targetSchemaName)-@(2,targetTableName).@(targetColumn).mappedSourceColumn.columnType",
              "@(sourceColumnAttributes)": "tempReverseColumnMap.@(2,targetSchemaName)-@(2,targetTableName).@(targetColumn).mappedSourceColumn.columnAttributes",
              // 收集源表关联的目标表ID
              "@(2,targetSchemaName)-@(2,targetTableName)": "tempSourceTargetMap.@(sourceSchemaName)-@(sourceTableName).mappedTargetTables[]",
              // 收集目标表关联的源表ID
              "@(sourceSchemaName)-@(sourceTableName)": "tempTargetSourceMap.@(2,targetSchemaName)-@(2,targetTableName).mappedSourceTables[]"
            }
          }
        }
      },
      // 保留源表原始数据
      "sourceTables": "sourceTables",
      // 保留目标表原始数据
      "targetTables": "targetTables"
    }
  },
  // 第二步：去重关联的表列表
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "tempSourceTargetMap": {
        "*": {
          "mappedTargetTables": "=unique"
        }
      },
      "tempTargetSourceMap": {
        "*": {
          "mappedSourceTables": "=unique"
        }
      }
    }
  },
  // 第三步：构建最终的source对象
  {
    "operation": "shift",
    "spec": {
      "sourceTables": {
        "*": {
          "sourceSchemaName": "source.@(sourceSchemaName)-@(sourceTableName).schemaName",
          "sourceTableName": "source.@(sourceSchemaName)-@(sourceTableName).tableName",
          "@(sourceSchemaName)-@(sourceTableName)": "source.@(sourceSchemaName)-@(sourceTableName).id",
          "sourceTableColumnDefinitions": {
            "*": {
              "sourceColumnName": "source.@(3,sourceSchemaName)-@(3,sourceTableName).sourceColumnMap.&.sourceColumnDefinition.columnName",
              "sourceColumnType": "source.@(3,sourceSchemaName)-@(3,sourceTableName).sourceColumnMap.&.sourceColumnDefinition.columnType",
              "sourceColumnAttributes": "source.@(3,sourceSchemaName)-@(3,sourceTableName).sourceColumnMap.&.sourceColumnDefinition.columnAttributes",
              "@(3,sourceSchemaName)": "source.@(3,sourceSchemaName)-@(3,sourceTableName).sourceColumnMap.&.sourceColumnDefinition.schemaName",
              "@(3,sourceTableName)": "source.@(3,sourceSchemaName)-@(3,sourceTableName).sourceColumnMap.&.sourceColumnDefinition.tableName"
            }
          },
          // 关联目标表列表
          "@(2,tempSourceTargetMap.@(sourceSchemaName)-@(sourceTableName).mappedTargetTables)": "source.@(sourceSchemaName)-@(sourceTableName).mappedTargetTableNames"
        }
      },
      // 关联源列对应的目标列
      "tempColumnMap": {
        "*": {
          "*": {
            "mappedTargetColumns": "source.&1.sourceColumnMap.&2.mappedTargetColumnDefinition"
          }
        }
      },
      // 保留临时数据用于后续构建target
      "tempReverseColumnMap": "tempReverseColumnMap",
      "tempTargetSourceMap": "tempTargetSourceMap",
      "targetTables": "targetTables"
    }
  },
  // 第四步：构建最终的target对象
  {
    "operation": "shift",
    "spec": {
      "targetTables": {
        "*": {
          "targetSchemaName": "target.@(targetSchemaName)-@(targetTableName).schemaName",
          "targetTableName": "target.@(targetSchemaName)-@(targetTableName).tableName",
          "@(targetSchemaName)-@(targetTableName)": "target.@(targetSchemaName)-@(targetTableName).id",
          "targetTableColumnDefinitions": {
            "*": {
              "targetColumn": "target.@(3,targetSchemaName)-@(3,targetTableName).targetColumnMap.&.targetColumnDefinition.columnName",
              "targetColumnType": "target.@(3,targetSchemaName)-@(3,targetTableName).targetColumnMap.&.targetColumnDefinition.columnType",
              "targetColumnAttributes": "target.@(3,targetSchemaName)-@(3,targetTableName).targetColumnMap.&.targetColumnDefinition.columnAttributes",
              "@(3,targetSchemaName)": "target.@(3,targetSchemaName)-@(3,targetTableName).targetColumnMap.&.targetColumnDefinition.schemaName",
              "@(3,targetTableName)": "target.@(3,targetSchemaName)-@(3,targetTableName).targetColumnMap.&.targetColumnDefinition.tableName"
            }
          },
          // 关联源表列表
          "@(2,tempTargetSourceMap.@(targetSchemaName)-@(targetTableName).mappedSourceTables)": "target.@(targetSchemaName)-@(targetTableName).mappedSourceTables"
        }
      },
      // 关联目标列对应的源列
      "tempReverseColumnMap": {
        "*": {
          "*": {
            "mappedSourceColumn": "target.&1.targetColumnMap.&2.mappedSourceColumnDefinition"
          }
        }
      },
      // 保留最终的source对象
      "source": "source"
    }
  },
  // 第五步：将空字符串替换为null
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": {
              "columnAttributes": ["=isNull", ""]
            }
          }
        }
      }
    }
  }
]

转换说明 #

1. 提取映射关系：通过shift操作提取所有列的双向映射、表的关联关系，存储到临时对象中，方便后续关联。
2. 去重表关联列表：使用modify-overwrite-beta的unique函数，去除关联表列表中的重复项。
3. 构建source对象：将源表数据整理为指定格式，关联临时存储的目标表列表和目标列映射。
4. 构建target对象：同理整理目标表数据，关联临时存储的源表列表和源列映射。
5. 空值处理：将空字符串的columnAttributes替换为null，匹配期望输出格式。

内容的提问来源于stack exchange，提问作者Balan Gurumurthi