CODEWITHSUNDEEP
solr
yazraf
yazraf

Reputation: 35

How to index multi dimension array in solr field

I am indexing Mysql database with solr, I have one-many relation between users table and order table:one user can have many orders.

order table have many columns (id, orderDate, caseNumber).

My goal is to index these tables in solr and have USR_ID field to store the user id, ORDERS feild type= multidimensional array to store each order for that user as an associative array.

the desired result is: 

{
        "USR_ID":"10",
        "ORDERS":[
             {"ID":"1" ,"ORDER_DATE":"12-03-2018", "CASE_NUMBER":"554"}, //FIRST FIELD
             {"ID":"9","ORDER_DATE":"15-03-2018", "CASE_NUMBER":"569"} //SECOND FIELD
         ]
}

what i am getting is one dimensional array with all orders columns

{
        "USR_ID":"10",
        "ORDERS":[
             "1", "12-03-2018", "554", //FIRST FIELD
             "9", "15-03-2018", "569" //SECOND FIELD
         ]
}

Here is what I tried. entities config in data-config.xml 

<dataConfig>
    <dataSource type="JdbcDataSource" 
                driver="com.mysql.jdbc.Driver"
                url="jdbc:mysql://localhost/mydb1" 
                user="" 
                password=""/>
    <document>
        <entity name="USERS"  
            pk="USR_ID"
            query="SELECT USR_UID, FROM USERS"
            deltaImportQuery="SELECT USR_UID, FROM USERS WHERE USR_UID='${dih.delta.USR_UID}'"
            deltaQuery="SELECT USR_UID FROM USERS WHERE USERS.USR_UPDATE_DATE > '${dih.last_index_time}'">
            <entity name="ORDER"  pk="ID"
                query="SELECT ID AS ORDERID, ORDER_DATE, CASE_NUMBER FROM ORDER WHERE USR_ID = '${USERS.USR_UID}'"
                deltaQuery="select ID from ORDER where UPDATED_AT > '${dih.last_index_time}'"
                parentDeltaQuery="SELECT USR_UID FROM USERS WHERE USR_UID = ${ORDER.USR_UID}">
                    <field column="ORDERID" name="ORDERS" />
                    <field column="CREATION_DATE" name="ORDERS" />
                    <field column="CASE_NUMBER" name="ORDERS" />     
            </entity>
        </entity>
    </document>
</dataConfig>

Here is fields definition in schema.xml file


  <field name="USR_ID" type="string" indexed="true" stored="true" required="true" multiValued="false" />
  <field name="ORDERS" type="text_general" indexed="true" stored="true" required="false" multiValued="true"/>

Upvotes: 2

Views: 1877

Answers (2)

yazraf
yazraf

Reputation: 35

The answer was to use the following attribute in child="true" field when u define your data-config.xml file

in my case 

  <entity child="true" name="ORDER"  pk="ID"
                query="SELECT ID AS ORDERID, ORDER_DATE, CASE_NUMBER FROM ORDER WHERE USR_ID = '${USERS.USR_UID}'"
                deltaQuery="select ID from ORDER where UPDATED_AT > '${dih.last_index_time}'"
                parentDeltaQuery="SELECT USR_UID FROM USERS WHERE USR_UID = ${ORDER.USR_UID}">
                    <field column="ORDERID" name="ORDERS" />
                    <field column="CREATION_DATE" name="ORDERS" />
                    <field column="CASE_NUMBER" name="ORDERS" />     
            </entity>

Upvotes: 0

PiGo
PiGo

Reputation: 149

You will have to go with sub-documents, or at least have one document by order since you only have an Id at the root level :

{
        "USR_ID":"10",
        "ID":"1" ,
        "ORDER_DATE":"12-03-2018", 
        "CASE_NUMBER":"554"
}

See this good explantion of nested documents : http://yonik.com/solr-nested-objects/

Upvotes: 1

Related Questions