How to convert multiple related rows in CSV into nested JSON using Nifi?

Question

I have the following CSV: mobileusernamebill_idbill_dateproduct_idproduct_price9876543210JohnAB102020-12-23X345009876543210JohnAB102020-12-23X352309876543210JohnAB222020-11-14X897009999999999AliceAC542019-12-20X10109 And I want to convert them to the following structure: {    "users": [    {        "mobile": "9876543210",        "username": "John",        "bills": [        {            "bill_id": "AB10",            "bill_date": "2020-12-23",            "products": [{                "product_id": "X34",                "product_price": "500",            },            {                "product_id": "X35",                "product_price": "230",            }]        },        {            "bill_id": "AB22",            "bill_date": "2020-11-14",            "products": [            {                "product_id": "X89",                "product_price": "700",            }]        }]    },    {        "mobile": "9999999999",        "username": "Alice",        "bills": [        {            "bill_id": "AC54",            "bill_date": "2019-12-20",            "products": [            {                "product_id": "X10",                "product_price": "109",            }]        }]    }]}  I wasn't able to figure out how to group the related properties (e.g. product properties with the same bill_id) into an array. How should I go about doing this?

OliverGong · Accepted Answer

Thank you for your question.We can try following steps to make it work as expected. 1. Convert csv to json ( you may use ConvertRecord ), to get JSON as below [{"mobile": "9876543210","username": "John","bill_id": "AB10","bill_date": "2020-12-23","product_id": "X34","product_price": "500"}, {"mobile": "9876543210","username": "John","bill_id": "AB10","bill_date": "2020-12-23","product_id": "X35","product_price": "230"}, {"mobile": "9876543210","username": "John","bill_id": "AB22","bill_date": "2020-11-14","product_id": "X89","product_price": "700"}, {"mobile": "9999999999","username": "Alice","bill_id": "AC54","bill_date": "2019-12-20","product_id": "X10","product_price": "109"}] 2. We will then use JoltTransformJSON to make the shift stuff, the specification of such JOLT is listed as: [ {  "operation" : "shift",  "spec" : {    "*" : {      "*" : "@(1,username)@(1,mobile)@(1,bill_id)@(1,bill_date)@(1,product_id).&"    }  }}, {  "operation" : "shift",  "spec" : {    "*" : {      "$" : "users.[#2].username",      "*" : {        "$" : "users.[#3].mobile",        "*" : {          "*" : {            "$" : "users.[#5].bills.[#3].bill_date",            "$1" : "users.[#5].bills.[#3].bill_id",            "*" : {              "product_*" : "users.[#6].bills.[#4].products.[#2].&"            }          }        }      }    }  }} ] NOTE:- For the JOLT spec, we gonna group the raw data into organic fields, which means we will get the data which are grouped by "username + mobile + bill_id +bill_date + product_id " fields.- After that, we get the phase-1 shifted data that are ordered by unique keys.- For the phase-2 shift, we gonna list out the data as predefined logic which is inferred from your attached sample output.3. For details on JOLT spec, you may refer to the following link: https://jolt-demo.appspot.com/Hope this helps.Thanks Oliver Gong

mobile	username	bill_id	bill_date	product_id	product_price
9876543210	John	AB10	2020-12-23	X34	500
9876543210	John	AB10	2020-12-23	X35	230
9876543210	John	AB22	2020-11-14	X89	700
9999999999	Alice	AC54	2019-12-20	X10	109

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.