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

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

NiFi convert avro to JSON array format with nested array

问题

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

  1. [
  2.   {
  3.     "FIELD2": "24017",
  4.     "MEMBER_ID": "874450963",
  5.     "TIMEPLACED": null,
  6.     "TOTALPRODUCT": "173.95",
  7.     "TOTALSHIPPING": "0",
  8.     "TOTALTAX": "0",
  9.     "itemDetails": [
  10.       {
  11.         "PARTNUM": "015935966",
  12.         "PRICE": "42",
  13.         "QUANTITY": "1"
  14.       },
  15.       {
  16.         "PARTNUM": "000756009",
  17.         "PRICE": "32",
  18.         "QUANTITY": "1"
  19.       },
  20.       {
  21.         "PARTNUM": "012179293",
  22.         "PRICE": "99.95",
  23.         "QUANTITY": "1"
  24.       }
  25.     ]
  26.   },
  27.   {
  28.     "FIELD2": "25008",
  29.     "MEMBER_ID": "874221898",
  30.     "TIMEPLACED": null,
  31.     "TOTALPRODUCT": "183.80",
  32.     "TOTALSHIPPING": "0",
  33.     "TOTALTAX": "0",
  34.     "itemDetails": [
  35.       {
  36.         "PARTNUM": "013519828",
  37.         "PRICE": "16.95",
  38.         "QUANTITY": "1"
  39.       },
  40.       {
  41.         "PARTNUM": "012625445",
  42.         "PRICE": "5.95",
  43.         "QUANTITY": "1"
  44.       },
  45.       {
  46.         "PARTNUM": "017219152",
  47.         "PRICE": "54.95",
  48.         "QUANTITY": "1"
  49.       }
  50.     ]
  51.   }
  52. ]

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

英文:

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:

  1. [
  2.   {
  3.     "FIELD2": "24017",
  4.     "MEMBER_ID": "874450963",
  5.     "PARTNUM": "015935966",
  6.     "PRICE": "42",
  7.     "QUANTITY": "1",
  8.     "TIMEPLACED": null,
  9.     "TOTALPRODUCT": "173.95",
  10.     "TOTALSHIPPING": "0",
  11.     "TOTALTAX": "0"
  12.   },
  13.   {
  14.     "FIELD2": "24017",
  15.     "MEMBER_ID": "874450963",
  16.     "PARTNUM": "000756009",
  17.     "PRICE": "32",
  18.     "QUANTITY": "1",
  19.     "TIMEPLACED": null,
  20.     "TOTALPRODUCT": "173.95",
  21.     "TOTALSHIPPING": "0",
  22.     "TOTALTAX": "0"
  23.   },
  24.   {
  25.     "FIELD2": "24017",
  26.     "MEMBER_ID": "874450963",
  27.     "PARTNUM": "012179293",
  28.     "PRICE": "99.95",
  29.     "QUANTITY": "1",
  30.     "TIMEPLACED": null,
  31.     "TOTALPRODUCT": "173.95",
  32.     "TOTALSHIPPING": "0",
  33.     "TOTALTAX": "0"
  34.   }
  35. ]

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

  1. [
  2.   {
  3.     "FIELD2": "24017",
  4.     "MEMBER_ID": "874450963",
  5.     "TIMEPLACED": null,
  6.     "TOTALPRODUCT": "173.95",
  7.     "TOTALSHIPPING": "0",
  8.     "TOTALTAX": "0",
  9.     "itemDetails": [
  10.       {
  11.         "PARTNUM": "015935966",
  12.         "PRICE": "42",
  13.         "QUANTITY": "1"
  14.       },
  15.       {
  16.         "PARTNUM": "000756009",
  17.         "PRICE": "32",
  18.         "QUANTITY": "1"
  19.       },
  20.       {
  21.         "PARTNUM": "012179293",
  22.         "PRICE": "99.95",
  23.         "QUANTITY": "1"
  24.       }
  25.     ]
  26.   }
  27. ]

