Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How do I Merge header and detail files (like joining 2 tables in a database)

How do I Merge header and detail files (like joining 2 tables in a database)

New Contributor

This question is similar to How to merge 2 flow file content coming from 2 different NiFi flow based on some matching condition ...

And I understand NiFi is not meant to join 2 files, but shouldn't what I did below work?

My inputs are as follows:

Input 1:

[{"id": 1, "visitDate": "12/5/2017", "name":"VJ1"},
 {"id": 2, "visitDate": "12/5/2017", "name":"VJ2"}]

Input 2:

[{"id":1,"qtag":"abc","value":1},
 {"id":1,"qtag":"def","value":2},
 {"id":1,"qtag":"comments","value":"test1"},
 {"id":2,"qtag":"abc","value":3},
 {"id":2,"qtag":"def","value":1},
 {"id":2,"qtag":"comments","value":"testing 2"}]

Desired Output:

Flowfile 1: 
{
 "id": 1, 
 "visitDate": "12/5/2017", 
 "name":"VJ1", 
 "details": [{"qtag":"abc","value":1},
             {"qtag":"def","value":2},
             {"qtag":"comments","value":"test1"}]
}
Flowfile 2:
{
 "id": 2, 
 "visitDate": "12/5/2017", 
 "name":"VJ2", 
 "details": [{"qtag":"abc","value":3},
             {"qtag":"def","value":1},
             {"qtag":"comments","value":"testing 2"}]
}

I tried this as follows:

ListFiles --> (2 files returned) --> Fetch File --> SplitJSON --> EvaluateJSON (create attribute "xid" from "$.id") --> UpdateAttribute (just to ensure that the "xid" attribute was created) --> MergeContent (Correlation Attribute: "xid"). Only the content from Input File 2 gets merged. The flowfiles from Input 1 even if though the "xid" matches, it is not merged with the other content. I gave some large values for minimum and maximum number of entries, 2 min for Max bin age, but they are not merging.

Any suggestions? Or maybe I havent understood exactly how MergeContent works.

Thanks!

1 REPLY 1

Re: How do I Merge header and detail files (like joining 2 tables in a database)

MergeContent is not a join, it merges the content of flow files one after another.

If flow file 1 has:

[
{"id":1,"visitDate":"12/5/2017","name":"VJ1"},
{"id":2,"visitDate":"12/5/2017","name":"VJ2"}
]

and flow file 2 has:

[
{"id":1,"qtag":"abc","value":1},
{"id":1,"qtag":"def","value":2},
{"id":1,"qtag":"comments","value":"test1"},
{"id":2,"qtag":"abc","value":3},
{"id":2,"qtag":"def","value":1},
{"id":2,"qtag":"comments","value":"testing 2"}
]

The merged content of these two flow files would be:

[
{"id":1,"visitDate":"12/5/2017","name":"VJ1"},
{"id":2,"visitDate":"12/5/2017","name":"VJ2"}
]
[
{"id":1,"qtag":"abc","value":1},
{"id":1,"qtag":"def","value":2},
{"id":1,"qtag":"comments","value":"test1"},
{"id":2,"qtag":"abc","value":3},
{"id":2,"qtag":"def","value":1},
{"id":2,"qtag":"comments","value":"testing 2"}
]