Nifi how to sql join two flowfiles

[#1]

Hi everyone

I have spent several days trying to solve the following case.

I need to join two flowfiles (master-detail) using Nifi flow, I tried using queryrecord and mergerecord but i couldn't did it.

I have some json based files in a HDFS (Apache Spark) i need to make a join and put de result in a database table (using nifi v1.11.4). I can read HDFS files but i cant' perform a join.

Example:

master_file content:

{

  "FALLA_ID" : 40217,
  "FALLA_FECHA" : "1998-01-01 00:00:00",
  "FALLA_RPT_NRO" : 25,
  "FALLA_FRECUENCIA" : "60.0000"

}

detail_file content:

{

"FALLA_ID" : 40217,
"PRINCIPAL" : 1,
"FALLA_DTL_ID" : 1,
"FALLA_CLASE" : 1,
"TPF_FALLA_ID" : 1

},

{

"FALLA_ID" : 40217,
"PRINCIPAL" : 1,
"FALLA_DTL_ID" : 2,
"FALLA_CLASE" : 2,
"TPF_FALLA_ID" : 5

}

Expected_File content:

{

  "FALLA_ID" : 40217,
  "FALLA_FECHA" : "1998-01-01 00:00:00",
  "FALLA_RPT_NRO" : 25,
  "FALLA_FRECUENCIA" : "60.0000",

  "PRINCIPAL" : 1,

  "FALLA_CLASE" : 2

}

As you can see, the FALLA_ID field is the common field to make a join.

Have you some ideas?

Thank you!!

[#2]

Hi mahendra

Thank you for you answer

In effect i'm using a nifi cluster so the second option isn't viable.

I will work in your first approach and return back when i will finish, to write my results.

Regards.

[#3]

Hi mahendra

I'm back, i did try your first approach and it helps me to solve my requirement.

Thank you again

Regards

[#4]

Hi @SirV  can you please send me the code , m also stuck in the same situation.

[#5]

Hi @hegdemahendra @SirV , I have a same scenario, and your point 1 resolution seems straight forward, but i didn't get in "MergeContent" processor. Your first dataEvaluateJsonPath is going to add in MergeContent with comman attribute "FILLA_ID" and detail_file content also going to MergeContent processor. but its not giving result with mapped field. sharing in my flow. Please suggest.