やったこと備忘_20190316

#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"である要素の数を集計したい場合の方法を調査