Tried making this work:
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;
Table Data:
taxi
-----------------------------------------
| id | info |
|---------------------------------------|
| 1 | {'33': 'foo', '64': 'bar'} |
-----------------------------------------
taxi_model
---------------------
| id | name |
|-------------------|
| 33 | 'blueTaxi'|
| 64 | 'redTaxi' |
---------------------
But not returning blueTaxi
nor redTaxi
.
When doing a simple select on JSON_KEYS(t.info), it returns a proper array of taxi_model ids.
Maybe I need to cast JSON_KEYS result to something before the JSON_CONTAINS?
Any help will be appreciated
Version: 5.7.12-log
CodePudding user response:
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;