Skip to main content
J0sh
J0sh
Gold ⭐️
April 18, 2022
Solved

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

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

araujo
araujoAnswer
Platinum ⭐️⭐️⭐️
April 21, 2022

@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 in thread

13 replies

J0sh
J0shAuthor
Gold ⭐️
April 21, 2022

any support 
Im new 
should I use 
RouteonContent or Attribute 
How do I define Attributes 

Translate
araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
April 21, 2022

@J0sh 

 

You can use a ReplaceText processor with the following properties:

Search Value:      (?s)^.*?(\{".*\}).*$
Replacement Value: $1

 

Cheers,

André

Translate
-- 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.
J0sh
J0shAuthor
Gold ⭐️
April 21, 2022

Thank you for the Response 
I tried this processor and connected to EvaulateJSONPath & It gave an error msg:
This is not a valid JSON Content

 

J0sh_0-1650529409165.png

 

Translate
araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
April 21, 2022

@J0sh ,

 

Can you provide me with the entire contents of your flow file?

 

Cheers,

André

 

Translate
-- 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.
J0sh
J0shAuthor
Gold ⭐️
April 21, 2022

Im not able to attach the file in this post 

help me & I'll attach the file 
I'm getting the file from Rest API & want to store in SQL Server

Translate
araujo
Platinum ⭐️⭐️⭐️
araujo
Platinum ⭐️⭐️⭐️
April 21, 2022
You can copy and paste the content into a Word file (.docx) and attach it
to the post.
Translate
-- 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.
J0sh
J0shAuthor
Gold ⭐️
April 21, 2022

@araujo As Im not able to attach I'll share the maximum content I can that will make it clear to understand what I have 
first few lines
<br> Response_Array:Array
(
[0] => {
[1] => access_token
[2] => :
[3] => 81e77e95-b3c2-4b0d-bf45-150caefa0250
[4] => ,
[5] => token_type
[6] => :
[7] => bearer
[8] => ,
[9] => refresh_token
[10] => :
[11] => a02658dc-2e94-4681-9bee-4ff4a7300519
[12] => ,
[13] => expires_in
[14] => :2349,
[15] => scope
[16] => :
[17] => read write
[18] => }
)
<br>Access Token Rcvd : 81e77e95-b3c2-4b0d-bf45-150caefa0250<br><br><br> <b>ZONE 1 - Loop Completed </b> <br>Procesing -> <br>{"Instant":[{"Pillar Id":"SPZ301FPL5207", "Last communicated time":"2022-04-21 14:04:58", "Volt_R":"208.40", "Volt_Y":"218.00", "Volt_B":"209.90", "Curr_R":"0.00", "Curr_Y":"0.00", "Curr_B":"0.00", "Curr_N":"0.00", "Pwr_R":"0.00", "Pwr_Y":"0.00", "Pwr_B":"0.00", "Total Load":"0", "PF_Avg":"0.999", "Total Energy Consumed (kWh)":"5106.69", "Burning %":"0.00", "Relay_R":"OFF", "Relay_Y":"OFF", "Relay_B":"OFF", "Mode":"Auto", "Load ON Hours":"0000-00-00 00:00:00", "Alerts":"", "Root Cause":""},

Middle

{"Pillar Id":"SPZ301FPL5828", "Last communicated time":"2022-04-21 14:06:52", "Volt_R":"222.70", "Volt_Y":"224.90", "Volt_B":"224.90", "Curr_R":"0.00", "Curr_Y":"0.00", "Curr_B":"0.00", "Curr_N":"0.00", "Pwr_R":"0.00", "Pwr_Y":"0.00", "Pwr_B":"0.00", "Total Load":"0", "PF_Avg":"1.000", "Total Energy Consumed (kWh)":"5534.58", "Burning %":"0.00", "Relay_R":"OFF", "Relay_Y":"OFF", "Relay_B":"OFF", "Mode":"Auto", "Load ON Hours":"0000-00-00 00:00:00", "Alerts":"Low Voltage", "Root Cause":""}]}<br>Array
(
[success] => 1
[message] => Message sent Successfully
)
<br><br><br> <b>ZONE 2 - Loop Completed </b> <br>Procesing -> <br>{"Instant":[{"Pillar Id":"SPZ301FPL3887", "Last communicated time":"2022-04-21 14:06:30", "Volt_R":"0.00", "Volt_Y":"220.50", "Volt_B":"216.10", "Curr_R":"0.00", "Curr_Y":"0.00", "Curr_B":"0.00", "Curr_N":"0.00", "Pwr_R":"0.00", "Pwr_Y":"0.00", "Pwr_B":"0.00", "Total Load":"0", "PF_Avg":"1.000", "Total Energy Consumed (kWh)":"1854.50", "Burning %":"0.00", "Relay_R":"OFF", "Relay_Y":"OFF", "Relay_B":"OFF", "Mode":"Auto", "Load ON Hours":"0000-00-00 00:00:00", "Alerts":"Low Voltage", "Root Cause":""},

{"Pillar Id":"SPZ301FPL6205", "Last communicated time":"2022-04-21 14:06:33", "Volt_R":"213.30", "Volt_Y":"213.20", "Volt_B":"212.90", "Curr_R":"0.00", "Curr_Y":"0.00", "Curr_B":"0.00", "Curr_N":"0.00", "Pwr_R":"0.00", "Pwr_Y":"0.00", "Pwr_B":"0.00", "Total Load":"0", "PF_Avg":"0.999", "Total Energy Consumed (kWh)":"10860.35", "Burning %":"0.00", "Relay_R":"OFF", "Relay_Y":"OFF", "Relay_B":"OFF", "Mode":"Auto", "Load ON Hours":"0000-00-00 00:00:00", "Alerts":"High Voltage", "Root Cause":""}]}<br>Array
(
[success] => 1
[message] => Message sent Successfully
)
<br>Referer - cron
Last Line

This is my content I think my API is mixed with JSON & xml format 
sorry Im beginner & Im trying understand and learn my way up on this tool

Translate
J0sh
J0shAuthor
Gold ⭐️
April 21, 2022

for clarity 
earlier I shared information from Master flowFlile 
now I sent from Instant & only the few few lines changes in master & instant API data

select concat('SPZ301FPL', a.meter_primary_id) as pillarid, g.created_date as last_communicated_time, g.vr, g.vy, g.vb, g.ir, g.iy, g.ib, g.ineutral, g.kwr, g.kwy, g.kwb, g.avgpf, g.relay_r, g.relay_y, g.relay_b, g.automanual, g.loadonhour, '' as alerts, '' as rootcause 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 limit 5

Response_Array:Array ( [0] => { [1] => access_token [2] => : [3] => 5bc16e44-4e4c-4b21-b15c-68c4eda466a2 [4] => , [5] => token_type [6] => : [7] => bearer [8] => , [9] => refresh_token [10] => : [11] => 9654dc77-4457-49ef-ab75-f027e1a8bd22 [12] => , [13] => expires_in [14] => :8200, [15] => scope [16] => : [17] => read write [18] => } )
Access Token Rcvd : 5bc16e44-4e4c-4b21-b15c-68c4eda466a2
.....Procesing ->
{"Master":[{"Pillar Id":"SPZ301FPL3205", "Last communicated time":"2022-04-21 15:24:43", "Volt_R":"223.30", "Volt_Y":"220.20", "Volt_B":"215.20", "Curr_R":"0.00", "Curr_Y":"0.00", "Curr_B":"0.00", "Pwr_R":"0.00", "Pwr_Y":"0.00", "Pwr_B":"0.00", "PF_Avg":"0.999", "Burning %":"0.00", "Relay_R":"OFF", "Relay_Y":"OFF", "Relay_B":"OFF", "automanual":"0", "Load ON Hours":"0000-00-00 00:00:00", "Alerts":"", "Root Cause":""},

Translate
J0sh
J0shAuthor
Gold ⭐️
April 21, 2022

@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

Translate
araujo
Platinum ⭐️⭐️⭐️
araujoAnswer
Platinum ⭐️⭐️⭐️
April 21, 2022

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

 

Translate
-- 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.
    J0sh
    J0shAuthor
    Gold ⭐️
    April 22, 2022

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


    Thanks again
    J0sh.

    Translate
      J0sh
      J0shAuthor
      Gold ⭐️
      April 22, 2022

      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-be-converted/td-p/341954

      Thanks,

      J0sh.

      Translate
      Terms & ConditionsAccessibility statement