第243回　MySQLのJSON_TABLE()を使ってJSONデータを行に分解する

JSON型は、配列やオブジェクト形式のデータを1つのカラムにそのまま保存できるという柔軟さがあり、MySQLにおいても8.0以降でそのサポートが大きく強化されました。これにより、従来のリレーショナルなテーブル設計だけでは扱いづらかった構造のデータにも対応しやすくなっています。

ただし、こうした柔軟な構造を活かしつつも、実際にクエリで中身を参照したり、要素ごとに展開したりしようとすると、一筋縄ではいかない場面もあります。

たとえば、1レコードに複数の商品をJSON配列として保持している場合、それらを個別の行として扱いたくなることがあるでしょう。そんなときに活用できるのが、MySQL 8.0.4以降で利用可能なJSON_TABLE()構文です。今回は、JSON配列を仮想的なテーブルとして扱えるJSON_TABLE()の使い方と活用方法について紹介します。

サンプルデータの準備

まずは、検証用にJSON配列を格納するテーブルを用意します。

CREATE TABLE orders (
   id INT PRIMARY KEY,
   customer_name VARCHAR(100),
   items JSON
);

INSERT INTO orders VALUES
 (1, 'Alice', JSON_ARRAY(
   JSON_OBJECT('product', 'Pen', 'qty', 2),
   JSON_OBJECT('product', 'Notebook', 'qty', 3)
 )),
 (2, 'Bob', JSON_ARRAY(
   JSON_OBJECT('product', 'Pencil', 'qty', 5)
 ));

今回はこのように、ordersテーブルのitemsカラムに複数の商品データをJSON配列として保存してみました。

JSON_EXTRACT()を使った基本的な抽出

まずは第197回　MySQLでJSONを活用してみる［その1］で紹介してあるとおり、JSON_EXTRACT()関数を使って抽出をしてみましょう。

mysql> SELECT JSON_EXTRACT(items, '$[0].product') FROM orders WHERE id = 1;
+-------------------------------------+
| JSON_EXTRACT(items, '$[0].product') |
+-------------------------------------+
| "Pen"                               |
+-------------------------------------+
1 row in set (0.00 sec)

SELECT id, JSON_EXTRACT(items, '$[0].product') AS product FROM orders
UNION ALL
SELECT id, JSON_EXTRACT(items, '$[1].product') AS product FROM orders;

+----+------------+
| id | product    |
+----+------------+
|  1 | "Pen"      |
|  2 | "Pencil"   |
|  1 | "Notebook" |
|  2 | NULL       |
+----+------------+
4 rows in set (0.00 sec)

特定の商品だけを取得したい場合、従来のJSON_EXTRACT()では配列の要素を1件ずつ指定しなければならず、扱いづらい面があります。

JSON_TABLE()を使って展開

JSON_TABLE()を使うと、JSON配列をテーブルのように扱って展開することができます。

mysql> SELECT
    ->   o.id,
    ->   o.customer_name,
    ->   jt.product,
    ->   jt.qty
    -> FROM orders o,
    -> JSON_TABLE(
    ->   o.items,
    ->   '$[*]' COLUMNS (
    ->     product VARCHAR(100) PATH '$.product',
    ->     qty INT PATH '$.qty'
    ->   )
    -> ) AS jt;
+----+---------------+----------+------+
| id | customer_name | product  | qty  |
+----+---------------+----------+------+
|  1 | Alice         | Pen      |    2 |
|  1 | Alice         | Notebook |    3 |
|  2 | Bob           | Pencil   |    5 |
+----+---------------+----------+------+
3 rows in set (0.00 sec)

1レコード（例ではid=1のAliceの行）のJSON配列が複数行に展開されて、通常のテーブルのように扱えるようになります。JSON_TABLE(json, '$path' COLUMNS (...))という構文で、JSON配列やオブジェクトの中身を列として取り出すことができます。JSON_TABLE()の構文は以下の通りです。

JSON_TABLE(json,'$path' COLUMNS() {on empty} {on error})

$[*]は配列のすべての要素を対象とするパス
COLUMNS内で各項目をSQLのカラム型にマッピング
PATHにJSON内のキーへのパスを指定

オプションとして{on empty}や{on error}にERROR ON ERRORやNULL ON ERRORなどのエラーハンドリング指定も可能です。

JSON_TABLEのエラー制御と安全な展開

JSONの中にキーが欠損している、またはNULL値が含まれている場合に備えて、エラー制御のオプションを使うと安全に展開できます。まずは検証用に、キーが欠損したデータを追加してみます。

INSERT INTO orders VALUES 
(3, 'Carol', JSON_ARRAY(
  JSON_OBJECT('product', 'Eraser'),
  JSON_OBJECT('qty', 10)
));

このデータには、1つ目の要素にqtyがなく、2つ目の要素にproductがありません。先ほどのJSON_TABLE()のクエリを実行すると、以下のようにproductとqtyでNULLの値が確認できます。

mysql> SELECT
    ->   o.id,
    ->   o.customer_name,
    ->   jt.product,
    ->   jt.qty
    -> FROM orders o,
    -> JSON_TABLE(
    ->   o.items,
    ->   '$[*]' COLUMNS (
    ->     product VARCHAR(100) PATH '$.product',
    ->     qty INT PATH '$.qty'
    ->   )
    -> ) AS jt;
+----+---------------+----------+------+
| id | customer_name | product  | qty  |
+----+---------------+----------+------+
|  1 | Alice         | Pen      |    2 |
|  1 | Alice         | Notebook |    3 |
|  2 | Bob           | Pencil   |    5 |
|  3 | Carol         | Eraser   | NULL |
|  3 | Carol         | NULL     |   10 |
+----+---------------+----------+------+
5 rows in set (0.00 sec)

