MySQL Data Type

空間情報(GIS)

 (x, y)や、(緯度, 軽度)の組み合わせで表現される座標情報のことを「空間情報」や「位置情報」と呼ばれる。

空間情報の型

型名	意味
POINT	点
LINESTRING	線
POLYGON	面
GEOMETORY	上記すべてを扱い可能

型名	意味
MULTIPOINT	点の集合
MULTILINESTRING	線の集合
MULTIPOLYGON	面の集合
GEOMETORYCOLLECTION	上記集合のすべてを扱い可能

POINT

 2つの軸(X軸、 Y軸)の値を表す2つの数字からなる

LINESTRIING

 複数のPOINTを指定し、それらをつないだ線。必ずしも1つの直線というわけではなく、3点以上を次々と結んだ型を表せる。

POLYGN

 複数のPOINTを次々と結び、最後に始点に戻ってくるもの。中をくりぬくことも可能

GEOMETORYCOLLECTION

 POINTE/LINESTRING/POLYGONのすべての型を格納することができる型

MULTI- GEOMETORYCOLLECTION

 それぞれの集合を扱える型

空間情報の表し方

 MySQLでは、空間情報の3つの表現を知っておくと便利。

• WKT : 人間にとってわかりやすい表現WKT(Well Known Text)
• WKB : WKB(Well Known Binary)
• MySQLの内部表現 : MySQL内部表現はバイナリ型の表現

GEOMETRY型のカラムを持つテーブルを作成し、データを登録

mysql> CREATE TABLE geo (id INTEGER, pos GEOMETRY);

mysql> INSERT INTO geo VALUES (1, ST_GeomFromText('POINT(3 5)'));
mysql> INSERT INTO geo VALUES (2, ST_GeomFromText('LINESTRING(3 5, 4 7, 5 2)'));
mysql> INSERT INTO geo VALUES (3, ST_GeomFromText('POLYGON((1 1, 5 1, 1 5, 1 1))'));

中身確認

mysql> SELECT * FROM geo;
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id   | pos                                                                                                                                                                  |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|    1 | 0x00000000010100000000000000000008400000000000001440                                                                                                                 |
|    2 | 0x000000000102000000030000000000000000000840000000000000144000000000000010400000000000001C4000000000000014400000000000000040                                         |
|    3 | 0x0000000001030000000100000004000000000000000000F03F000000000000F03F0000000000001440000000000000F03F000000000000F03F0000000000001440000000000000F03F000000000000F03F |
+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+

 INSERT文で、POINT(3, 5)という人間にわかりやすい表現(= WKT)に対して、ST_GeomFromText()という関数を使用。この関数が、WKTからMySQL内部表現へと変換する関数。

 MySQL内部表現 → WKTへの変換する関数も存在する。ST_AsText()という関数が用意されている。次のように、空間参照型のカラムに対してこの関数を使用することで、人間に読みやすい形式結果を得ることが可能。

mysql> SELECT id, ST_AsText(pos) FROM geo;
+------+----------------------------+
| id   | ST_AsText(pos)             |
+------+----------------------------+
|    1 | POINT(3 5)                 |
|    2 | LINESTRING(3 5,4 7,5 2)    |
|    3 | POLYGON((1 1,5 1,1 5,1 1)) |
+------+----------------------------+

MySQLの空間情報の関数

 空間情報を扱う関数の多くは、ST*で始まる名前。

ST_Distance

 2つの点(1,1)と(3,2)の距離を求める。

mysql> SELECT ST_Distance(ST_GeomFromText('POINT(1 1)'),
                          ST_GeomFromText('POINT(3 2)')) AS dist;
+------------------+
| dist             |
+------------------+
| 2.23606797749979 |
+------------------+

ST_Envelope

 与えられた座標情報に外接する四角形を返す。ST_AsText関数を使い、見やすい結果にできる。

mysql> SELECT ST_AsText(
           ST_Envelope( ST_GeomFromText('LINESTRING(1 2, 2 5, 3 1)') )
         ) AS mbr;
+--------------------------------+
| mbr                            |
+--------------------------------+
| POLYGON((1 1,3 1,3 5,1 5,1 1)) |
+--------------------------------+

 MySQLで空間情報を扱う際には、WKTとMySQL内部バイナリの違いをしっかりと認識しておく。

