JSON转换:用JOLT展平包含空对象的复杂嵌套JSON数组

Question

我是JOLT转换的新手，我发现它在JSON转换中非常有用。但是当我遇到嵌套且复杂的JSON时，我感到困惑。

下面的JSON是一个嵌套且复杂的JsonArray，我需要将其转换为完全扁平的JsonArray。

JSON输入：

[
  {
    "Code": -1,
    "Name": "All",
    "PublisherTypes": [
      {
        "Code": 2,
        "Name": "General",
        "LetterTypes": []
      },
      {
        "Code": 3,
        "Name": "Financials",
        "LetterTypes": []
      }
    ]
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherTypes": [
      {
        "Code": 2,
        "Name": "General",
        "LetterTypes": [
          {
            "Id": 10,
            "Name": "Long Term Info",
            "Code": ""
          },
          {
            "Id": 20,
            "Name": "Short Term Info",
            "Code": ""
          }
        ]
      },
      {
        "Code": 3,
        "Name": "Financials",
        "LetterTypes": [
          {
            "Id": 101,
            "Name": "Total Income",
            "Code": ""
          },
          {
            "Id": 202,
            "Name": "Total Taxes",
            "Code": ""
          }
        ]
      }
    ]
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherTypes": [
      {
        "Code": 2,
        "Name": "General",
        "LetterTypes": [
          {
            "Id": 8,
            "Name": "Monthly Reoprt",
            "Code": ""
          },
          {
            "Id": 58,
            "Name": "Status Report",
            "Code": ""
          }
        ]
      },
      {
        "Code": 3,
        "Name": "Financials",
        "LetterTypes": [
          {
            "Id": 170,
            "Name": "Manager Level",
            "Code": ""
          },
          {
            "Id": 156,
            "Name": "Expert Level",
            "Code": ""
          }
        ]
      }
    ]
  }
]

如您所见，我们有一个空的对象“字母类型”：[]，但在其他Json对象中，“字母类型”有自己的对象并且不是空的。

下面的JSON是我的预期输出。

预期输出：

[
  {
    "Code": -1,
    "Name": "All",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": "",
    "LetterName": "",
    "LetterCode": ""
  },
  {
    "Code": -1,
    "Name": "All",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": "",
    "LetterName": "",
    "LetterCode": ""
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": 10,
    "LetterName": "Long Term Info",
    "LetterCode": ""
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": 20,
    "LetterName": "Short Term Info",
    "LetterCode": ""
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": 101,
    "LetterName": "Total Income",
    "LetterCode": ""
  },
  {
    "Code": 1,
    "Name": "Information",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": 202,
    "LetterName": "Total Taxes",
    "LetterCode": ""
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": 8,
    "LetterName": "Monthly Reoprt",
    "LetterCode": ""
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherCode": 2,
    "PublisherName": "General",
    "LetterId": 58,
    "LetterName": "Status Reoprt",
    "LetterCode": ""
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": 170,
    "LetterName": "Manager Level",
    "LetterCode": ""
  },
  {
    "Code": 2,
    "Name": "Reporting",
    "PublisherCode": 3,
    "PublisherName": "Financials",
    "LetterId": 156,
    "LetterName": "Expert Level",
    "LetterCode": ""
  }
]

我需要的是一个JOLT规范来为我生成上述输出，以便当“LetterTypes”为空时，它在输出中显示为空字符串("")值。那么，有人能为这个问题提供一个JOLT规范吗？

Answer 1

您可以在应用修改转换规范后使用以下移位转换，其中；

1. toInteger 将带引号的值转换为不带引号的整数（如果可以，例如“12”-

，以便为LetterType数组的每个属性使用默认null（“”）值填充空数组，例如

json prettyprint-override">[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": {
          "*": {
            "Letter*": ["=toInteger",
              [
                {
                  "Id": "",
                  "Name": "",
                  "Code": ""
                }
              ]
            ]
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*Types": { // * wildcard represents one or multiple characters, here "*Types" stands for "PublisherTypes"
          "*": {
            "*Types": {
              "*": {
                "@(4,Code)": "Code",
                "@(4,Name)": "Name", // going 4 levels up to grab the desired value
                "@(2,Code)": "PublisherCode",
                "@(2,Name)": "PublisherName", // going 2 levels up to grab the desired value
                "*": "&(2,1)&" // all attributes under the "LetterTypes" arrays, in &(2,1)& : 2 is for going 2 levels up, 1 represents grabbing the piece("Letter") where * is substituted, and the last & represents the value of the current key name("Code" or "Name") 
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[&].&2"
        }
      }
    }
  }
]

所有的属性都累积在最里面的部分，在这个部分中，在整个JSON值中重复次数最多。

在第一个规范中，确定了包含10个组件的所有单个数组，并将它们移动到第二个规范中的相应对象。