question Re: convert json to csv in nifi in Support Questions

convert json to csv in nifi

VikasNayak07 — Fri, 11 Mar 2022 11:16:31 GMT

Hi,

I am trying to convert json to csv in nifi. My json is something like below

{"tokens":["12343455577666","2134421241241","124142141411445","21425654768686"]}

and my csv should be like below

tokens

12343455577666

2134421241241

124142141411445

21425654768686

Please help me. Thanks!

Re: convert json to csv in nifi

Royles — Fri, 11 Mar 2022 11:58:39 GMT

There are a few posts on this topic. The nearest to your need with a solution is
https://community.cloudera.com/t5/Support-Questions/NiFi-JSON-Array-split/m-p/158842#M121233

The key being using the SplitJson processor the updated doc reference is
https://nifi.apache.org/docs/nifi-docs/components/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.9.0/org.apache.nifi.processors.standard.SplitJson/index.html

This will need to be used in combination with Reader > JsonPathReader > SplitJson > Writer

Re: convert json to csv in nifi

araujo — Tue, 15 Mar 2022 02:38:50 GMT

Hi, @VikasNayak07 ,

An easy way to achieve this is to use a JoltTransformRecord processor with a JsonTreeReader and a CSVRecordSetWriter. The following "Chain" transformation will produce the output that you want:

[ { "operation": "shift", "spec": { "tokens": { "*": "[&].token" } } } ]

Cheers,

André

--
Was your question answered? Please take some time to click on "Accept as Solution" below this post.
If you find a reply useful, say thanks by clicking on the thumbs up button.

Re: convert json to csv in nifi

Kiranq — Mon, 02 Oct 2023 16:19:45 GMT

@araujo
I am trying to implement your suggested way.
My json is :
{
"Id" : "some int",
"TenantId" : "some double value"
}

and I am using chain jolt transform method as:
[
{
"operation": "shift",
"spec": {
"*": {
"@(1,Id)": "[&1].Id",
"@(1,TenantId)": "[&1].TenantId"
}
}
}]

However, I am getting a error as:

Can you tell where I am going wrong?
Thanks.

Re: convert json to csv in nifi

SAMSAL — Mon, 02 Oct 2023 16:32:48 GMT

@Kiranq,

from the error message I can see that your input seems to be in avro format. Are you sure the flowfile has valid json format? If not you can use ConvertAvroToJson processor before using the jolt transformation to convert avro to json. Also from the json you provided not sure your jolt spec will work. If you provide your json input and the expected output more accurately maybe I can help you with defining the correct jolt spec.

Re: convert json to csv in nifi

Kiranq — Mon, 02 Oct 2023 16:40:08 GMT

@SAMSAL
thank you for responding.

I am infact using/converting the output from ExecuteSql from Arvo to Json using ConvertAvroToJson processor.
Below is the output of the same processor:

I am also wondering if I am providing the json format, its throwing error for avro.
Can you suggest something?

Re: convert json to csv in nifi

SAMSAL — Mon, 02 Oct 2023 17:03:08 GMT

@Kiranq,

I have to look into the JoltTranformationRecord to see what configuration you have there vs what is expected. However since you are having a json input can you use the JoltTransformationJson processor instead or are you trying to convert the output json into different format?

Re: convert json to csv in nifi

Kiranq — Mon, 02 Oct 2023 17:12:35 GMT

Thats right. I intend to convert the json using JoltTransfomationRecord into csv format and then save the file in my local machine.

Re: convert json to csv in nifi

SAMSAL — Mon, 02 Oct 2023 17:25:33 GMT

Can you take screenshot of the JoltTranformRecord and the reader\write services settings?

Re: convert json to csv in nifi

Kiranq — Mon, 02 Oct 2023 21:46:18 GMT

Here

JoltTransforRecord:

JsonTreeReader:

AvroReader:

Thanks.

Re: convert json to csv in nifi

SAMSAL — Tue, 03 Oct 2023 15:12:29 GMT

@Kiranq,

What I found is that the JoltTransformRecord expects only single record to work with hence the name. I noticed when I try to pass an array I was getting the error "...error transforming the first record", however if I pass just one json record it works. If you have an array of json\csv and you are looking to split and process each record individually then I would suggest that you split the records before the JoltTransformRecord. If you dont want to split the array then I recommend using JoltTranformJson first and then use Convert Record processor to convert to CSV.