Access nested fields in canvas markdown

Elastic Stack Kibana
1

Hi,

I'm able to use the text element on a canvas and use ES SQL to query to retrieve my document content.
Basically I have a single 'health report' document from another source and I'd like to create a simple dashboard.
I have 2 issues:

  1. The SQL appears to be scoped inside _source, so how can I retrieve the document ID? (I'd like to create a link to the document).
  2. I can only access fields at the root of _source in the markdown using syntax {{rows.[0].fieldname}}. When I want to access {{rows[0].some.nested.field}}, it never returns a value. Currently my workaround is to add in the select clause ",.some.nested.field as my_alias" and then I can retrieve the value using {{rows[0].my_alias}} but I'm guessing there's an easier way?

Thanks

Steve