#jsonから特定の要素を抽出
使用するテーブルは以下
mysql> show columns from countryinfo;
+-------+-------------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+------------------+
| doc | json | YES | | NULL | |
| _id | varchar(32) | NO | PRI | NULL | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+
json型が格納されているカラムの要素は以下のとおり
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| doc | _id |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| {"GNP": 828, "_id": "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}} | ABW |
| {"GNP": 5976, "_id": "AFG", "Name": "Afghanistan", "IndepYear": 1919, "geography": {"Region": "Southern and Central Asia", "Continent": "Asia", "SurfaceArea": 652090}, "government": {"HeadOfState": "Mohammad Omar", "GovernmentForm": "Islamic Emirate"}, "demographics": {"Population": 22720000, "LifeExpectancy": 45.900001525878906}} | AFG |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
ここから特定の要素を取りだす。
例えば、govermentを取り出す場合は
mysql> select json_extract(doc,'$.government')
-> from countryinfo limit 2;
+----------------------------------------------------------------------------------------------+
| json_extract(doc,'$.government') |
+----------------------------------------------------------------------------------------------+
| {"HeadOfState": "Beatrix", "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"} |
| {"HeadOfState": "Mohammad Omar", "GovernmentForm": "Islamic Emirate"} |
+----------------------------------------------------------------------------------------------+
となる
さらにgovernmentのなかでも、GovernmentFormを取り出す場合は、
mysql> select json_extract(doc,'$.government.GovernmentForm')
-> from countryinfo limit 2;
+-------------------------------------------------+
| json_extract(doc,'$.government.GovernmentForm') |
+-------------------------------------------------+
| "Nonmetropolitan Territory of The Netherlands" |
| "Islamic Emirate" |
+-------------------------------------------------+
となる
今回はここまで。
次回はGovernmentFormが"Republic"である要素の数を集計したい場合の方法を調査