# JSON 型
## 例
```json
{
"職務スキル": "Java",
"プロジェクト経験": "3年以上"
}
```
```sql
SELECT * FROM t1 WHERE data->"$.職務スキル" = "Java";
```
## JSON 型 + INDEX
```sql
ALTER TABLE members
ADD INDEX idx_custom_skills ((JSON_UNQUOTE(custom_choices->"$.職務スキル")));
```
### 検討
- パフォーマンス
- 上記インデックスの利用
- 高度な集計には不向き
- 対策：集計結果を非正規化キャッシュテーブルに保存しておくなど
## JSON型 + JSON_TABLE
- [MySQL :: MySQL 8.4 Reference Manual :: 14.17.6 JSON Table Functions](https://dev.mysql.com/doc/refman/8.4/en/json-table-functions.html)
- Extracts data from a JSON document and returns it as a relational table having the specified columns.
### データ構造例
```sql
CREATE TABLE members (
id INT PRIMARY KEY,
name VARCHAR(100),
custom_fields JSON
);
-- サンプルデータの挿入
INSERT INTO members (id, name, custom_fields) VALUES
(1, 'Alice', '{"職務スキル": "Java", "プロジェクト経験": "3年以上"}'),
(2, 'Bob', '{"職務スキル": "Python", "プロジェクト経験": "1-3年"}'),
(3, 'Charlie', '{"職務スキル": "Java", "プロジェクト経験": "1-3年"}');
```
### クロス集計
```sql
SELECT
jt.職務スキル,
jt.プロジェクト経験,
COUNT(m.id) AS member_count
FROM
members AS m,
JSON_TABLE(
m.custom_fields,
"
quot;
COLUMNS (
職務スキル VARCHAR(100) PATH "$.職務スキル",
プロジェクト経験 VARCHAR(100) PATH "$.プロジェクト経験"
)
) AS jt
GROUP BY
jt.職務スキル,
jt.プロジェクト経験;
```
| 職務スキル | プロジェクト経験 | member_count |
| ---------- | ---------------- | ------------ |
| Java | 3年以上 | 1 |
| Python | 1-3年 | 1 |
| Java | 1-3年 | 1 |
### 制約
- `JSON_TABLE` で展開した列自体にはインデックスを直接適用できない
- JSON データ内の特定フィールドに対しては、 `JSON_EXTRACT` などを用いて部分インデックスを作成可能
- データが増えると効果は限定的
- 更新処理には非対応
- データ量に応じてパフォーマンス低下
## データ量
- 目安： 10 万件くらいまで
- 増えても数十万件まで
- 100 万件いくと厳しい
- `company_id` などで先に絞り込めれば大丈夫なはず
### 先に絞り込む例
- `company_id` に INDEX が貼られていること
- 絞り込んだ結果が数百件以下に収まること
```sql
SELECT
jt.職務スキル,
jt.プロジェクト経験,
COUNT(m.id) AS member_count
FROM
members AS m
JOIN
JSON_TABLE(
m.custom_fields,
"quot;
COLUMNS (
職務スキル VARCHAR(100) PATH "$.職務スキル",
プロジェクト経験 VARCHAR(100) PATH "$.プロジェクト経験"
)
) AS jt
WHERE
m.company_id = 123
GROUP BY
jt.職務スキル,
jt.プロジェクト経験;
```
## キャッシュテーブル
[[キャッシュテーブル]]
## Validation
[[JSON 型 + Validation]]