如何扁平化嵌套JSON？含多层数组的JSON扁平化方案求助

我需要将一个包含产品、产品属性、关联商品及商品属性的嵌套JSON进行扁平化处理，把所有字段提升到根层级。目前只能处理顶层字段，对嵌套数组的扁平化毫无头绪，不确定是否需要分多步操作。

输入JSON #

{
  "product": {
    "attributes": [
      {
        "values": [
          {
            "unit_of_measure": null,
            "language": null,
            "value": "changable"
          },
          {
            "unit_of_measure": null,
            "language": null,
            "value": "washable"
          }
        ],
        "id": "cleaning",
        "kind": "option"
      },
      {
        "values": [
          {
            "unit_of_measure": null,
            "language": null,
            "value": "myTest"
          }
        ],
        "id": "how_to_purchase",
        "kind": "String"
      }
    ],
    "title": "myTitle",
    "category": {
      "id": "47114712"
    },
    "items": [
      {
        "item_number": "4711",
        "attributes": [
          {
            "values": [
              {
                "unit_of_measure": null,
                "language": null,
                "value": "2024-01-25T09:11:49"
              }
            ],
            "id": "timestamp",
            "kind": "String"
          },
          {
            "values": [
              {
                "unit_of_measure": "kg",
                "language": null,
                "value": 0.47
              }
            ],
            "id": "weight",
            "kind": "float"
          },
          {
            "values": [
              {
                "unit_of_measure": null,
                "language": null,
                "value": "orange"
              },
              {
                "unit_of_measure": null,
                "language": null,
                "value": "black"
              }
            ],
            "id": "colour",
            "kind": "option"
          }
        ]
      },
      {
        "item_number": "4712",
        "attributes": [
          {
            "values": [
              {
                "unit_of_measure": null,
                "language": null,
                "value": "2024-01-25T09:11:49"
              }
            ],
            "id": "timestamp",
            "kind": "String"
          },
          {
            "values": [
              {
                "unit_of_measure": null,
                "language": null,
                "value": true
              }
            ],
            "id": "valid",
            "kind": "boolean"
          }
        ]
      }
    ],
    "product_number": "myProduct123"
  }
}

期望扁平化输出 #

{
    "p-myProduct123-cleaning-0-unit_of_measure": null,
    "p-myProduct123-cleaning-0-language": null,
    "p-myProduct123-cleaning-0-value": "changable",
    "p-myProduct123-cleaning-1-unit_of_measure": null,
    "p-myProduct123-cleaning-1-language": null,
    "p-myProduct123-cleaning-1-value": "washable",
    "p-myProduct123-cleaning-id": "cleaning",
    "p-myProduct123-cleaning-kind": "option",
    "p-myProduct123-how_to_purchase-0-unit_of_measure": null,
    "p-myProduct123-how_to_purchase-0-language": null,
    "p-myProduct123-how_to_purchase-0-value": "myTest",
    "p-myProduct123-how_to_purchase-id": "how_to_purchase",
    "p-myProduct123-how_to_purchase-kind": "String",
    "p-myProduct123-title": "myTitle",
    "p-myProduct123-category": "47114712",
    "i-4711-timestamp-0-unit_of_measure": null,
    "i-4711-timestamp-0-language": null,
    "i-4711-timestamp-0-value": "2024-01-25T09:11:49",
    "i-4711-timestamp-id": "timestamp",
    "i-4711-timestamp-kind": "String",
    "i-4711-weight-0-unit_of_measure": "kg",
    "i-4711-weight-0-language": null,
    "i-4711-weight-0-value": 0.47,
    "i-4711-weight-id": "weight",
    "i-4711-weight-kind": "float",
    "i-4711-colour-0-unit_of_measure": null,
    "i-4711-colour-0-language": null,
    "i-4711-colour-0-value": "orange",
    "i-4711-colour-1-unit_of_measure": null,
    "i-4711-colour-1-language": null,
    "i-4711-colour-1-value": "black",
    "i-4711-colour-id": "colour",
    "i-4711-colour-kind": "option",
    "i-4712-timestamp-0-unit_of_measure": null,
    "i-4712-timestamp-0-language": null,
    "i-4712-timestamp-0-value": "2024-01-25T09:11:49",
    "i-4712-timestamp-id": "timestamp",
    "i-4712-timestamp-kind": "String",
    "i-4712-valid-0-unit_of_measure": null,
    "i-4712-valid-0-language": null,
    "i-4712-valid-0-value": true,
    "i-4712-valid-id": "valid",
    "i-4712-valid-kind": "boolean"
}