地球上の位置

 測地系を用いた地球上の位置の表し方には、緯度と軽度を用いた「地理座標系」と、決められた範囲ごとの原点座標からの北/東角方向への距離で表した「投影座標系」がある。

 各座標系には、SRID(SRS ID)と呼ばれる一意の数字が割り振られている。

SRIDを指定した例

mysql> CREATE TABLE geo2 (id integer, pos GEOMETRY SRID 6668);
mysql> INSERT INTO geo2 VALUES (1, ST_GeomFromText('POINT(43.06416667 141.3469444)', 6668));

 MySQLで地球上の緯度と軽度を用いた表現は、(緯度 軽度)の順で表す。postGISなどと異なるので、相互に使用したり移行をしたりする際には注意が必要。

取得結果

mysql> select * from geo2;
+------+------------------------------------------------------+
| id   | pos                                                  |
+------+------------------------------------------------------+
|    1 | 0x0C1A00000101000000F970242B1AAB614055920A9D36884540 |
+------+------------------------------------------------------+

地球上の2点間の距離を取得

• 北海道庁 : 東経 141.3469444 度、 北緯 43.06416667 度
• 沖縄県庁 : 東経 127.6811111 度、 北緯 26.21250000 度

mysql> SELECT ST_Distance( ST_GeomFromText('POINT(43.06416667 141.3469444)', 6668), ST_GeomFromText('POINT(26.21250000 127.6811111)', 6668), "kilometre") AS dist;
+--------------------+
| dist               |
+--------------------+
| 2243.8430513104017 |
+--------------------+

 地球上の位置を手軽に扱える便利機能。

JSON

 MySQLより遅れて世に出ている。2001年ぐらいに命名されたとされ、その後標準化される。

• 出発は、「ブラウザ上でJavaScriptを使用し、ダイナミックなページを実現する」

• ただのデータの記述ルールに過ぎず、それを受け取り、送信、加工しているのはJavaScript

• JavaScriptの爆発的な普及と発展とともに、この記述ルールが使用される

• 現在、Webサーバーとブラウザ間だけのやり取りにと留まらず、サーバー同士のやり取りや、一種のデータ操作にも使用されている

JSONの利点

• テキスト文字列で記述可能

• リレーショナルデータベースのようにカラムの設計の中身のデータを合わせる制約がない

• 自由な項目数。すべての行が同じ項目を持つ必要がない

• 自由な型指定。文字列、値、配列をミックス可能。キー・バリュー式のミックスも可能

 状況に応じて、値や型を追加、削除できるのが特徴。

JSONの利用

 MySQLでのJSONの機能。

カラムとデータ

 テーブル作成。

mysql> CREATE TABLE menu (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  j  JSON
) DEFAULT CHARSET=utf8mb4;

 データ挿入。

mysql> INSERT INTO menu(j) VALUES
  ( JSON_OBJECT('name', 'fried set meal', 'price', 1700) )
, ( JSON_OBJECT('name', 'box lunch', 'price', 500) )
, ( JSON_OBJECT('name', 'rolled natto', 'price', 300) )
, ( JSON_OBJECT('name', 'soy sauce ramen', 'price', 800) )
, ( '{"name":"melon", "price": 3000}' )
, ( '{"name":"curry bun", "price": 300}' )
, ( '{"name":"manju", "price": 200}' )
, ( '{"name":"icecream", "price": 400}' )
;

 データ取得

mysql> SELECT * FROM menu;
+----+-------------------------------------------+
| id | j                                         |
+----+-------------------------------------------+
|  1 | {"name": "fried set meal", "price": 1700} |
|  2 | {"name": "box lunch", "price": 500}       |
|  3 | {"name": "rolled natto", "price": 300}    |
|  4 | {"name": "soy sauce ramen", "price": 800} |
|  5 | {"name": "melon", "price": 3000}          |
|  6 | {"name": "curry bun", "price": 300}       |
|  7 | {"name": "manju", "price": 200}           |
|  8 | {"name": "icecream", "price": 400}        |
+----+-------------------------------------------+

 JSONの記載方法は、JSON_OBJECT関数を使用しても、そのまま文字列で与えても構わない。JSONに記述スロ日本語文字列はUTF-8エンコーディングとRFC 8259で決められている。MySQLのJSON型は、強制的にUTF-8で保存するように作られている。仮にMySQLのクライアントがEUC-JPで動いていても、自動変換が働く。

