APIで取得したコードをmysqlのjson_tableで抜き出す場合のご質問

Question

mysql でこちらの内閣府の人口情勢のAPIを利用してMYSQLに落としました
https://opendata.resas-portal.go.jp/docs/api/v1/population/composition/perYear.html

curl コマンドを利用して、各都道府県のデータをcurl コマンドと seq コマンドを　
利用して、下記のようにMYSQLに落とすところまではできました。
データベース名は　resas 、テーブル名は　j_pop_growth_pref　としました
下記は1行ごとに各都道府県ごとに人口の履歴を抜き出したMYSQLの画面の切り取りです。

しかしながら、このデータの中から、データを抜き出して表を作るところで躓いています
具体的にたたいたSQLは下記になります

select j_pop_growth.*
from j_pop_growth_pref, JSON_TABLE(
  `doc`,
  "$.result.bar.data[*]"
  columns (
    sum double path '$.sum',
    year varchar(64) path '$.year'
  )
) j_pop_growth;

これだと下記のように、sumとyearしか抜き出すことができません。

実際に抜き出すJSONデータは下記になっています。

 {"message":null,"result":{"line":{"boundaryYear":2015,"data":[{"year":1965,"value":2.63},{"year":1970,"value":0.24},{"year":1975,"value":2.97},{"year":1980,"value":4.45},{"year":1985,"value":1.86},{"year":1990,"value":-0.62},{"year":1995,"value":0.86},{"year":2000,"value":-0.15},{"year":2005,"value":-0.96},{"year":2010,"value":-2.15},{"year":2015,"value":-2.25},{"year":2020,"value":-3.77},{"year":2025,"value":-4.2},{"year":2030,"value":-4.85},{"year":2035,"value":-5.44},{"year":2040,"value":-6.09}]},"bar":{"data":[{"year":1965,"sum":2.63,"class":[{"label":"老年人口","value":17.57},{"label":"生産年齢 人口","value":10},{"label":"年少人口","value":-13.04}]},{"year":1970,"sum":0.24,"class":[{"label":"老年人口","value":19.95},{"label":"生産年齢人口","value":3.33},{"label":"年少人口","value":-10.43}]},{"year":1975,"sum":2.97,"class":[{"label":"老年人口","value":22.6},{"label":"生産年齢人口","value":2.3},{"label":"年少人口","value":0.24}]},{"year":1980,"sum":4.45,"class":[{"label":"老年人口","value":23.2},{"label":"生産年齢人口","value":4.54},{"label":"年少人口","value":-1.08}]},{"year":1985,"sum":1.86,"class":[{"label":"老年人口","value":21.64},{"label":"生産年齢人口","value":2.27},{"label":"年少人口","value":-6.18}]},{"year":1990,"sum":-0.62,"class":[{"label":"老年人口","value":22.82},{"label":"生産年齢人口","value":0.36},{"label":"年少人口","value":-15.07}]},{"year":1995,"sum":0.86,"class":[{"label":"老年人口","value":25.2},{"label":"生産年齢人口","value":0.46},{"label":"年少人口","value":-13.1}]},{"year":2000,"sum":-0.15,"class":[{"label":"老年人口","value":22.09},{"label":"生産年齢人口","value":-2.78},{"label":"年少人口","value":-11.82}]},{"year":2005,"sum":-0.96,"class":[{"label":"老年人口","value":16.88},{"label":"生産年齢人口","value":-3.56},{"label":"年少人口","value":-9.24}]},{"year":2010,"sum":-2.15,"class":[{"label":"老年人口","value":12.64},{"label":"生産年齢人口","value":-5.78},{"label":"年少人口","value":-8.58}]},{"year":2015,"sum":-2.25,"class":[{"label":"老年人口","value":14.75},{"label":"生産年齢人口","value":-8.36},{"label":"年少人口","value":-7.45}]},{"year":2020,"sum":-3.77,"class":[{"label":"老年人口","value":8.86},{"label":"生産年齢人口","value":-7.52},{"label":"年少人口","value":-12.69}]},{"year":2025,"sum":-4.2,"class":[{"label":"老年人口","value":1.16},{"label":"生産年齢人口","value":-5.96},{"label":"年少人口","value":-11.61}]},{"year":2030,"sum":-4.85,"class":[{"label":"老年人口","value":-0.15},{"label":"生産年齢人口","value":-6.68},{"label":"年少人口","value":-11.19}]},{"year":2035,"sum":-5.44,"class":[{"label":"老年人口","value":-0.81},{"label":"生産年齢人口","value":-8},{"label":"年少人口","value":-8.54}]},{"year":2040,"sum":-6.09,"class":[{"label":"老年人口","value":0.47},{"label":"生産年齢人口","value":-10.58},{"label":"年少人口","value":-7.27}]}]}}} 