当前实现的部分Jolt转换规则 #

[
  {
    "operation": "shift",
    "spec": {
      "product": {
        "product_number": "@(1,product_number).product_number",
        "title": "@(1,product_number).title",
        "items": "@(1,product_number).items",
        "category": {
          "id": "@(2,product_number).category"
        },
        "attributes": "@(1,product_number).product_attributes"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "p-&1-&"
      }
    }
  }
]

完整Jolt转换解决方案 #

需要分多步shift操作处理不同层级的嵌套结构，最终生成符合要求的扁平化键值对：

[
  // 第一步：拆分原始结构为产品基础信息、产品属性、商品列表三个中间结构
  {
    "operation": "shift",
    "spec": {
      "product": {
        "product_number": "p_base.product_number",
        "title": "p_base.title",
        "category": {
          "id": "p_base.category"
        },
        "attributes": {
          "*": {
            "id": "p_attrs.[&1].id",
            "kind": "p_attrs.[&1].kind",
            "values": {
              "*": "p_attrs.[&2].values.[&1]"
            }
          }
        },
        "items": {
          "*": {
            "item_number": "items.[&1].item_number",
            "attributes": {
              "*": {
                "id": "items.[&2].i_attrs.[&1].id",
                "kind": "items.[&2].i_attrs.[&1].kind",
                "values": {
                  "*": "items.[&2].i_attrs.[&1].values.[&1]"
                }
              }
            }
          }
        }
      }
    }
  },
  // 第二步：扁平化产品基础字段
  {
    "operation": "shift",
    "spec": {
      "p_base": {
        "*": "p-@(1,product_number)-&"
      },
      "p_attrs": "*",
      "items": "*"
    }
  },
  // 第三步：扁平化产品属性
  {
    "operation": "shift",
    "spec": {
      "p-*": "&",
      "*": {
        "id": "p-@(2,p_base.product_number)-@(1,id)-id",
        "kind": "p-@(2,p_base.product_number)-@(1,id)-kind",
        "values": {
          "*": {
            "*": "p-@(3,p_base.product_number)-@(2,id)-&1-&"
          }
        }
      },
      "items": "*"
    }
  },
  // 第四步：拆分商品及商品属性结构
  {
    "operation": "shift",
    "spec": {
      "p-*": "&",
      "*": {
        "item_number": "i_num.[&1]",
        "i_attrs": {
          "*": {
            "id": "i_attrs.[&2].@(1,id).id",
            "kind": "i_attrs.[&2].@(1,id).kind",
            "values": {
              "*": {
                "*": "i_attrs.[&2].@(2,id).values.[&1]-&"
              }
            }
          }
        }
      }
    }
  },
  // 第五步：映射商品编号与属性的关联
  {
    "operation": "shift",
    "spec": {
      "p-*": "&",
      "i_num": {
        "*": {
          "@": "item_nums.[&1]"
        }
      },
      "i_attrs": {
        "*": {
          "*": {
            "id": "item_attrs.[&2].@(1,id).id",
            "kind": "item_attrs.[&2].@(1,id).kind",
            "values": {
              "*": "item_attrs.[&2].@(2,id).values.[&1]"
            }
          }
        }
      }
    }
  },
  // 第六步：生成商品属性的扁平化键值对
  {
    "operation": "shift",
    "spec": {
      "p-*": "&",
      "item_nums": {
        "*": {
          "@": "item_nums.[&1]"
        }
      },
      "item_attrs": {
        "*": {
          "*": {
            "id": "i-@(2,item_nums[&1])-@(1,id)-id",
            "kind": "i-@(2,item_nums[&1])-@(1,id)-kind",
            "values": {
              "*": {
                "*": "i-@(3,item_nums[&2])-@(2,id)-&1-&"
              }
            }
          }
        }
      }
    }
  },
  // 清理临时辅助字段
  {
    "operation": "remove",
    "spec": {
      "item_nums": ""
    }
  }
]

规则说明 #

1. 第一步：将原始嵌套结构拆解为三个独立的中间模块，避免层级干扰，方便分步处理。
2. 第二步：优先处理产品基础字段，生成p-[产品编号]-[字段名]格式的键。
3. 第三步：遍历产品属性列表，将属性的id/kind以及多值values字段拼接成指定格式的扁平化键。
4. 第四至第六步：针对商品及商品属性重复类似逻辑，生成i-[商品编号]-[属性id]-[索引]-[字段名]格式的键，最后清理临时生成的辅助字段。

内容的提问来源于stack exchange，提问作者Katja Bürger