Array of arrays

[dan-lind]

I've been playing around with the jdbc river for a bit, and I have a question. Is it currently possible in any way to create objects that contains lists within lists? My use case is that I have items, suppliers, and countries. Each item can have many suppliers, and each supplier can exist in many countries. The structure I'm looking for is along these lines:

{
   "item":{
      "id":12345,
      "description":"blue item",
      "supplier":[
         {
            "id":1408,
            "name":"supplier_1",
            "country":[
               {
                  "id":1,
                  "name":"sweden"
               },
               {
                  "id":2,
                  "name":"norway"
               }
            ]
         },
         {
            "id":1374,
            "name":"supplier_2",
            "country":[
               {
                  "id":2,
                  "name":"norway"
               }
            ]
         }
      ]
   }
}

I know that I can create lists using the bracket notation when naming columns, something like

select item.id as _id, item.desc as desc, supplier.id as supplier[id], supplier.name as supplier[name] from ...

But as far as I understand this only supports "one level", so I'm not able to create a list of countries on type of my suppliers in this example.

I also explored using multiple sql statements with the same _id, but this overwrites my document rather than updating/amending it. I understood that this behavior is default in ES.

Is there anything else that I can try, or did I miss anything in the reasoning above?

[Cadrach]

I am trying to achieve the same thing, I want to create a polygon geo_shape from a MySQL river, and the structure of the coordinates property is an Array of coordinates [[lat1, lng1], [lat2, lng2],...], so I would be interested in an answer too (if there is one of course :))

[jprante]

The issue of grouping the objects is repeating groups. There is no good solution available yet.

For the question about arrays of arrays, this will never be supported, ES is unable to index such JSON.