NiFi convert avro to JSON array format with nested array

Question

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"
      }
    ]
  }
]

Answer 1

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"
    }
  }
]