Cube.js: issue with query generator for Mongobi for nested fields in document

[krunalsabnis]

error while executing query with nested fields.

Error while querying: {"queryKey":["SELECT details.name flow.detailsname, count(*) flow.count FROM flow AS flow GROUP BY 1 ORDER BY 2 DESC LIMIT 10000",[],[]],"error":"Error: Unknown column 'details.name' in 'field list'

For schema file below

cube(Flow, { sql:SELECT * FROM 1224.flow`,

joins: {

},

measures: {
count: {
type: count,
drillMembers: [createdbyuser, details.name, projectid, createddate, modifieddate]
}
},

dimensions: {
details.owner: {
sql: details.owner,
type: string
},

createdbyuser: {
  sql: `createdByUser`,
  type: `string`
},

details.description: {
  sql: `details.description`,
  type: `string`
},

details.name: {
  sql: `details.name`,
  type: `string`
},
`

For mongobi query, the nested fields needs to be inside `` I tested this with mysql workbench connecting to mongo over same BI Connectors.
I think, may be query generator needs to handle this.

working version of same query from mysql test
SELECTdetails.nameflow.detailsname, count(*)flow.countFROM flow AS flow GROUP BY 1 ORDER BY 2 DESC LIMIT 10000

[paveltiunov]

@krunalsabnis Hey Krunal! I believe you can escape details.name in sql field like this:

detailsName: {
  sql: `\`details.name\``,
  type: `string`
},

[krunalsabnis]

@paveltiunov
Yes that works !

[paveltiunov]

@krunalsabnis Great to hear that!

[paveltiunov]

Actually would leave this open as we'd need to handle this case at Scaffolding level.