MySQL は、JSON (JavaScript Object Notation) ドキュメント内のデータへの効率的なアクセスを可能にする、RFC 7159 によって定義されたネイティブ JSON データ型をサポートしています。 JSON データ型には、JSON 形式の文字列を文字列カラムに格納するよりも、次の利点があります:

簡単な使い方。ただ、データ型にJSONを指定するだけ。

mysql>  CREATE TABLE t1 (jdoc JSON);
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES('{"key1": "value1", "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)
INSERT INTO t1 VALUES('{"id": 123, "key2": "value2"}');
mysql> select * from t1;
+--------------------------------------+
| jdoc                                 |
+--------------------------------------+
| {"key1": "value1", "key2": "value2"} |
+--------------------------------------+
1 row in set (0.00 sec)

JSON値取得

JSON列の値ではなくその中の値を取得する方法。

二通りある。

カラムパス演算子

特定のキーを使用してこの特定の文を取得するにはカラムパス演算子 -> を使用します。

mysql> select jdoc->"$.key1" from t1;
+----------------+
| jdoc->"$.key1" |
+----------------+
| "value1"       |
+----------------+
1 row in set (0.00 sec)

インラインパス 演算子

カラムパス演算子はバックスラッシュは引用符とともにそのまま残ります。特定のキーを使用して、引用符やエスケープを含めずに目的の値を表示するには、次のようにインラインパス演算子 ->> を使用します。

mysql> select jdoc->>"$.key1" from t1;
+-----------------+
| jdoc->>"$.key1" |
+-----------------+
| value1          |
+-----------------+
1 row in set (0.00 sec)

JSON値検索

ふと気になって検証した内容です。

大文字小文字判定

MySQLだと設定しないと大文字小文字を判定されません。

BINARYと指定するとバイナリ比較となり思い通りの結果となる。

mysql> select "a" = "A";
+-----------+
| "a" = "A" |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select "A" = "A";
+-----------+
| "A" = "A" |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

mysql> select BINARY  "a" = "A" ;
+-------------------+
| BINARY  "a" = "A" |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.00 sec)

では、JSONの場合はどうなるでしょう。

mysql> show create table countryinfo\G;
*************************** 1. row ***************************
       Table: countryinfo
