JOIN on JSON_KEYS

Question

试过了:

SELECT tm.name
FROM taxi t
JOIN taxi_model tm ON JSON_CONTAINS(JSON_KEYS(t.info), CAST(tm.id AS JSON))
WHERE t.id = 1;

表数据:

taxi
-----------------------------------------
| id    | info                          |            
|---------------------------------------|
| 1     | {'33': 'foo', '64': 'bar'}    |
-----------------------------------------
taxi_model
---------------------
| id    | name      |            
|-------------------|
| 33    | 'blueTaxi'|
| 64    | 'redTaxi' |
---------------------

但不返回blueTaxi和redTaxi。当对JSON_KEYS(t.f info)执行一个简单的选择时，它返回一个适当的tax_model id数组。也许我需要在JSON_CONTAINS之前将JSON_KEYS结果转换为某些东西?

任何帮助都将不胜感激

版本:5.7.12-log

Answer 1

SELECT tm.name FROM taxi t
JOIN taxi_model tm ON JSON_CONTAINS(JSON_KEYS(t.info),   JSON_ARRAY(CAST(tm.id AS char)))
WHERE t.id = 1;