多层级扁平JSON转嵌套JSON：Jolt分组转换遇阻求助

需求说明 #

需要将给定的扁平JSON数组，按以下规则转换为多层嵌套结构：

1. 先按bucket_id_1、bucket_id_2、bucket_id_3字段拆分出独立的bucket条目
2. 再按article_id对每个bucket下的内容分组，最终形成buckets -> articles -> items的三层嵌套结构

输入扁平JSON #

[
  {
    "item_attr1": "abc",
    "item_attr2": "123",
    "item_attr3": "123",
    "item_id": "12345",
    "bucket_attr1": 1919,
    "bucket_attr2": "abc",
    "bucket_attr3": 1922,
    "bucket_attr4": "abc",
    "bucket_id_1": "abc",
    "bucket_id_2": "def",
    "bucket_id_3": "ghi",
    "articleattribute1": "abc",
    "articleattribute2": "abc",
    "articleattribute3": "2233",
    "article_id": "123458"
  },
  {
    "item_attr1": "abc",
    "item_attr2": "123",
    "item_attr3": "123",
    "item_id": "543421",
    "bucket_attr1": 1919,
    "bucket_attr2": "abc",
    "bucket_attr3": 1922,
    "bucket_attr4": "abc",
    "bucket_id_1": "abc",
    "bucket_id_2": "mef",
    "articleattribute1": "abc",
    "articleattribute2": "abc",
    "articleattribute3": "2233",
    "article_id": "12345"
  }
]

期望输出结构 #

{
  "buckets": [
    {
      "bucket_id": "abc",
      "bucket_attr1": 1919,
      "bucket_attr2": "abc",
      "bucket_attr3": 1922,
      "bucket_attr4": "abc",
      "articles": [
        {
          "articleattribute1": "abc",
          "articleattribute2": "abc",
          "articleattribute3": "2233",
          "article_id": "12345",
          "items": [
            {
              "item_attr1": "abc",
              "item_attr2": "123",
              "item_attr3": "123",
              "item_id": "543421"
            }
          ]
        },
        {
          "articleattribute1": "abc",
          "articleattribute2": "abc",
          "articleattribute3": "2233",
          "article_id": "123458",
          "items": [
            {
              "item_attr1": "abc",
              "item_attr2": "123",
              "item_attr3": "123",
              "item_id": "12345"
            }
          ]
        }
      ]
    },
    {
      "bucket_id": "def",
      "bucket_attr1": 1919,
      "bucket_attr2": "abc",
      "bucket_attr3": 1922,
      "bucket_attr4": "abc",
      "articles": [
        {
          "articleattribute1": "abc",
          "articleattribute2": "abc",
          "articleattribute3": "2233",
          "article_id": "123458",
          "items": [
            {
              "item_attr1": "abc",
              "item_attr2": "123",
              "item_attr3": "123",
              "item_id": "12345"
            }
          ]
        }
      ]
    },
    {
      "bucket_id": "ghi",
      "bucket_attr1": 1919,
      "bucket_attr2": "abc",
      "bucket_attr3": 1922,
      "bucket_attr4": "abc",
      "articles": [
        {
          "articleattribute1": "abc",
          "articleattribute2": "abc",
          "articleattribute3": "2233",
          "article_id": "123458",
          "items": [
            {
              "item_attr1": "abc",
              "item_attr2": "123",
              "item_attr3": "123",
              "item_id": "12345"
            }
          ]
        }
      ]
    },
    {
      "bucket_id": "mef",
      "bucket_attr1": 1919,
      "bucket_attr2": "abc",
      "bucket_attr3": 1922,
      "bucket_attr4": "abc",
      "articles": [
        {
          "articleattribute1": "abc",
          "articleattribute2": "abc",
          "articleattribute3": "2233",
          "article_id": "12345",
          "items": [
            {
              "item_attr1": "abc",
              "item_attr2": "123",
              "item_attr3": "123",
              "item_id": "543421"
            }
          ]
        }
      ]
    }
  ]
}

Jolt变换规范 #

分三步完成转换：展开bucket条目、分组聚合、整理最终结构

第一步：展开每个bucket_id为独立条目 #

