Home > Software engineering >  JOIN on JSON_KEYS
JOIN on JSON_KEYS

Time:10-14

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;
  • Related