question Re: Convert Json array into csv using Nifi produces MapRecord in Support Questions

Convert Json array into csv using Nifi produces MapRecord

Althotta — Fri, 15 Jul 2022 12:41:51 GMT

JsonTreeReader to CSV Writer produces MapRecord String for the inner Json.

Data

"id" : "aaaa",

"billingaddress" : {
"city" : null,
"country" : "CHINA",
"geocodeAccuracy" : null,
"latitude" : null,
"longitude" : null,
"postalCode" : null,
"state" : null,
"street" : null
}

Produces CSV out with MapRecord String attached.

aaaa|MapRecord[{country=CHINA, city=null, street=null, latitude=null, postalCode=null, geocodeAccuracy=null, state=null, longitude=null}]

Expected Output:

aaaa|{country=CHINA, city=null, street=null, latitude=null, postalCode=null, geocodeAccuracy=null, state=null, longitude=null}

I want to keep the inner JSON as-is in the CSV output. Any workarounds to this issue?

Re: Convert Json array into csv using Nifi produces MapRecord

SAMSAL — Fri, 15 Jul 2022 12:33:33 GMT

Hi,

You can use FlattenJson processor to flatten your json before converting to CSV. The output Json after flattening will be something like this:

{
"id": "aaaa",
"billingaddress.city": null,
"billingaddress.country": "CHINA",
"billingaddress.geocodeAccuracy": null,
"billingaddress.latitude": null,
"billingaddress.longitude": null,
"billingaddress.postalCode": null,
"billingaddress.state": null,
"billingaddress.street": null
}

Another Option is to use JoltTransformJSON to flatten your json if you don't like the "billingaddress." prefix. You can use the following Jolt Spec:

[
{
   "operation": "shift",
   "spec": {
            "id": "id",
            "billingaddress": {
            "*": "&"
                }
      }
}
]

Which will produce the following json:

{
"id" : "aaaa",
"city" : null,
"country" : "CHINA",
"geocodeAccuracy" : null,
"latitude" : null,
"longitude" : null,
"postalCode" : null,
"state" : null,
"street" : null
}

If you think this solves the issue please accept solution. Thanks

Re: Convert Json array into csv using Nifi produces MapRecord

Althotta — Fri, 15 Jul 2022 12:43:39 GMT

@SAMSAL I need to retain the JSON format as-is in the output. I have updated the updated the expected output.

Expected Output:

aaaa|{country=CHINA, city=null, street=null, latitude=null, postalCode=null, geocodeAccuracy=null, state=null, longitude=null}

Re: Convert Json array into csv using Nifi produces MapRecord

SAMSAL — Fri, 15 Jul 2022 13:06:04 GMT

In this case I would suggest ReplaceText processor with the Search Value set to:

(MapRecord|\[|\])

And Replacement Value is set to Empty String.This will produce the result:

aaaa|"{country=CHINA, city=null, street=null, latitude=null, postalCode=null, geocodeAccuracy=null, state=null, longitude=null}"

Re: Convert Json array into csv using Nifi produces MapRecord

DianaTorres — Mon, 18 Jul 2022 17:23:10 GMT

@Althotta Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. Thanks

Re: Convert Json array into csv using Nifi produces MapRecord

Althotta — Mon, 25 Jul 2022 09:26:14 GMT

Not yet resolved. @SAMSAL solution is a potential workaround. Iam looking for a right solution why Convert produces this as i have few places to change.

Re: Convert Json array into csv using Nifi produces MapRecord

arshad10244 — Sun, 29 Jan 2023 16:49:04 GMT

I had a similar issue, it happens because of nifi not being able to resolve the schema. You need to first create a schema and set the type of billing address as string and add it to avroschemaregistry and lastly configure your jsontreereader to use that schema