使用shift操作，将每个bucket_id_N字段拆分为独立的bucket对象，同时携带对应的bucket、article、item属性：

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "bucket_id_1": {
          "@2": {
            "bucket_id": "@",
            "bucket_attr1": "&3.bucket_attr1",
            "bucket_attr2": "&3.bucket_attr2",
            "bucket_attr3": "&3.bucket_attr3",
            "bucket_attr4": "&3.bucket_attr4",
            "articleattribute1": "&3.articleattribute1",
            "articleattribute2": "&3.articleattribute2",
            "articleattribute3": "&3.articleattribute3",
            "article_id": "&3.article_id",
            "item_attr1": "&3.item_attr1",
            "item_attr2": "&3.item_attr2",
            "item_attr3": "&3.item_attr3",
            "item_id": "&3.item_id"
          }
        },
        "bucket_id_2": {
          "@2": {
            "bucket_id": "@",
            "bucket_attr1": "&3.bucket_attr1",
            "bucket_attr2": "&3.bucket_attr2",
            "bucket_attr3": "&3.bucket_attr3",
            "bucket_attr4": "&3.bucket_attr4",
            "articleattribute1": "&3.articleattribute1",
            "articleattribute2": "&3.articleattribute2",
            "articleattribute3": "&3.articleattribute3",
            "article_id": "&3.article_id",
            "item_attr1": "&3.item_attr1",
            "item_attr2": "&3.item_attr2",
            "item_attr3": "&3.item_attr3",
            "item_id": "&3.item_id"
          }
        },
        "bucket_id_3": {
          "@2": {
            "bucket_id": "@",
            "bucket_attr1": "&3.bucket_attr1",
            "bucket_attr2": "&3.bucket_attr2",
            "bucket_attr3": "&3.bucket_attr3",
            "bucket_attr4": "&3.bucket_attr4",
            "articleattribute1": "&3.articleattribute1",
            "articleattribute2": "&3.articleattribute2",
            "articleattribute3": "&3.articleattribute3",
            "article_id": "&3.article_id",
            "item_attr1": "&3.item_attr1",
            "item_attr2": "&3.item_attr2",
            "item_attr3": "&3.item_attr3",
            "item_id": "&3.item_id"
          }
        }
      }
    }
  }
]

第二步：按bucket_id和article_id分组聚合 #

使用shift操作，将相同bucket_id的条目聚合，再在每个bucket下按article_id聚合items：

{
  "operation": "shift",
  "spec": {
    "*": {
      "*": {
        "bucket_id": {
          "@2": {
            "bucket_attr1": "buckets.[#2].bucket_attr1",
            "bucket_attr2": "buckets.[#2].bucket_attr2",
            "bucket_attr3": "buckets.[#2].bucket_attr3",
            "bucket_attr4": "buckets.[#2].bucket_attr4",
            "bucket_id": "buckets.[#2].bucket_id",
            "article_id": {
              "@2": {
                "articleattribute1": "buckets.[#4].articles.[#2].articleattribute1",
                "articleattribute2": "buckets.[#4].articles.[#2].articleattribute2",
                "articleattribute3": "buckets.[#4].articles.[#2].articleattribute3",
                "article_id": "buckets.[#4].articles.[#2].article_id",
                "item_attr1": "buckets.[#4].articles.[#2].items[#1].item_attr1",
                "item_attr2": "buckets.[#4].articles.[#2].items[#1].item_attr2",
                "item_attr3": "buckets.[#4].articles.[#2].items[#1].item_attr3",
                "item_id": "buckets.[#4].articles.[#2].items[#1].item_id"
              }
            }
          }
        }
      }
    }
  }
}

第三步：去重并整理数组结构 #

使用cardinality操作去除重复的bucket和article属性，确保每个bucket和article只保留一份属性：

{
  "operation": "cardinality",
  "spec": {
    "buckets": {
      "*": {
        "bucket_attr1": "ONE",
        "bucket_attr2": "ONE",
        "bucket_attr3": "ONE",
        "bucket_attr4": "ONE",
        "bucket_id": "ONE",
        "articles": {
          "*": {
            "articleattribute1": "ONE",
            "articleattribute2": "ONE",
            "articleattribute3": "ONE",
            "article_id": "ONE"
          }
        }
      }
    }
  }
}

