Apache Nifi - Not a Validate JSON & Not a Validate XML

[#1]

select concat SPZ301FPL , a meter primary id as pillarid, b locationid as zoneidnumber, b remarks as zonename, f wardid as division, d sectionname as section, concat e streetname, , , e landmark as location, f latitude as pillarlatitude, f longitude as pillarlongitude, g ontime as scheduledontime, g offtime as scheduledofftime, f numberoffittings as numberoflightsconnected, f totalkw as basewattage from meter primary master a inner join locationmaster b on a locationid b locationid inner join polemaster f on a meter primary id f poleid inner join sectionmaster d on d sectionid f sectionid inner join addressmaster e on e addressid f addressid inner join currentreading g on g meter primary id a meter primary id where b locationid in 1, 2, 3, 4, 5 order by a meter primary id br br br Response Array:Array 0 { 1 access token 2 : 3 10350925 2a0d 4bbe b044 7393ae1426c6 4 , 5 token type 6 : 7 bearer 8 , 9 refresh token 10 : 11 eca77863 e681 4a5c abfb b90ea6a436f8 12 , 13 expires in 14 :9348, 15 scope 16 : 17 read write 18 } br Access Token Rcvd : 10350925 2a0d 4bbe b044 7393ae1426c6 br Procesing br {"Master": {"Pillar Id":"SPZ301FPL3205", "Zone Number":"5", "Zone Name":"Royapuram", "Division":"58", "Section":"South GT", "Location":"EVR Salai, Opp Rippon Building", "Pillar Latitude":"13 08117", "Pillar Longitude":"80 272727", "Scheduled ON Time":"18:30", "Scheduled OFF Time":"05:45", "No of lights connected":"38", "Base Wattage":"7220"},

I have api that gives the above content want only the Braces value & delete rest

please help 

hope Im clear 

Tried - EvaluateJSONPath error - not a valid JSON Content
Tried - XML - did not start & end properly

Thanks,

J0Sh

[#2]

@araujo 

Pls request access
https://drive.google.com/file/d/13h2hW3eJrPXf0ysO0L-xp_LlPgAKCcPQ/view?usp=sharing
Instant (main File)
https://drive.google.com/file/d/1hkT8lv4q_bHnsuEO0exxXmHUCcdviiaX/view?usp=sharing
Master

Thanks
J0sh

[#3 · Accepted solution]

@J0sh ,

If you use a ReplaceText with the following settings it will clean up the garbage in your flowfiles:

Search Value:      (?s)(^|(\})<br>).*?(\n).*?(<br>(\{)|$)
Replacement Value: $2$3$5

This file, though, has not 1 JSON, but 8 different JSON fragments. So the resulting flowfile will still have 8 lines, each with a valid JSON. You must use a SplitText process to separate those into 8 different flowfiles before applying the EvaluateJSONPath.

Cheers,

André

[#4]

@araujo Thanks for the support I'll do the steps & will let you know my results

Thanks again
J0sh.

[#5]

I still have 1more problem 
some datetime is this 0000-00-00 00:00:00 & SQL is not accepting this data
I'll opened this issue as new post please check it 

https://community.cloudera.com/t5/Support-Questions/Apache-Nifi-DateTime-0000-00-00-00-00-00-cannot-...

Thanks,

J0sh.