How do we do this?

Thanks!

Searched forum and didn't see anything similar.

New Scenario:

  1. [
  2.   {
  3.     "FIELD2": "24017",
  4.     "MEMBER_ID": "874450963",
  5.     "PARTNUM": "015935966",
  6.     "PRICE": "42",
  7.     "QUANTITY": "1",
  8.     "TIMEPLACED": null,
  9.     "TOTALPRODUCT": "173.95",
  10.     "TOTALSHIPPING": "0",
  11.     "TOTALTAX": "0"
  12.   },
  13.   {
  14.     "FIELD2": "24017",
  15.     "MEMBER_ID": "874450963",
  16.     "PARTNUM": "000756009",
  17.     "PRICE": "32",
  18.     "QUANTITY": "1",
  19.     "TIMEPLACED": null,
  20.     "TOTALPRODUCT": "173.95",
  21.     "TOTALSHIPPING": "0",
  22.     "TOTALTAX": "0"
  23.   },
  24.   {
  25.     "FIELD2": "24017",
  26.     "MEMBER_ID": "874450963",
  27.     "PARTNUM": "012179293",
  28.     "PRICE": "99.95",
  29.     "QUANTITY": "1",
  30.     "TIMEPLACED": null,
  31.     "TOTALPRODUCT": "173.95",
  32.     "TOTALSHIPPING": "0",
  33.     "TOTALTAX": "0"
  34.   },
  35.   {
  36.     "FIELD2": "25008",
  37.     "MEMBER_ID": "874221898",
  38.     "PARTNUM": "013519828",
  39.     "PRICE": "16.95",
  40.     "QUANTITY": "1",
  41.     "TIMEPLACED": null,
  42.     "TOTALPRODUCT": "83.80",
  43.     "TOTALSHIPPING": "0",
  44.     "TOTALTAX": "0"
  45.   },
  46.   {
  47.     "FIELD2": "25008",
  48.     "MEMBER_ID": "874221898",
  49.     "PARTNUM": "012625445",
  50.     "PRICE": "5.95",
  51.     "QUANTITY": "1",
  52.     "TIMEPLACED": null,
  53.     "TOTALPRODUCT": "83.80",
  54.     "TOTALSHIPPING": "0",
  55.     "TOTALTAX": "0"
  56.   },
  57.   {
  58.     "FIELD2": "25008",
  59.     "MEMBER_ID": "874221898",
  60.     "PARTNUM": "017219152",
  61.     "PRICE": "54.95",
  62.     "QUANTITY": "1",
  63.     "TIMEPLACED": null,
  64.     "TOTALPRODUCT": "83.80",
  65.     "TOTALSHIPPING": "0",
  66.     "TOTALTAX": "0"
  67.   }
  68. ]

Result should be:

  1. [
  2.   {
  3.     "FIELD2": "24017",
  4.     "MEMBER_ID": "874450963",
  5.     "TIMEPLACED": null,
  6.     "TOTALPRODUCT": "173.95",
  7.     "TOTALSHIPPING": "0",
  8.     "TOTALTAX": "0",
  9.     "itemDetails": [
  10.       {
  11.         "PARTNUM": "015935966",
  12.         "PRICE": "42",
  13.         "QUANTITY": "1"
  14.       },
  15.       {
  16.         "PARTNUM": "000756009",
  17.         "PRICE": "32",
  18.         "QUANTITY": "1"
  19.       },
  20.       {
  21.         "PARTNUM": "012179293",
  22.         "PRICE": "99.95",
  23.         "QUANTITY": "1"
  24.       }
  25.     ]
  26.   },
  27.   {
  28.     "FIELD2": "25008",
  29.     "MEMBER_ID": "874221898",
  30.     "TIMEPLACED": null,
  31.     "TOTALPRODUCT": "183.80",
  32.     "TOTALSHIPPING": "0",
  33.     "TOTALTAX": "0",
  34.     "itemDetails": [
  35.       {
  36.         "PARTNUM": "013519828",
  37.         "PRICE": "16.95",
  38.         "QUANTITY": "1"
  39.       },
  40.       {
  41.         "PARTNUM": "012625445",
  42.         "PRICE": "5.95",
  43.         "QUANTITY": "1"
  44.       },
  45.       {
  46.         "PARTNUM": "017219152",
  47.         "PRICE": "54.95",
  48.         "QUANTITY": "1"
  49.       }
  50.     ]
  51.   }
  52. ]

