Support Questions

Find answers, ask questions, and share your expertise

Convert Json array into csv using Nifi produces MapRecord

Explorer

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?

6 REPLIES 6

Super Collaborator

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

Explorer

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

Super Collaborator

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

Community Manager

@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


Regards,

Diana Torres,
Community Moderator


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community:

Explorer

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. 

New Contributor

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