Are joins possible between Elasticsearch and MySQL when first has deeper JSON objects?

[cbarbieru]

Hi,

I'm new user and advocate of Trino inside my org. I've recently stumbled upon the need to join data from a JSON path in Elasticsearch with some MySQL column.

The way I read in connector docs, once you send "native" pass-through queries (and just get a JSON back), there's no way anymore you could use the power of Analyzer/Planner to join the 2 data sources, is this understanding correct?

As a simple example, say I have
elastic 'Person' index

{
    "person": {
        "phone": "id of cool brand"
    }
}

mysql 'Phone' table with columns

id
make
model

I would need something like below
SELECT * FROM elastic.Person pe JOIN mysql.Phone ph ON ph.id = pe.person.phone;