Elasticsearch index with jdbc driver

Question

Sorry my english is bad

I am using elasticsearch and jdbc river. I have two table with many-to-many relations. For example:

product

+---+---------------+
| id| title         |
+---+---------------+
| 1 | Product One   |
| 2 | Product Two   |
| 3 | Product Three |
| 4 | Product Four  |
| 5 | Product Five  |
+---+---------------+

product_category

+------------+-------------+
| product_id | category_id |
+------------+-------------+
|      1     |       1     |
|      1     |       2     |
|      1     |       3     |
|      2     |       4     |
|      2     |       5     |
+------------+-------------+

category

+---+---------------+
| id| name          |
+---+---------------+
| 1 | Category One  |
| 2 | Category Two  |
| 3 | Category Three|
| 4 | Category Four |
| 5 | Category Five |
+---+---------------+

I want to use array type.

{
    "id": 1,
    "name": "Product one",
    "categories": {"Category One", "Category Two", "Category Three"}
},

How should I write a sql?

Answer 1

Use elasticsearch-jdbc structured objects with sql, no need to group_concat:

SELECT
  product.id AS _id,
  product.id,
  title,
  name AS categories
FROM product
LEFT JOIN (
 SELECT * 
 FROM product_category
 LEFT JOIN category 
   ON product_category.category_id = category.id
) t
ON product.id = t.product_id

Since river has been deprecated since ES v1.5, maybe run a standalone importer is better.