Array field with MySQL

[xavierfacq]

Hi,

I have a problem with a MySQL query and a field typed as an array. My mapping is : "partner_teams_ids" : { "type": "long", "index": "not_analyzed", "include_in_all": false },

I want a subquery result to be "translated" to an array

e.g. : SELECT x.a, (SELECT group_concat(y.b) from y where y.ext_id = x.id) as b from x;

With MySQL I have to use the group_concat() but the field is then a String and the import fails to convert the String ("1,2,3,4,5)" to an array of Long.

Any idea how to solve this problem ?

Thanks ! Xavier

[jprante]

I do not support MySQL extensions with JDBC importer, group_concat is defined to return a string.

ES will index longs for you as you defined in the mapping, even if you pass strings, so there is not a big problem.

In most cases, you don't need constructs like group_concat, as JDBC importer is able to handle multi values for you.

[xavierfacq]

Hi,

I think that it's not possible because :

• MySQL doesn't support subquery to return multi-values
• When I use a group_concat() and return a String, with comma or not, there is an error during the import:
  message [MapperParsingException[failed to parse [subjective_contact_ids]]; nested: NumberFormatException[For input string: "0 0 0"]; ]

MapperParsingException[failed to parse [subjective_contact_ids]]; nested: NumberFormatException[For input string: "632021842, 0, 0"]; ]

If I try to return multi values the query error is : [11:24:11,460][ERROR][importer.jdbc.context.standard][pool-2-thread-1] java.sql.SQLException: Operand should contain 1 column(s)

[jprante]

Taking your example, you can just do

SELECT x.a as _id, y.b as b from x, y where x.id = y.ext_id;

for JDBC importer to create a numeric array b, so there is no need for something like group_concat

BTW MySQL can do subqueries http://dev.mysql.com/doc/refman/5.7/en/subqueries.html but that does not matter here.

[xavierfacq]

Whatever is the query format or driver, MySQL doesn't support to return multi-values for one field. One field => One value. That's why I have to use the group_concat(). Nevermind, I wrote my own importer and split the string when I need to.

What you can do is to add field params to inform your code that a field must split the input and convert to another format. (Look at Sphinx configuration : http://sphinxsearch.com/docs/latest/conf-sql-attr-multi.html)

[jprante]

JDBC importer supports multi-value select for all databases automatically, out of the box. The values can be string, integer, long, boolean etc.

This step is called row merging https://github.com/jprante/elasticsearch-jdbc/blob/master/src/main/java/org/xbib/elasticsearch/jdbc/strategy/standard/StandardSource.java#L781-L814 and an innovation of JDBC importer to overcome JDBC deficiencies. No need to instruct a database to return groups. lists, or something.

[wanghc85]

这是来自QQ邮箱的假期自动回复邮件。您好，我最近正在休假中，无法亲自回复您的邮件。我将在假期结束后，尽快给您回复。