Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

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

avatar
Contributor

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

image.png

1 ACCEPTED SOLUTION

avatar
Super Guru

@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

araujo_0-1650577447677.png

 

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é

 

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

View solution in original post

13 REPLIES 13

avatar
Contributor

avatar
Super Guru

@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

araujo_0-1650577447677.png

 

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é

 

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

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


Thanks again
J0sh.

avatar
Contributor

J0sh_0-1650590445291.png

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.