Create Table: CREATE TABLE `countryinfo` (
  `doc` json DEFAULT NULL,
  `_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT NULL,
  `_json_schema` json GENERATED ALWAYS AS (_utf8mb4'{"type":"object"}') VIRTUAL,
  PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> select * from countryinfo limit 1 \G;
*************************** 1. row ***************************
         doc: {"GNP": 828, "_id": "00005de917d80000000000000000", "Code": "ABW", "Name": "Aruba", "IndepYear": null, "geography": {"Region": "Caribbean", "Continent": "North America", "SurfaceArea": 193}, "government": {"HeadOfState": "Beatrix", "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"}, "demographics": {"Population": 103000, "LifeExpectancy": 78.4000015258789}}
         _id: 0x30303030356465393137643830303030303030303030303030303030
_json_schema: {"type": "object"}
1 row in set (0.00 sec)

KEY値の場合

JSON値のKEYは大文字小文字区別されます。

なので大文字小文字を間違うと結果が出ません。

mysql> SELECT * from countryinfo c where doc -> "$.Code"= "ABW"\G;
*************************** 1. row ***************************
         doc: {"GNP": 828, "_id": "00005de917d80000000000000000", "Code": "ABW", "Name": "Aruba", "IndepYear": null, "geography": {"Region": "Caribbean", "Continent": "North America", "SurfaceArea": 193}, "government": {"HeadOfState": "Beatrix", "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"}, "demographics": {"Population": 103000, "LifeExpectancy": 78.4000015258789}}
         _id: 0x30303030356465393137643830303030303030303030303030303030
_json_schema: {"type": "object"}
1 row in set (0.01 sec)

ERROR:
No query specified

mysql> SELECT * from countryinfo c where doc -> "$.code"= "ABW"\G;
Empty set (0.00 sec)

VALUE値の場合

JSON値のVALUEは大文字小文字区別されます。

mysql> SELECT * from countryinfo c where doc -> "$.Code"= LOWER("ABW")\G;
Empty set (0.00 sec)

ERROR:
No query specified

mysql> SELECT * from countryinfo c where doc -> "$.Code"= "ABW"\G;
*************************** 1. row ***************************
         doc: {"GNP": 828, "_id": "00005de917d80000000000000000", "Code": "ABW", "Name": "Aruba", "IndepYear": null, "geography": {"Region": "Caribbean", "Continent": "North America", "SurfaceArea": 193}, "government": {"HeadOfState": "Beatrix", "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"}, "demographics": {"Population": 103000, "LifeExpectancy": 78.4000015258789}}
         _id: 0x30303030356465393137643830303030303030303030303030303030
_json_schema: {"type": "object"}
1 row in set (0.00 sec)

これは設定とか関係なく、大文字小文字を区別するようになると思います。

（JSON列で大文字小文字を区別しない方法とかあったりするのでしょうか....気になりました）

JSON列INDEX

次はSQLでおなじみのINDEXはどうなるのかということです。

↓ドキュメントを見ていて下記の記述を見つけました。

生成された列にインデックスを付けてJSON列インデックスを提供する

他の場所で説明されているように、JSON 列に直接インデックスを付けることはできません。このような列を間接的に参照するインデックスを作成するには、次の例に示すように、インデックスを作成する必要のある情報を抽出する生成列を定義してから、生成された列にインデックスを作成します。

どうやら他のデータ型とは違い簡単にINDEXは貼れない模様。

mysql> CREATE TABLE jemp (
    ->     c JSON,
    ->     g INT GENERATED ALWAYS AS (c->"$.id"),
    ->     INDEX i (g)
    -> );
Query OK, 0 rows affected (0.28 sec)

mysql> INSERT INTO jemp (c) VALUES
     >   ('{"id": "1", "name": "Fred"}'), ('{"id": "2", "name": "Wilma"}'),
     >   ('{"id": "3", "name": "Barney"}'), ('{"id": "4", "name": "Betty"}');
Query OK, 4 rows affected (0.04 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT c->>"$.name" AS name
     >     FROM jemp WHERE g > 2;
+--------+
| name   |
+--------+
| Barney |
| Betty  |
+--------+
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT c->>"$.name" AS name
     >    FROM jemp WHERE g > 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: jemp
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

しかし解決方法がないわけではないです。

MySQL 8.0.21以降では、式を使用してクエリを最適化するために使用できる式を含む関数をJSON使用して、列にインデックスを作成することもできますJSON_VALUE()。詳細と例については、その関数の説明を参照してください。

JSON_VALUE

JSON_VALUE(json_doc, path)

指定されたドキュメントで指定されたパスでJSONドキュメントから値を抽出し、抽出された値を返します。オプションで、目的のタイプに変換します。完全な構文を次に示します。

mysql> CREATE TABLE t1(
    ->     j JSON,
    ->     INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES('{"id": 123, "key2": "value2"}');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+-------------------------------+
| j                             |
+-------------------------------+
| {"id": 123, "key2": "value2"} |
+-------------------------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM t1 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123\G;
*************************** 1. row ***************************
j: {"id": 123, "key2": "value2"}
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> explain SELECT * FROM t1 WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: i1
          key: i1
      key_len: 9
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

確かにINDEXが使用されているのが見れます。

INDEXの列やUPDATEする列は切り出す

これは余談ですが、先日視聴したEngineers in CARTA vol.2の中で少しだけJSONについてのお話がありました。

そのなかで興味深かった内容があったので記述します。

JSONの中身を検索する場合はJSON型として保持するのではなく、別カラムに切り出したほうが良いそうです。

これはよく更新するカラムに対しても同様だそうです。

https://www.youtube.com/watch?v=DXyTy98AWlA

気になった方はアーカイブをご覧ください。

ちなみに、該当の話は1:40:00あたりです。

〆

RDBで初めてJSON型を使いました。

SELECTするときとかJSONの中のKEYまで考えたりすると大変だなという印象です。

明日は@TomoProgさんの記事です。

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MySQLのJSON型ってどんな感じで扱うのの日。

初めに

目次

とある日

JSON型