※ UTF-8が前提のカラムになるので、CREATE TABLEやALTER TABLE時にJSONカラムに対してCHARACTER SETを指定する必要はない。逆にCHARCTER SETを指定すると文法エラーになる。

値の抽出

 JSONの各部分を取り出す。

mysql> SELECT JSON_EXTRACT(j, '$.name', '$.price') FROM menu;
+--------------------------------------+
| JSON_EXTRACT(j, '$.name', '$.price') |
+--------------------------------------+
| ["fried set meal", 1700]             |
| ["box lunch", 500]                   |
| ["rolled natto", 300]                |
| ["soy sauce ramen", 800]             |
| ["melon", 3000]                      |
| ["curry bun", 300]                   |
| ["manju", 200]                       |
| ["icecream", 400]                    |
+--------------------------------------+

 JSON形式を取り出せる。

 JSONの名前をSQL文中で指定するは、'$.名前'と書く。指定を文字列として与えていること、'$.'を使用していることに注意。

 nameの値のSQLの結果を受け取る。JSON_EXTRACT()を使用する。

mysql> SELECT JSON_EXTRACT(j, '$.name') FROM menu;
+---------------------------+
| JSON_EXTRACT(j, '$.name') |
+---------------------------+
| "fried set meal"          |
| "box lunch"               |
| "rolled natto"            |
| "soy sauce ramen"         |
| "melon"                   |
| "curry bun"               |
| "manju"                   |
| "icecream"                |
+---------------------------+

 nameの値の文字列に"が付く。SQL検索をかけてヒットさせるために"を外す。

JSON_EXTRACT()

mysql> SELECT JSON_UNQUOTE( JSON_EXTRACT(j, '$.name') ) FROM menu;
+-------------------------------------------+
| JSON_UNQUOTE( JSON_EXTRACT(j, '$.name') ) |
+-------------------------------------------+
| fried set meal                            |
| box lunch                                 |
| rolled natto                              |
| soy sauce ramen                           |
| melon                                     |
| curry bun                                 |
| manju                                     |
| icecream                                  |
+-------------------------------------------+

 "が外れて、取得できる。MySQLでJSONを取り出すときは、JSON_UNQUOTE()が必要な場合があると認識しておく。

JSON_QUOTE()

 JSONのルールでエスケープが必要な文字が含まれているときに使用する。

 "で文字そのものを入れたいときは、以下のように使用する。

mysql> SELECT JSON_QUOTE('"s"');
+-------------------+
| JSON_QUOTE('"s"') |
+-------------------+
| "\"s\""           |
+-------------------+

JSON_EXTRACT()、JSON_QUOTE()の合わせ技

 MySQL8.0では、簡便な記法が用意されている。

カラム名->>JSON部分指定

 UNQUOTEした値が得られる。

mysql> SELECT j->>'$.name' FROM menu;
+-----------------+
| j->>'$.name'    |
+-----------------+
| fried set meal  |
| box lunch       |
| rolled natto    |
| soy sauce ramen |
| melon           |
| curry bun       |
| manju           |
| icecream        |
+-----------------+

カラム名->JSON部分指定

 >が1つになると、JSON文字列表記で得られる。

mysql> SELECT j->'$.name' FROM menu;
+-------------------+
| j->'$.name'       |
+-------------------+
| "fried set meal"  |
| "box lunch"       |
| "rolled natto"    |
| "soy sauce ramen" |
| "melon"           |
| "curry bun"       |
| "manju"           |
| "icecream"        |
+-------------------+

 この記法、関数、文字列には使用できない制限があり、次の場合エラーになる。

mysql> SET @j:='{"a":"x"}';
mysql> SELECT @j->>'$.a';
mysql> SELECT '{"a":"x"}'->>'$.a';
mysql> SELECT CAST('{"a":"x"}' as JSON)->>'$.a';
mysql> SELECT JSON_QBJECT("a","x")->>'$.a';

 上記のSELECTで以下のエラー。

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '->>'$.a'' at line 1

値の抽出 WHERE

 JSONの抽出記述は、SELECTの選択項目以外にも使用できる。

 「melon」を指定して、取得。