答案1

得分: 1

以下是翻译好的部分：

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

  1. [
  2.   {
  3.     "operation": "shift",
  4.     "spec": {
  5.       "*": {
  6.         "*": "&", // form arrays for the attibutes with keys other than below ones 
  7.         "PARTNUM|PRICE|QUANTITY": "itemDetails[&1].&"
  8.       }
  9.     }
  10.   },
  11.   {
  12.     "operation": "cardinality",
  13.     "spec": {
  14.       "*": "ONE", // pick only one of the repeating components 
  15.       "itemDetails": "MANY"
  16.     }
  17.   },
  18.   {
  19.     "operation": "sort"
  20.   }
  21. ]

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），例如

  1. [
  2.   {
  3.     "operation": "shift",
  4.     "spec": {
  5.       "*": {
  6.         "*": "@1,FIELD2.&",
  7.         "PARTNUM|PRICE|QUANTITY": "@1,FIELD2.itemDetails[&1].&"
  8.       }
  9.     }
  10.   },
  11.   { // get rid of object keys
  12.     "operation": "shift",
  13.     "spec": {
  14.       "*": ""
  15.     }
  16.   },
  17.   {
  18.     "operation": "cardinality",
  19.     "spec": {
  20.       "*": {
  21.         "*": "ONE", // pick only one of the repeating components 
  22.         "itemDetails": "MANY"
  23.       }
  24.     }
  25.   },
  26.   { // just to order the attributes 
  27.     "operation": "sort"
  28.   },
  29.   {// get rid of redundantly generated null values
  30.     "operation": "modify-overwrite-beta",
  31.     "spec": {
  32.       "*": "=recursivelySquashNulls"
  33.     }
  34.   }
  35. ]
英文:

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

  1. [
  2.   {
  3.     "operation": "shift",
  4.     "spec": {
  5.       "*": {
  6.         "*": "&", // form arrays for the attibutes with keys other than below ones 
  7.         "PARTNUM|PRICE|QUANTITY": "itemDetails[&1].&"
  8.       }
  9.     }
  10.   },
  11.   {
  12.     "operation": "cardinality",
  13.     "spec": {
  14.       "*": "ONE", // pick only one of the repeating components 
  15.       "itemDetails": "MANY"
  16.     }
  17.   },
  18.   {
  19.     "operation": "sort"
  20.   }
  21. ]

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

  1. [
  2.   {
  3.     "operation": "shift",
  4.     "spec": {
  5.       "*": {
  6.         "*": "@1,FIELD2.&",
  7.         "PARTNUM|PRICE|QUANTITY": "@1,FIELD2.itemDetails[&1].&"
  8.       }
  9.     }
  10.   },
  11.   { // get rid of object keys
  12.     "operation": "shift",
  13.     "spec": {
  14.       "*": ""
  15.     }
  16.   },
  17.   {
  18.     "operation": "cardinality",
  19.     "spec": {
  20.       "*": {
  21.         "*": "ONE", // pick only one of the repeating components 
  22.         "itemDetails": "MANY"
  23.       }
  24.     }
  25.   },
  26.   { // just to order the attributes 
  27.     "operation": "sort"
  28.   },
  29.   {// get rid of redundantly generated null values
  30.     "operation": "modify-overwrite-beta",
  31.     "spec": {
  32.       "*": "=recursivelySquashNulls"
  33.     }
  34.   }
  35. ]

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:

确定