CSVLookupService to Return multiple records

[#1]

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" : [{...}]

} ]

[#2]

Hi

i have the same issue, did you solve it ?

[#3]

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

[#4]

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