mysql> SELECT * FROM menu WHERE j->>'$.name' = 'melon';
+----+----------------------------------+
| id | j                                |
+----+----------------------------------+
|  5 | {"name": "melon", "price": 3000} |
+----+----------------------------------+

 「ramen」が、含まれているものを取得。

mysql> SELECT * FROM menu WHERE j->>'$.name' LIKE '%ramen%';
+----+-------------------------------------------+
| id | j                                         |
+----+-------------------------------------------+
|  4 | {"name": "soy sauce ramen", "price": 800} |
+----+-------------------------------------------+

 500円以上のデータを取得。

mysql> SELECT * FROM menu WHERE j->>'$.price' >= 500;
+----+-------------------------------------------+
| id | j                                         |
+----+-------------------------------------------+
|  1 | {"name": "fried set meal", "price": 1700} |
|  2 | {"name": "box lunch", "price": 500}       |
|  4 | {"name": "soy sauce ramen", "price": 800} |
|  5 | {"name": "melon", "price": 3000}          |
+----+-------------------------------------------+

JSONに追加、JSON配列

 メニューを整理するために、JSONデータに、categoryという名前と値のペアを導入する。1つの商品は複数のカテゴリーに属する場合がある。

 以下の構造を追加する。

"category": ["カテゴリー名1", "category2", ...]

 categoryの値を配列にしている。MySQLではJSONの配列を作成する場合は、JSON_ARRAY()を使用する。

mysql> UPDATE menu SET j = JSON_INSERT(j, '$.category', JSON_ARRAY("sweets", "fruits")) WHERE id IN(5);

mysql> UPDATE menu SET j = JSON_INSERT(j, '$.category', JSON_ARRAY("sweets", "japanese sweets")) WHERE id IN(7);

mysql> UPDATE menu SET j = JSON_INSERT(j, '$.category', JSON_ARRAY("sweets", "ice")) WHERE id IN(8)

 取得する。

mysql> SELECT * FROM menu WHERE id IN(5,7,8);
+----+----------------------------------------------------------------------------+
| id | j                                                                          |
+----+----------------------------------------------------------------------------+
|  5 | {"name": "melon", "price": 3000, "category": "sweets"}                     |
|  7 | {"name": "manju", "price": 200, "category": ["sweets", "japanese sweets"]} |
|  8 | {"name": "icecream", "price": 400, "category": ["sweets", "ice"]}          |
+----+----------------------------------------------------------------------------+

 配列部分にJSON_ARRAY()ではなく文字列で与えるとする。

mysql> SELECT JSON_INSERT('{"a":"xxx"}', '$.c', '["sweets", "fruits"]') as j;
+----------------------------------------------+
| j                                            |
+----------------------------------------------+
| {"a": "xxx", "c": "[\"sweets\", \"fruits\"]"} |
+----------------------------------------------+
1 row in set (0.00 sec)

 「JSON配列」てして与えたい部分が、MySQLでは「1つの文字列」てして扱われる。配列全体が"で囲まれているのでただの文字列。

 JSON_OBJECT()を使ってもうまくいかない。

mysql> SELECT JSON_MERGE( '{"a":"xxx"}'
            , JSON_OBJECT("c", '["sweets", "fruits"]') ) as j;
+----------------------------------------------+
| j                                            |
+----------------------------------------------+
| {"a": "xxx", "c": "[\"sweets\", \"fruits\"]"} |
+----------------------------------------------+

 CAST()を使用するとうまく解釈される。

mysql> SELECT JSON_INSERT('{"a":"xxx"}'
          , '$.c', CAST('["sweets", "fruits"]' AS JSON) ) AS j;
+-----------------------------------------+
| j                                       |
+-----------------------------------------+
| {"a": "xxx", "c": ["sweets", "fruits"]} |
+-----------------------------------------+

JSON配列指定

mysql> SELECT JSON_EXTRACT('["a","b","c"]', '$[2]');
+---------------------------------------+
| JSON_EXTRACT('["a","b","c"]', '$[2]') |
+---------------------------------------+
| "c"                                   |
+---------------------------------------+

 $[2]は、「配列の3番目」という意味。配列の位置指定は0スタート。多次元配列でも使用可能。

mysql> SELECT JSON_EXTRACT('["a","b",["x","y","z"]]', '$[2][1]');
+----------------------------------------------------+
| JSON_EXTRACT('["a","b",["x","y","z"]]', '$[2][1]') |
+----------------------------------------------------+
| "y"                                                |
+----------------------------------------------------+

