MySQL8 JSON TABLE で配列を扱う

MySQL8からJSONの配列をレコードとして扱えるので、使ってみました。

[ Table例 ]
空港の滑走路のマスタで、複数の滑走路をJSONにしてます。

CREATE TABLE `m_runway` (
	`CD_IATA` CHAR(3) NOT NULL DEFAULT '' COMMENT 'IATA空港コード' COLLATE 'utf8_general_ci',
	`CD_ICAO` CHAR(4) NOT NULL DEFAULT '' COMMENT 'ICAO空港コード' COLLATE 'utf8_general_ci',
	`LAT` DOUBLE(22,10) NOT NULL DEFAULT '500.0000000000' COMMENT '緯度',
	`LON` DOUBLE(22,10) NOT NULL DEFAULT '500.0000000000' COMMENT '経度',
	`RUNWAYS` JSON NULL DEFAULT NULL COMMENT 'JSON明細',
	`TM_ADD` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '追加時刻',
	INDEX `m_runway_CDIATA` (`CD_IATA`) USING BTREE,
	INDEX `m_runway_CDICAO` (`CD_ICAO`) USING BTREE,
	INDEX `m_runway_LAT` (`LAT`) USING BTREE,
	INDEX `m_runway_LON` (`LON`) USING BTREE
)
COMMENT='滑走路マスタ'
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

[ JSON列のJSON ]


[
    {
        "name": "06",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 2.316,
            "nm": 1.25,
            "feet": 7598,
            "mile": 1.439,
            "meter": 2315.87
        },
        "surface": "Asphalt",
        "trueHdg": 51.2,
        "isClosed": false,
        "location": {
            "lat": 40.6469841,
            "lon": -75.45063
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    },
    {
        "name": "13",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 1.766,
            "nm": 0.954,
            "feet": 5794,
            "mile": 1.097,
            "meter": 1766.01
        },
        "surface": "Asphalt",
        "trueHdg": 123.1,
        "isClosed": false,
        "location": {
            "lat": 40.6551933,
            "lon": -75.44975
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    },
    {
        "name": "24",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 2.316,
            "nm": 1.25,
            "feet": 7598,
            "mile": 1.439,
            "meter": 2315.87
        },
        "surface": "Asphalt",
        "trueHdg": 231.2,
        "isClosed": false,
        "location": {
            "lat": 40.6600342,
            "lon": -75.42926
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0.152,
            "nm": 0.082,
            "feet": 499,
            "mile": 0.095,
            "meter": 152.1
        }
    },
    {
        "name": "31",
        "width": {
            "km": 0.046,
            "nm": 0.025,
            "feet": 150,
            "mile": 0.028,
            "meter": 45.72
        },
        "length": {
            "km": 1.766,
            "nm": 0.954,
            "feet": 5794,
            "mile": 1.097,
            "meter": 1766.01
        },
        "surface": "Asphalt",
        "trueHdg": 303.1,
        "isClosed": false,
        "location": {
            "lat": 40.6465263,
            "lon": -75.4322357
        },
        "hasLighting": true,
        "displacedThreshold": {
            "km": 0,
            "nm": 0,
            "feet": 0,
            "mile": 0,
            "meter": 0
        }
    }
]

[ Select SQL例 ]

NESTED PATH を指定


    SELECT
      r.CD_IATA,
      r.CD_ICAO,
      -500 AS LAT,
      -500 AS LON,
      detail.NM_RUNWAY,
      detail.HDG,
      ROUND(detail.FEET, 0) AS FEET, 
      ROUND(detail.METER, 0) AS METER
    FROM 
      m_runway r, 

      JSON_TABLE(RUNWAYS, '$[*]' 
      COLUMNS (
        `NM_RUNWAY` VARCHAR(100) PATH '$.name',
        HDG INT PATH '$.trueHdg',
        
        NESTED PATH '$.length' 
        COLUMNS (
          FEET FLOAT PATH '$.feet',
          METER FLOAT PATH '$.meter'
        )               
        )
      ) AS detail
    WHERE
      CD_ICAO = 'RJTT'

[ 結果 ]

+---------+---------+------+------+-----------+------+-------+-------+
| CD_IATA | CD_ICAO | LAT  | LON  | NM_RUNWAY | HDG  | FEET  | METER |
+---------+---------+------+------+-----------+------+-------+-------+
| HND     | RJTT    | -500 | -500 | 04        |   35 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 05        |   42 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 16L       |  150 | 11053 |  3369 |
| HND     | RJTT    | -500 | -500 | 16R       |  150 |  9862 |  3006 |
| HND     | RJTT    | -500 | -500 | 22        |  215 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 23        |  222 |  8215 |  2504 |
| HND     | RJTT    | -500 | -500 | 34L       |  330 |  9862 |  3006 |
| HND     | RJTT    | -500 | -500 | 34R       |  330 | 11053 |  3369 |
+---------+---------+------+------+-----------+------+-------+-------+