MySQLのJSONをテーブルに変換して集計等を行う

JSON_TABLE()を使ってMySQLのJSONをテーブルに変換する

MySQL 8.0のJSONをテーブルに変換するにはJSON_TABLE()を使う。

例えば次のように、人が複数のスキルを持っていて、そのスキルを複数の評価者が点数付けすることを表すのに、正規化しないでJSONで定義しているとする。

{
  "skills": [
    {
      "name": "Python",
      "level": 3
    },
    {
      "name": "Java",
      "level": 4
    }
  ]
}

{
  "skills": [
    {
      "name": "Python",
      "level": 4
    },
    {
      "name": "Java",
      "level": 5
    }
  ]
}

{
  "skills": [
    {
      "name": "Python",
      "level": 1
    },
    {
      "name": "Shell",
      "level": 5
    }
  ]
}

各人のスキルレベルの評価平均を取得したい時、正規化していないがゆえにGROUP BYが簡単にできなくなっている。

そのような時にJSON_TABLE()を使ってJSONをテーブルに変換する。（書き方がCROSS JOINのようにも見えるが、実際にはLATERAL JOIN的な動きになっている。）

SELECT
    skill_name,       -- JSON_TABLE内で定義した名前
    AVG(skill_level)  -- JSON_TABLE内で定義した名前
FROM table1,
    JSON_TABLE(skill_evaluations, '$.skills[*]' COLUMNS (  -- 参照元のテーブルの直後にJSON_TABLEを配置
        skill_name  TEXT PATH '$.name',  -- 定義名 型 PATH JSONパス
        skill_level INT  PATH '$.level')
    ) x
WHERE person = 1
GROUP BY skill_name

結果は以下のようになる

型定義にはCOLLATE句をつけた方がいい

GROUP BYする時に、大文字小文字/ひらがなカタカナ等が同一視されたりされなかったり、MySQLのデフォルト照合順序の設定に依存してしまう。

意図通りの結果を得るために、TEXT型やVARCHAR型として定義した場合は、COLLATE句をつけた方がいい。

COLLATE句なし

COLLATE句なしだと以下のようにPythonとPYTHONが同一視されるかもしれない。

WITH table_1 AS (
  SELECT
      1 AS person,
      999 AS evaluator,
      '{"skills": [{"name": "Python", "level": 3}]}' AS skill_evaluations
  UNION ALL
  SELECT
      1 AS person,
      999 AS evaluator,
      '{"skills": [{"name": "PYTHON", "level": 4}]}' AS skill_evaluations
)
SELECT
    skill_name,
    AVG(skill_level)
FROM table1,
    JSON_TABLE(skill_evaluations, '$.skills[*]' COLUMNS (
        skill_name  TEXT PATH '$.name',
        skill_level INT  PATH '$.level')
    ) x
WHERE person = 1
GROUP BY skill_name

COLLATE句あり

skill_name TEXT PATH '$.name'

を

skill_name TEXT COLLATE utf8mb4_bin PATH '$.name'

とすれば、常に以下の結果が得られる。