Solr DIH importing multiple values for a column

Question

I 'm using DIH to import various fields from database. The sql query returns the following data

    |COL1 | COL2 |COL3 |COL4 |COL5 |COL6 |COL7 |COL8 |COL9 |COL10 |
    ------------------------------------------------------------------
    |val1 |val2  |val3 |val4 |val5 |val6 |val7 |val8 |val9 |multi-val1 |
    |val1 |val2  |val3 |val4 |val5 |val6 |val7 |val8 |val9 |multi-val2 |

<field name="Col10" type="int" indexed="true" stored="true" default="0" multiValued="true"/>

When I run the full import all the rows are processed but when I look at the data I see that there is only 1 row in Solr. I understand that is because of unique Id (Col1). But I 'm not sure why col10 does not store multiple values that are returned from my query.

What do I need to do to store the value as multivalued/array?

Thanks

Answer 1

On the other hand, jsp's SQL query return the multiple value if col10 in multiple rows, since the unique id is col1

|COL1 | COL2 |COL3 |COL4 |COL5 |COL6 |COL7 |COL8 |COL9 |COL10 |
------------------------------------------------------------------
|val1 |val2  |val3 |val4 |val5 |val6 |val7 |val8 |val9 |multi-val1 |
|val1 |val2  |val3 |val4 |val5 |val6 |val7 |val8 |val9 |multi-val2 |

Perhaps tweak the SQL to produce the following result, and then use RegexTransformer as Kevin suggested.

|COL1 | COL2 |COL3 |COL4 |COL5 |COL6 |COL7 |COL8 |COL9 |COL10 |
------------------------------------------------------------------
|val1 |val2  |val3 |val4 |val5 |val6 |val7 |val8 |val9 |multi-val1, multi-val2|

Certainly there are more than one ways to assemble the multiple valued field based on DIH. One alternative example:

<entity name="cols" query="Select * From col1_9_table">
    <entity name="col10" query="Select col10 From col10_table 
                                Where Id='${col1_9_table.Id}'">  
     <field column="col10" name="col10"/>
    </entity>
</entity>