How to build query which contain foreign keys inside of JSON data (MySQL JSON columns)`

Hello, I have following two tables:

xml_advisors (id:INT, name:VARCHAR, techs:JSON)
xml_techtree (id:INT, name:VARCHAR)

Sample Data:
xml_advisors:

ID	NAME*	TECHS
1	3		{"tech":"Advisor_Aapep_C"}
2	15		{"tech":"Advisor_Aapep_E"}
xml_techtree:
ID	NAME*
1	Advisor_Aapep_C
6	AdvisorUniqueUnitEliteEle_E

As you can see xml_advisor can have many xml_techtree as TECHS column can contain also JSON with array of tech names. How I can build such association in CakePHP 3.5?

Finally I would like to use it in following way:

$advisors = TableRegistry::get('XmlAdvisors')->find()->contain(['XmlTechtreex'])->toArray();

I can easily get results which I need with following raw SQL:

SELECT 
  XmlAdvisors.id AS `XmlAdvisors__id`, 
  XmlAdvisors.item_level AS `XmlAdvisors__item_level`, 
  XmlAdvisors.techs AS `XmlAdvisors__techs`, 
  XmlTechtreex.id AS `XmlTechtreex__id`,
  XmlTechtreex.name AS `XmlTechtreex.name`
FROM 
  xml_advisors XmlAdvisors
LEFT JOIN 
  xml_techtreex as XmlTechtreex 
ON 
  JSON_SEARCH(XmlAdvisors.techs, 'one', XmlTechtreex.name) IS NOT NULL

Could you please help me? How should I create models when associations are done via data inside of JSON string? Is it supported by Cake?

Thank you.

something among the lines:

->leftJoin('XmlTechtreex', $query->newExpr()->isNotNull($query->func()->json_search(["XmlAdvisors.techs, 'one', XmlTechtreex.name" => 'literal'])))

note that json_search is magic function so you wont find it in docs

Hi!

Thank you for your response. Unfortunatelly building query in that way will not give me final results in form which comes from contain() - that is the biggest downside. After talk on IRC I came to the conclusion that I have to change my data in database as now building associations will be very hard in Cake.