# Elasticsearch-0.20.2 with jdbc-river, in the context of simple sql join seems not to fecth all subdocuments in the structuredObject, Please help **URL:** \ **Category:** Elasticsearch\ **Created:** [January 25, 2013, 12:42pm UTC](https://discuss.elastic.co/t/elasticsearch-0-20-2-with-jdbc-river-in-the-context-of-simple-sql-join-seems-not-to-fecth-all-subdocuments-in-the-structuredobject-please-help/10505 "2013-01-25T12:42:58Z")\ **Posts on this page:** 2\ **Page:** 1
**Author:** ![Gabriel\_Mirea](https://avatars.discourse-cdn.com/v4/letter/g/ee59a6/32.png) [@Gabriel\_Mirea](https://discuss.elastic.co/u/Gabriel_Mirea)\ **Post date:** [January 25, 2013, 12:42pm UTC](https://discuss.elastic.co/t/elasticsearch-0-20-2-with-jdbc-river-in-the-context-of-simple-sql-join-seems-not-to-fecth-all-subdocuments-in-the-structuredobject-please-help/10505/1 "2013-01-25T12:42:58Z")
1)I' m using a Oracle XE server on a win7 x64, where I've done: 1.1)unzip elasticsearch-0.20.2.zip 1.2)copy ojdbc14.jar to %ES\_HOME%/lib/ojdbc14.jar 1.3)./bin/plugin -url [http://bit.ly/U75w1N](http://bit.ly/U75w1N) -install river-jdbc 1. Within oracle squirrel client: 2.1)Create Table ORDERS( ORDER\_ID Number(7) Primary Key, description VARCHAR2(100)); Create Table OPTIONS( OPTION\_ID Number(7) Primary Key, order\_id Number(7), option\_name VARCHAR2(50), CONSTRAINT fk\_order FOREIGN KEY (order\_id) REFERENCES ORDERS(order\_id)) INSERT INTO ORDERS(ORDER\_ID, description) values(1, '1st order'); INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(1, 1, '1st order option1'); INSERT INTO OPTIONS(OPTION\_ID, ORDER\_ID, option\_name) values(2, 1, '1st order option2'); 2.2)sqplus-\> SQL\> select \* from orders; ## ORDER\_ID DESCRIPTION * * * ``` 1 1st order ``` SQL\> select \* from options; OPTION\_ID ORDER\_ID OPTION\_NAME * * * ``` 1 1 1st order option1 2 1 1st order option2 ``` SQL\> select 'order' as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.options" from orders ord inner join options opt on ord.order\_id = opt.order\_id; \_index \_id order.oId order.options * * * * * * order 1 1 1st order option1 order 1 1 1st order option2 1. On a ubuntu VM box, hosted on the same win7 x64, I executed: ubuntu@ubuntu-VirtualBox:~$ uname -a Linux ubuntu-VirtualBox 3.2.0-24-generic-pae #37-Ubuntu SMP Wed Apr 25 10:47:59 UTC 2012 i686 i686 i386 GNU/Linux ubuntu@ubuntu-VirtualBox:~$ curl -XPUT '192.168.56.1:9200/\_river/my\_jdbc\_river/\_meta' -d '{ "type" : "jdbc", "jdbc" : { "driver" : "oracle.jdbc.OracleDriver", "url" : "jdbc:oracle:thin:@localhost:1521/XE", "user" : "", "password" : "", "poll" : "10s", "sql" : "select \u0027order\u0027 as "\_index", ord.order\_id as "\_id",ord.order\_id as "order.oId",option\_name as "order.option" from orders ord inner join options opt on ord.order\_id = opt.order\_id" } }' 4)I'm searching for: ubuntu@ubuntu-VirtualBox:~$ curl -XGET '192.168.56.1:9200/order/jdbc/\_search?pretty' { "took" : 2, "timed\_out" : false, "\_shards" : { "total" : 5, "successful" : 5, "failed" : 0 }, "hits" : { "total" : 1, "max\_score" : 1.0, "hits" : [ { "\_index" : "order", "\_type" : "jdbc", "\_id" : "1", "\_score" : 1.0, "\_source" : {"order":{"oId":1,"option":"1st order option2"},"\_id":1,"\_index":"order"} } ] } Obviously is fetching only the 2nd record from the one-to-many relation, the "1st order option1" is missing...where I've done wrong? 5)But I would like to have the following result: "\_score" : 1.0, "\_source" : {"order":{"oId":1,"options":["1st order option1","1st order option2"]},"\_id":1,"\_index":"order"} that should be an equivalent result from your example: index=relations id=Good {"contact":{"employee":["Müller","Meier","Schulze"],"customer":"Good"}} Can you please help me?with any hint? Thanks gabriel -- ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 2:54am UTC](https://discuss.elastic.co/t/elasticsearch-0-20-2-with-jdbc-river-in-the-context-of-simple-sql-join-seems-not-to-fecth-all-subdocuments-in-the-structuredobject-please-help/10505/2 "2017-07-06T02:54:36Z")
Closed on July 6, 2017, 2:54am UTC