完整Jolt变换链 #

将以上三步组合，完整的Jolt规范如下：

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "bucket_id_1": {
          "@2": {
            "bucket_id": "@",
            "bucket_attr1": "&3.bucket_attr1",
            "bucket_attr2": "&3.bucket_attr2",
            "bucket_attr3": "&3.bucket_attr3",
            "bucket_attr4": "&3.bucket_attr4",
            "articleattribute1": "&3.articleattribute1",
            "articleattribute2": "&3.articleattribute2",
            "articleattribute3": "&3.articleattribute3",
            "article_id": "&3.article_id",
            "item_attr1": "&3.item_attr1",
            "item_attr2": "&3.item_attr2",
            "item_attr3": "&3.item_attr3",
            "item_id": "&3.item_id"
          }
        },
        "bucket_id_2": {
          "@2": {
            "bucket_id": "@",
            "bucket_attr1": "&3.bucket_attr1",
            "bucket_attr2": "&3.bucket_attr2",
            "bucket_attr3": "&3.bucket_attr3",
            "bucket_attr4": "&3.bucket_attr4",
            "articleattribute1": "&3.articleattribute1",
            "articleattribute2": "&3.articleattribute2",
            "articleattribute3": "&3.articleattribute3",
            "article_id": "&3.article_id",
            "item_attr1": "&3.item_attr1",
            "item_attr2": "&3.item_attr2",
            "item_attr3": "&3.item_attr3",
            "item_id": "&3.item_id"
          }
        },
        "bucket_id_3": {
          "@2": {
            "bucket_id": "@",
            "bucket_attr1": "&3.bucket_attr1",
            "bucket_attr2": "&3.bucket_attr2",
            "bucket_attr3": "&3.bucket_attr3",
            "bucket_attr4": "&3.bucket_attr4",
            "articleattribute1": "&3.articleattribute1",
            "articleattribute2": "&3.articleattribute2",
            "articleattribute3": "&3.articleattribute3",
            "article_id": "&3.article_id",
            "item_attr1": "&3.item_attr1",
            "item_attr2": "&3.item_attr2",
            "item_attr3": "&3.item_attr3",
            "item_id": "&3.item_id"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "bucket_id": {
            "@2": {
              "bucket_attr1": "buckets.[#2].bucket_attr1",
              "bucket_attr2": "buckets.[#2].bucket_attr2",
              "bucket_attr3": "buckets.[#2].bucket_attr3",
              "bucket_attr4": "buckets.[#2].bucket_attr4",
              "bucket_id": "buckets.[#2].bucket_id",
              "article_id": {
                "@2": {
                  "articleattribute1": "buckets.[#4].articles.[#2].articleattribute1",
                  "articleattribute2": "buckets.[#4].articles.[#2].articleattribute2",
                  "articleattribute3": "buckets.[#4].articles.[#2].articleattribute3",
                  "article_id": "buckets.[#4].articles.[#2].article_id",
                  "item_attr1": "buckets.[#4].articles.[#2].items[#1].item_attr1",
                  "item_attr2": "buckets.[#4].articles.[#2].items[#1].item_attr2",
                  "item_attr3": "buckets.[#4].articles.[#2].items[#1].item_attr3",
                  "item_id": "buckets.[#4].articles.[#2].items[#1].item_id"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "buckets": {
        "*": {
          "bucket_attr1": "ONE",
          "bucket_attr2": "ONE",
          "bucket_attr3": "ONE",
          "bucket_attr4": "ONE",
          "bucket_id": "ONE",
          "articles": {
            "*": {
              "articleattribute1": "ONE",
              "articleattribute2": "ONE",
              "articleattribute3": "ONE",
              "article_id": "ONE"
            }
          }
        }
      }
    }
  }
]

说明 #

• 第一步把每个输入对象中的多个bucket_id拆成独立的临时对象，确保每个bucket_id对应一条完整的属性链
• 第二步通过[#N]索引语法，按bucket_id和article_id进行分组，将items聚合到对应的article下
• 第三步用cardinality去重，避免重复的bucket和article属性出现多次

内容的提问来源于stack exchange，提问作者Shashi Kiran GM