Mysql/json

JSONオブジェクトを作る

mysql5.7以降

-- JSON型を使用したテーブル作成
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profile JSON NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- JSONデータの挿入
INSERT INTO users (profile) VALUES 
  ('{"name": "山田太郎", "age": 30, "address": {"city": "東京", "zip": "100-0001"}}'),
  ('{"name": "佐藤花子", "age": 25, "hobbies": ["読書", "旅行"]}');

-- JSONデータの検索
SELECT id, profile->"$.name" AS name, profile->"$.age" AS age
FROM users
WHERE profile->"$.age" > 25;

参考：https://blog.s-style.co.jp/2017/05/301/

mariadbではこう書く

SELECT 
    id, 
    JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS name, 
    JSON_EXTRACT(profile, '$.age') AS age
FROM users
WHERE JSON_EXTRACT(profile, '$.age') > 25;
+----+--------------+------+
| id | name         | age  |
+----+--------------+------+
|  1 | 山田太郎     | 30   |
+----+--------------+------+