JSON mysql - find in mysql by json field (sql, json, JSON_EXTRACT, order, select, yii2, yii)

JSON MySql.md

select

select * from cars WHERE JSON_EXTRACT(data, "$.color") = 'red';

order

select * from cars ORDER BY JSON_EXTRACT(data, "$.color") DESC;

yii2 JSON_EXTRACT.md

tuning_color - GridView column class

info - table name

data - JSON column name

color - parameter to sort

Example

info.data:

{"color":"red"}

Sort

$dataProvider->sort->attributes['tuning_color'] = [
    'asc' => ["JSON_EXTRACT(info.data, '$.color')" => SORT_ASC],
    'desc' => ["JSON_EXTRACT(info.data, '$.color')" => SORT_DESC],
];

Filter

->andFilterWhere([
  '=',
  "JSON_EXTRACT(info.data, '$.color')",
  (int) $this->tuning_color ?: $this->tuning_color
])