Support Questions

Find answers, ask questions, and share your expertise

CSVLookupService to Return multiple records

New Contributor

Hi I have input.csv and Lookup.csv. I have multiple matching records in Lookup.csv.

After lookup how do i get all the related records from Lookup.csv. Below is my requirements.

Please help me finding  solution for this, any suggestions will be of great helpful to me, Thanks in advance for you time and suggestions, With CSVRecordLookupService, im getting only last record in lookup.csv

 

input.csv
col1,col2,col3,col4
col1,2,col3,col4
col1,4,col3,col4
col1,6,col3,col4
col1,8,col3,col4

Lookup.csv
col2,col5,col6,col7,col8
2,abc,China,123,col8
4,def,USA,118,col8
8,qwe,Canada,118,col8
8,zyx,England,118,col8
 
Output as json
[ {
  "col1" : "col1",
  "col2" : "8",
  "col3" : "col3",
  "col4" : "col4",
  "col6" : null,
  "sub" : [{
      "col5":"qwe",
      "col6":"Canada",
      "col7":"118",
      "col8":"col8"
  },{
      "col5":"zyx",
      "col6":"England",
      "col7":"118",
      "col8":"col8"
  }]
}, {
  "col1" : "col1",
  "col2" : "2",
  "col3" : "col3",
  "col4" : "col4",
  "col6" : null,
  "sub" : [{...}]
} ]
3 REPLIES 3

Explorer

Hi

i have the same issue, did you solve it ?

Master Collaborator

@ABBI @yamaga ,

 

The lookup service require that the lookup key be unique for it to work correctly. If there are duplicates you can chose to ignore them, but only one value will ever be returned.

 

One thing you can do is to consolidate records with the same key under the same line, so all the values will be returned and you can then deal with it (e.g. split the values)  in the NiFi flow.

 

In your example, we could change the lookup files like below:

 

col2,col5,col6,col7,col8
2,abc,China,123,col8
4,def,USA,118,col8
8,"qwe,zyx","Canada,England","118,118","col8,col8"

Regards,

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.

New Contributor

Hi Yamaga,

Basically all lookup services in NIFI does not return multiple rows.

In my case i wanted out put in json, so I had converted csv lookup file using JOLT transfomation to have key:[jsonArray], with this i used properties lookup and I got the required output.

 

Thanks

Ansar

Take a Tour of the Community
Don't have an account?
Your experience may be limited. Sign in to explore more.