JSON値削除、入れ替え

 以下のように変更する。

mysql> SELECT j FROM menu WHERE id=2;
+-------------------------------------+
| j                                   |
+-------------------------------------+
| {"name": "box lunch", "price": 500} |
+-------------------------------------+

mysql> UPDATE menu SET j = 
        JSON_INSERT(j, '$.cat', JSON_ARRAY("sweets", "lunch box"))
        WHERE id = 2;

mysql> SELECT j FROM menu WHERE id=2\G
*************************** 1. row ***************************
j: {"cat": ["sweets", "lunch box"], "name": "box lunch", "price": 500}

 商品は弁当なのに、投入データはsweetsにも属しているので、カテゴリーから削除。

 JSONの要素を削除するには、JSON_REMOVE()を使用。

mysql> UPDATE menu SET j=JSON_REMOVE(j, '$.cat[0]') WHERE id=2;

mysql> SELECT * FROM menu WHERE id=2\G
*************************** 1. row ***************************
id: 2
 j: {"cat": ["lunch box"], "name": "box lunch", "price": 500}
1 row in set (0.00 sec)

 '$.cat[0]'は、「catの値として指定された配列の一番最初」。

 catではなく、categoryになっているので変更する。

mysql> UPDATE menu SET j = 
        JSON_INSERT(JSON_REMOVE(j, '$.cat'), '$.category', JSON_EXTRACT(j, '$.cat'))
       WHERE id=2;

mysql> SELECT * FROM menu WHERE id=2\G
*************************** 1. row ***************************
id: 2
 j: {"name": "box lunch", "price": 500, "category": ["lunch box"]}

 以下の処理を行っている。

①. $.catを削除

②. 消した残りに$.categoryを追加

③. $.categoryの値は、現状の$.catの値

上書き／追加

JSONの値を変更する方法

	JSON_INSERT()	JSON_REPLACE()	JSON_SET()
新規に「名前：値」を追加	する	ー	ー
現像する値を上書き	ー	する	する

 新しくcommentという名前と値を入れる。

 JSON_PRETTY()で整形して見やすくしてくれる。

mysql> SELECT JSON_PRETTY(j) as j FROM menu WHERE id=7\G
*************************** 1. row ***************************
j: {
  "name": "manju",
  "price": 200,
  "category": [
    "sweets",
    "japanese sweets"
  ]
}

mysql> SELECT JSON_INSERT(j, '$.comment', "very tasty buns") as i FROM menu WHERE id=7\G

*************************** 1. row ***************************
i: {"name": "manju", "price": 200, "comment": "very tasty buns", "category": ["sweets", "japanese sweets"]}

mysql> SELECT JSON_REPLACE(j, '$.comment', "very tasty buns" ) as r FROM menu WHERE id=7\G
*************************** 1. row ***************************
r: {"name": "manju", "price": 200, "category": ["sweets", "japanese sweets"]}

mysql> SELECT JSON_SET(j, '$.comment', "very tasty buns" ) as s FROM menu WHERE id=7\G
*************************** 1. row ***************************
s: {"name": "manju", "price": 200, "comment": "very tasty buns", "category": ["sweets", "japanese sweets"]}

 JSON_REPLACEだけは、元の情報が存在しないので、commentが作成できない。

 $.nameを変更する

mysql> SELECT j FROM menu WHERE id=7\G
*************************** 1. row ***************************
j: {"name": "manju", "price": 200, "category": ["sweets", "japanese sweets"]}

mysql> SELECT JSON_INSERT(j ,'$.name', "bean-curd") as i FROM menu WHERE id=7\G
*************************** 1. row ***************************
i: {"name": "manju", "price": 200, "category": ["sweets", "japanese sweets"]}

mysql> SELECT JSON_REPLACE(j, '$.name', "bean-curd") as r FROM menu WHERE id=7\G
*************************** 1. row ***************************
r: {"name": "bean-curd", "price": 200, "category": ["sweets", "japanese sweets"]}

mysql> SELECT JSON_SET(j, '$.name', "burn-curd") as s FROM menu WHERE id=7\G
*************************** 1. row ***************************
s: {"name": "burn-curd", "price": 200, "category": ["sweets", "japanese sweets"]}

 JSON_INSERT()は既存の値を上書きしないので、$.nameを変更できない。

