NiFi将Avro转换为具有嵌套数组的JSON数组格式。

huangapple
go评论58阅读模式
英文:

NiFi convert avro to JSON array format with nested array

问题

以下是请求的JSON格式更改的部分：

[
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0",
    "itemDetails": [
      {
        "PARTNUM": "015935966",
        "PRICE": "42",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "000756009",
        "PRICE": "32",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "012179293",
        "PRICE": "99.95",
        "QUANTITY": "1"
      }
    ]
  },
  {
    "FIELD2": "25008",
    "MEMBER_ID": "874221898",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "183.80",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0",
    "itemDetails": [
      {
        "PARTNUM": "013519828",
        "PRICE": "16.95",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "012625445",
        "PRICE": "5.95",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "017219152",
        "PRICE": "54.95",
        "QUANTITY": "1"
      }
    ]
  }
]

请告诉我如果您需要进一步的帮助。

英文:

New to Nifi and looking to see if we can convert results of SQL to JSON in the requested format.
SQL result is :

member_id field2 total tax ship partnum price qty
874450963 24017 173.95 0 0 015935966 42 1
874450963 24017 173.95 0 0 000756009 32 1
874450963 24017 173.95 0 0 012179293 99.95 1

The out of the box result looks like this using either ConvertRecord or ConvertAvroToJSON process:

[
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "PARTNUM": "015935966",
    "PRICE": "42",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  },
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "PARTNUM": "000756009",
    "PRICE": "32",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  },
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "PARTNUM": "012179293",
    "PRICE": "99.95",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  }
]

Request is to group these and have the partnum, price and quantity as an array like this:

[
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0",
    "itemDetails": [
      {
        "PARTNUM": "015935966",
        "PRICE": "42",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "000756009",
        "PRICE": "32",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "012179293",
        "PRICE": "99.95",
        "QUANTITY": "1"
      }
    ]
  }
]

How do we do this?

Thanks!

Searched forum and didn't see anything similar.

New Scenario:

[
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "PARTNUM": "015935966",
    "PRICE": "42",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  },
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "PARTNUM": "000756009",
    "PRICE": "32",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  },
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "PARTNUM": "012179293",
    "PRICE": "99.95",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  },
  {
    "FIELD2": "25008",
    "MEMBER_ID": "874221898",
    "PARTNUM": "013519828",
    "PRICE": "16.95",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "83.80",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  },
  {
    "FIELD2": "25008",
    "MEMBER_ID": "874221898",
    "PARTNUM": "012625445",
    "PRICE": "5.95",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "83.80",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  },
  {
    "FIELD2": "25008",
    "MEMBER_ID": "874221898",
    "PARTNUM": "017219152",
    "PRICE": "54.95",
    "QUANTITY": "1",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "83.80",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0"
  }
]

Result should be:

[
  {
    "FIELD2": "24017",
    "MEMBER_ID": "874450963",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "173.95",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0",
    "itemDetails": [
      {
        "PARTNUM": "015935966",
        "PRICE": "42",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "000756009",
        "PRICE": "32",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "012179293",
        "PRICE": "99.95",
        "QUANTITY": "1"
      }
    ]
  },
  {
    "FIELD2": "25008",
    "MEMBER_ID": "874221898",
    "TIMEPLACED": null,
    "TOTALPRODUCT": "183.80",
    "TOTALSHIPPING": "0",
    "TOTALTAX": "0",
    "itemDetails": [
      {
        "PARTNUM": "013519828",
        "PRICE": "16.95",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "012625445",
        "PRICE": "5.95",
        "QUANTITY": "1"
      },
      {
        "PARTNUM": "017219152",
        "PRICE": "54.95",
        "QUANTITY": "1"
      }
    ]
  }
]

答案1

得分: 1

以下是翻译好的部分：

One option would be carrying out this conversion by using JoltTransformJSON process with the following specification :
一个选项是使用 JoltTransformJSON 进程并使用以下规范执行此转换：

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "&", // form arrays for the attibutes with keys other than below ones 
        "PARTNUM|PRICE|QUANTITY": "itemDetails[&1].&"
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": "ONE", // pick only one of the repeating components 
      "itemDetails": "MANY"
    }
  },
  {
    "operation": "sort"
  }
]

Edit : You can handle the new case through determining an attribute as a key id such as FIELD2 or MEMBER_ID( I picked FIELD2 ) such as
编辑：您可以通过确定一个属性作为键 id 来处理新情况，例如 FIELD2 或 MEMBER_ID（我选择了 FIELD2），例如

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@1,FIELD2.&",
        "PARTNUM|PRICE|QUANTITY": "@1,FIELD2.itemDetails[&1].&"
      }
    }
  },
  { // get rid of object keys
    "operation": "shift",
    "spec": {
      "*": ""
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE", // pick only one of the repeating components 
        "itemDetails": "MANY"
      }
    }
  },
  { // just to order the attributes 
    "operation": "sort"
  },
  {// get rid of redundantly generated null values
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  }
]
英文:

One option would be carrying out this conversion by using JoltTransformJSON process with the following specification :

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "&", // form arrays for the attibutes with keys other than below ones 
        "PARTNUM|PRICE|QUANTITY": "itemDetails[&1].&"
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": "ONE", // pick only one of the repeating components 
      "itemDetails": "MANY"
    }
  },
  {
    "operation": "sort"
  }
]

Edit : You can handle the new case through determining an attribute as a key id such as FIELD2 or MEMBER_ID( I picked FIELD2 ) such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@1,FIELD2.&",
        "PARTNUM|PRICE|QUANTITY": "@1,FIELD2.itemDetails[&1].&"
      }
    }
  },
  { // get rid of object keys
    "operation": "shift",
    "spec": {
      "*": ""
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE", // pick only one of the repeating components 
        "itemDetails": "MANY"
      }
    }
  },
  { // just to order the attributes 
    "operation": "sort"
  },
  {// get rid of redundantly generated null values
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  }
]

huangapple
  • 本文由 发表于 2023年2月14日 00:06:55
  • 转载请务必保留本文链接：https://go.coder-hub.com/75438388.html
  • apache-nifi
  • jolt
  • json
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定