これをうまく利用して年代ごとの、生産年齢人口、年少人口、老年人口を抜き出して
下記のようなカラムの表を作りたいのですがsqlの書き方がわかりません、どなたか、ご指導いただけますでしょうか？

Comments

[yambejp]

例示されているjsonが正しい形式になっていません
参照可能なデータを例示ください

[dame-dame]

失礼しました。オンラインのJSONチェッカーでチェックしなおして、JSON全文を張りなおしました
ご確認いただければと思います。何卒よろしくお願いいたします

[yambejp]

mysqlのバージョンは8.0.4以降で間違いないですか？
classキーは配列を返すようなので[0]を参照すると老年人口が得られそうですが・・・

[dame-dame]

はい、8.026 です　すいません。配列を指定するのならば、０で一番最初のデータである
{"label":"老年人口","value":17.57}を取り出せるのはりかいできますが、
どうやってそれをsql 文に落とし込んで、カラムに並べるのかというところが、思いつかないのですが、、、、

Answer 1

配列を指定するのならば、０で一番最初のデータである
{"label":"老年人口","value":17.57}を取り出せるのは理解できますが、
どうやってそれをsql 文に落とし込んで、
カラムに並べるのかというところが、思いつかないのですが、、、、

JSON_TABLE関数において、class を json型 で定義すればどうでしょう。

以下、ご提示いただいたサンプルデータを SQL文内でJSON型に変換し、
ご希望のデータを取り出した例です。
( テーブルは使っていませんので クライアント上でコピペ実行できます )
( MySQL 8.0.X 必須 )

SELECT j_pop_growth.*
     , class->'$[0].value' 老年人口
     , class->'$[1].value' 生産年齢人口
     , class->'$[2].value' 年少人口
FROM
(
  SELECT
    cast(
    '{ "result": 
       { "bar": 
         { "data": 
           [
             {
               "sum"  : 2.63, 
               "year" : 1965, 
               "class": 
               [
                 { "label": "老年人口",     "value": 17.57  }, 
                 { "label": "生産年齢人口", "value": 10     }, 
                 { "label": "年少人口",     "value": -13.04 }
               ]
             },
             {
               "sum"  : 0.24, 
               "year" : 1970, 
               "class": 
               [
                 { "label": "老年人口",     "value": 19.95  }, 
                 { "label": "生産年齢人口", "value": 3.33   }, 
                 { "label": "年少人口",     "value": -10.43 }
               ]
             },
             {
               "sum"  : 2.97, 
               "year" : 1975, 
               "class": 
               [
                 { "label": "老年人口",     "value": 22.6  }, 
                 { "label": "生産年齢人口", "value": 2.3   }, 
                 { "label": "年少人口",     "value": 0.24  }
               ]
             },
             {
               "sum"  : 4.45, 
               "year" : 1980, 
               "class": 
               [
                 { "label": "老年人口",     "value": 23.2  }, 
                 { "label": "生産年齢人口", "value": 4.54  }, 
                 { "label": "年少人口",     "value": -1.08 }
               ]
             }
           ]
         }
       }
     }' as json 
    ) doc

) j_pop_growth_pref
,
JSON_TABLE
(
  `doc`,
  "$.result.bar.data[*]"
  columns 
  (
    sum   double      path '$.sum',
    year  varchar(64) path '$.year',
    class json        path '$.class' -- 追加
  )
) j_pop_growth;

■結果

sum	year	class	老年人口	生産年齢人口	年少人口
2.63	1965	[{...},{...},{...}]	17.57	10	-13.04
0.24	1970	[{...},{...},{...}]	19.95	3.33	-10.43
2.97	1975	[{...},{...},{...}]	22.6	2.3	0.24
4.45	1980	[{...},{...},{...}]	23.2	4.54	-1.08