追加：配列の場合

 JSON_ARRAY_APPEND()配列の最後に、値を入れる。

mysql> SELECT JSON_ARRAY_APPEND(j, '$.category', "heat") FROM menu WHERE id in (1,7,8);
+------------------------------------------------------------------------------------+
| JSON_ARRAY_APPEND(j, '$.category', "heat")                                         |
+------------------------------------------------------------------------------------+
| {"name": "fried set meal", "price": 1700}                                          |
| {"name": "manju", "price": 200, "category": ["sweets", "japanese sweets", "heat"]} |
| {"name": "icecream", "price": 400, "category": ["sweets", "ice", "heat"]}          |
+------------------------------------------------------------------------------------+

 JSON_ARRAY_INSERT()は配列の指定されたー位置に値を入れる。存在しない位置を指定されると無視される。

mysql> SELECT JSON_ARRAY_INSERT(j, '$.category[1]', "heat") FROM menu WHERE id in (1,7,8);
+------------------------------------------------------------------------------------+
| JSON_ARRAY_INSERT(j, '$.category[1]', "heat")                                      |
+------------------------------------------------------------------------------------+
| {"name": "fried set meal", "price": 1700}                                          |
| {"name": "manju", "price": 200, "category": ["sweets", "heat", "japanese sweets"]} |
| {"name": "icecream", "price": 400, "category": ["sweets", "heat", "ice"]}          |
+------------------------------------------------------------------------------------+

トリッキーな使用方法

JSONの1要素をカラムとして独立させる

mysql>　ALTER TABLE menu
        ADD name VARCHAR(255) GENERATED ALWAYS AS(j->>'$.name') STORED
        ;

mysql> SHOW CREATE TABLE menu\G
*************************** 1. row ***************************
       Table: menu
Create Table: CREATE TABLE `menu` (
  `id` int NOT NULL AUTO_INCREMENT,
  `j` json DEFAULT NULL,
  `name` varchar(255) GENERATED ALWAYS AS (json_unquote(json_extract(`j`,_utf8mb3'$.name'))) STORED,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

 GENERATED ALWAYSを指定したカラムを追加。これにより、menu.nameカラムは、JSONのnameを参照する。

JSON配列をテーブルのようにする

mysql> SELECT * FROM
         JSON_TABLE(
             '[{"a":1,"b":2}, {"a":99,"b":44}]'
             , '$[*]' COLUMNS (ia int PATH '$.a', ib int PATH '$.b')
         ) as t;
+------+------+
| ia   | ib   |
+------+------+
|    1 |    2 |
|   99 |   44 |
+------+------+

 JSON_TABLE()で作ったテーブルは、ほかのテーブルとJOIN可能。

pathの表記方法

 MySQL型を扱うときの、JSONのpath記法。

JSONのpath記法

'$.name'	name の値
'$[0]'	配列の一番頭
'$.a[1]'	{"a":[]}となっているとき,a 配列の2番目
'$.*'	名前のワイルドカード (全名前が対象。名前のないものは無視)
'$.[*]'	配列要素のワイルドカード
'$**'	あらゆるパスの中から選び出す

mysql> SELECT JSON_EXTRACT('{"a":1, "b":"z"}', '$.*');
+-----------------------------------------+
| JSON_EXTRACT('{"a":1, "b":"z"}', '$.*') |
+-----------------------------------------+
| [1, "z"]                                |
+-----------------------------------------+

mysql> SELECT JSON_EXTRACT('[1,2,3]', '$[*]');
+---------------------------------+
| JSON_EXTRACT('[1,2,3]', '$[*]') |
+---------------------------------+
| [1, 2, 3]                       |
+---------------------------------+

- 「a」という名前の値を取り出す。いかなる階層でも
mysql> SELECT JSON_EXTRACT('{"a":1, "b":2, "c":{"a":"z"} }', '$**.a');
+---------------------------------------------------------+
| JSON_EXTRACT('{"a":1, "b":2, "c":{"a":"z"} }', '$**.a') |
+---------------------------------------------------------+
| [1, "z"]                                                |
+---------------------------------------------------------+

 何らかの値をデータベースのカラムに無理やり押し込むぐらいなら、JSON型の使用も選択肢としてもあり。