重构JOLT代码以实现目标JSON输出格式

需要将嵌套结构的输入JSON转换为扁平化的数组结构，每个对象对应一个城市，包含所属的大都市圈、副大都市圈（若有）、州及区域信息。原JOLT代码未正确处理嵌套层级和无副大都市圈的情况，现提供修正后的JOLT配置。

输入JSON #

[
  {
    "id": "05301",
    "nome": "Região Metropolitana de Sorocaba",
    "UF": {
      "id": 35,
      "sigla": "SP",
      "nome": "São Paulo",
      "regiao": {
        "id": 3,
        "sigla": "SE",
        "nome": "Sudeste"
      }
    },
    "sub-regioes-metropolitanas": [
      {
        "id": "0530101",
        "nome": "Sub-região 1",
        "municipios": [
          { "id": 3500758, "nome": "Alambari" },
          { "id": 3507001, "nome": "Boituva" },
          { "id": 3510302, "nome": "Capela do Alto" },
          { "id": 3511508, "nome": "Cerquilho" },
          { "id": 3511607, "nome": "Cesário Lange" },
          { "id": 3522307, "nome": "Itapetininga" },
          { "id": 3525854, "nome": "Jumirim" },
          { "id": 3551108, "nome": "Sarapuí" },
          { "id": 3554003, "nome": "Tatuí" },
          { "id": 3554508, "nome": "Tietê" }
        ]
      }
    ]
  },
  {
    "id": "05101",
    "nome": "Região Metropolitana de Campinas",
    "UF": {
      "id": 35,
      "sigla": "SP",
      "nome": "São Paulo",
      "regiao": {
        "id": 3,
        "sigla": "SE",
        "nome": "Sudeste"
      }
    },
    "sub-regioes-metropolitanas": [],
    "municipios": [
      { "id": 3501608, "nome": "Americana" },
      { "id": 3503802, "nome": "Artur Nogueira" }
    ]
  }
]

预期输出JSON #

[
  [
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3500758,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3507001,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3510302,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3511508,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3511607,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3522307,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3525854,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3551108,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3554003,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05301",
      "nome_rgia_mptn": "Região Metropolitana de Sorocaba",
      "id_sub_rgia_mptn": "0530101",
      "nome_sub_rgia_mptn": "Sub-região 1",
      "id_mcpo": 3554508,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05101",
      "nome_rgia_mptn": "Região Metropolitana de Campinas",
      "id_sub_rgia_mptn": "",
      "nome_sub_rgia_mptn": "",
      "id_mcpo": 3501608,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    },
    {
      "id_rgia_mptn": "05101",
      "nome_rgia_mptn": "Região Metropolitana de Campinas",
      "id_sub_rgia_mptn": "",
      "nome_sub_rgia_mptn": "",
      "id_mcpo": 3503802,
      "id_uf": 35,
      "sgla_uf": "SP",
      "id_rgia": 3,
      "sgla_rgia": "SE"
    }
  ]
]

修正后的JOLT代码 #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "sub-regioes-metropolitanas": {
          "*": {
            "municipios": {
              "*": {
                "@4,id": "[0].&1.id_rgia_mptn",
                "@4,nome": "[0].&1.nome_rgia_mptn",
                "@2,id": "[0].&1.id_sub_rgia_mptn",
                "@2,nome": "[0].&1.nome_sub_rgia_mptn",
                "id": "[0].&1.id_mcpo",
                "@4,UF.id": "[0].&1.id_uf",
                "@4,UF.sigla": "[0].&1.sgla_uf",
                "@4,UF.regiao.id": "[0].&1.id_rgia",
                "@4,UF.regiao.sigla": "[0].&1.sgla_rgia"
              }
            }
          }
        },
        "municipios": {
          "*": {
            "@2,id": "[0].&1.id_rgia_mptn",
            "@2,nome": "[0].&1.nome_rgia_mptn",
            "id": "[0].&1.id_mcpo",
            "@2,UF.id": "[0].&1.id_uf",
            "@2,UF.sigla": "[0].&1.sgla_uf",
            "@2,UF.regiao.id": "[0].&1.id_rgia",
            "@2,UF.regiao.sigla": "[0].&1.sgla_rgia",
            "#": "[0].&1.id_sub_rgia_mptn",
            "#": "[0].&1.nome_sub_rgia_mptn"
          }
        }
      }
    }
  },
  {
    "operation": "default",
    "spec": {
      "*": {
        "*": {
          "id_sub_rgia_mptn": "",
          "nome_sub_rgia_mptn": ""
        }
      }
    }
  }
]

转换逻辑说明 #

1. Shift操作：
   • 处理有副大都市圈的情况：遍历sub-regioes-metropolitanas下的每个城市，通过@符号向上引用层级数据（如@4,id表示向上4层的id字段，即大都市圈的ID），将所有关联字段映射到输出数组的对应位置。
   • 处理无副大都市圈的情况：直接遍历顶层的municipios数组，同样引用上层的大都市圈、州及区域信息，同时用#设置副大都市圈字段的初始空值。
2. Default操作：确保所有对象的id_sub_rgia_mptn和nome_sub_rgia_mptn字段都有默认空值，避免缺失。

内容的提问来源于stack exchange，提问作者Mateus Costa Luz