Cube schema for Nested Field in Elastic index

[Vigneshg98]

Consider I have the following data in my Elastic index. "hits" : [ { "_index" : "cubepoc", "_type" : "_doc", "_id" : "i1", "_score" : 1.0, "_source" : { "pname" : "john doe", "pconcern" : "cough and fever", "product" : [ { "medicine1" : "medomol", "medicine2" : "aspirin" } ] } } ]

So in order to access the product, I would need to do a nested query in elastic. How could I achieve the same through Cube.js schema?

The following is my cubejs schema.

cube(`Cubepoc`, {
  sql: `SELECT * FROM cubepoc`,

  joins: {},

  measures: {},

  dimensions: {
    pname: {
      sql: `pname`,
      type: `string`,
      title: `Patient Name`
    },
    pconcern: {
      sql: `pconcern`,
      type: `string`,
      title: `Patient Concern`
    },
    product: {
      sql: `product`,
      type: `string`,
      title: `Patient Product`
    }
  }
});

Since the product is of type nested in Elastic, defining it as string doesn't help to access it. Help me to generate the correct schema.

Thanks & Regards

[RusovDmitriy]

Hi Vignesh! So far ElasticSearch has limitations for SQL Access:

• Grouping isn’t (yet) compatible with nested fields
• Cannot use aggregate functions for grouping

The cubejs-elasticsearch-driver uses built-in SQL support, which imposes restrictions on the use of nested data type