そこで、以下のようにDEFAULTやNULL ON EMPTYを指定することで、安全に展開できます。

SELECT
  o.id,
  o.customer_name,
  jt.product,
  jt.qty
FROM orders o,
JSON_TABLE(
  o.items,
  '$[*]' COLUMNS (
    product VARCHAR(100) PATH '$.product'
      DEFAULT '"不明"' ON EMPTY
      DEFAULT '"不明"' ON ERROR,
    qty INT PATH '$.qty'
      DEFAULT '1' ON EMPTY
      DEFAULT '0' ON ERROR
  )
) AS jt;

+----+---------------+----------+------+
| id | customer_name | product  | qty  |
+----+---------------+----------+------+
|  1 | Alice         | Pen      |    2 |
|  1 | Alice         | Notebook |    3 |
|  2 | Bob           | Pencil   |    5 |
|  3 | Carol         | Eraser   |    1 |
|  3 | Carol         | 不明     |   10 |
+----+---------------+----------+------+

DEFAULT '"不明"' ON ERRORは、productの取得に失敗した場合に代替値を返す

実際に「一部のキーだけ入ってる⁠」⁠、「⁠不完全なJSON」も多いため、こうした指定が役立ちます。

ネストしたJSON構造を展開する

続いて、検証用のデータとして、次のようなネストされた構造のレコードをordersテーブルに追加してみます。

INSERT INTO orders VALUES
(4, 'Dave', JSON_OBJECT(
  'customer', JSON_OBJECT('name', 'Dave', 'email', 'dave@example.com'),
  'items', JSON_ARRAY(
    JSON_OBJECT('product', 'Marker', 'qty', 4),
    JSON_OBJECT('product', 'Stapler', 'qty', 1)
  )
));

このようにitemsがオブジェクト内にネストされた構造を持つ場合でも、JSON_TABLE()を使って展開することが可能です。複雑なJSONでは、値が入れ子（ネスト）になっていることがあります。その場合はJSON_EXTRACT()を使ってnestされたJSONを指定することで、対象のデータを取得することができます。

mysql> SELECT
    ->   o.id,
    ->   o.customer_name,
    ->   jt.product,
    ->   jt.qty
    -> FROM orders o,
    -> JSON_TABLE(
    ->   JSON_EXTRACT(o.items, '$.items'),
    ->   '$[*]' COLUMNS (
    ->     product VARCHAR(100) PATH '$.product',
    ->     qty INT PATH '$.qty'
    ->   )
    -> ) AS jt
    -> WHERE o.id = 4;
+----+---------------+---------+------+
| id | customer_name | product | qty  |
+----+---------------+---------+------+
|  4 | Dave          | Marker  |    4 |
|  4 | Dave          | Stapler |    1 |
+----+---------------+---------+------+

続いては以下のようなデータを追加してみましょう。

INSERT INTO orders VALUES
(5, 'Ellen', JSON_OBJECT(
  'items', JSON_ARRAY(
    JSON_OBJECT('product', 'Pen', 'tags', JSON_ARRAY('stationery', 'writing')),
    JSON_OBJECT('product', 'Stapler', 'tags', JSON_ARRAY('office', 'tools'))
  )
));

読みやすくするためにjqコマンドを実行すると、以下のようなJSONです。

{
  "items": [
    {
      "tags": [
        "stationery",
        "writing"
      ],
      "product": "Pen"
    },
    {
      "tags": [
        "office",
        "tools"
      ],
      "product": "Stapler"
    }
  ]
}

各商品のtagsが配列として入れ子になっている構造です。このような場合はNESTED PATHを利用します。

SELECT
  o.id,
  o.customer_name,
  jt.product,
  jt.tag
FROM orders o,
JSON_TABLE(
  JSON_EXTRACT(o.items, '$.items'),
  '$[*]' COLUMNS (
    product VARCHAR(100) PATH '$.product',
    NESTED PATH '$.tags[*]' COLUMNS (
      tag VARCHAR(100) PATH '$'
    )
  )
) AS jt
WHERE o.id = 5;

+----+---------------+---------+------------+
| id | customer_name | product | tag        |
+----+---------------+---------+------------+
|  5 | Ellen         | Pen     | stationery |
|  5 | Ellen         | Pen     | writing    |
|  5 | Ellen         | Stapler | office     |
|  5 | Ellen         | Stapler | tools      |
+----+---------------+---------+------------+
4 rows in set (0.00 sec)

ネストされた配列tagsに対してNESTED PATHを使うことで、1つのJSON_TABLE()の中で最上位の構造と外部結合を実施し、展開することが可能です。

パフォーマンス上の注意点

実際に実行計画を見てもらうとわかる通り、JSON_TABLE()は非常に便利な反面、実行時にJSONの展開処理が必要になるため、大きなJSONを大量に処理するようなケースでは、クエリの実行時間が増加することがあります。

大量データを扱う場面では、一時テーブルに展開しておく、または事前に正規化した構造へ保存しておくといった運用も検討しましょう。

まとめ

JSON_TABLE()を使うことで、従来は困難だったJSON配列の行展開がSQLで簡潔に実現できるようになりました。

実際にJSONで受け取ったイベントログや履歴の集計、正規化せず柔軟にスキーマ設計したいデータなどはJSONをそのまま格納しておき、必要に応じてJSON_TABLE()で分解するという運用も選択肢になります。データ量が多い場合などはパフォーマンスの問題が出たり、複雑なJSONでは工夫したクエリを書かなければならないかもしれませんが、JSON型を活用している方はぜひ一度試してみてください。

ON EMPTYやNESTED PATHの使い方など、より詳細な内容については、公式ドキュメントのJSON テーブル関数を参考にしてください。