Support Questions

Find answers, ask questions, and share your expertise

convert json to csv in nifi

avatar
New Contributor

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!

10 REPLIES 10

avatar
Contributor

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...

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

avatar
Super Guru

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.

--
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.

avatar
Rising Star

@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:

Kiranq_0-1696263616383.png

Can you tell where I am going wrong?
Thanks. 

 

avatar
Super Guru

@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.

 

avatar
Rising Star

@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:

Kiranq_0-1696264800859.png



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

avatar
Super Guru

@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?

avatar
Rising Star

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

avatar
Super Guru

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

avatar
Rising Star

Here

JoltTransforRecord:

Kiranq_0-1696283131467.png

JsonTreeReader:

Kiranq_1-1696283171922.png

AvroReader:

Kiranq_2-1696283212507.png

Thanks.