[MySQL] Json to Table する方法
SET @j =
'{
"Data": {
"LogDate": "2020-01-01 12:34",
"TotalCnt": 1200,
"Detail": [
{ "Sn":1, "Cnt": 700 },
{ "Sn":2, "Cnt": 300 },
{ "Sn":3, "Cnt": 200 }
]
}
}';
SELECT
jt1.LogDate,
jt1.TotalCnt,
jt2.Sn,
jt2.Cnt
FROM
JSON_TABLE(
@j,
'$.Data' COLUMNS (
LogDate VARCHAR(255) PATH '$.LogDate',
TotalCnt INT PATH '$.TotalCnt'
)
) AS jt1
CROSS JOIN
JSON_TABLE(
@j,
'$.Data.Detail[*]' COLUMNS (
Sn INT PATH '$.Sn',
Cnt INT PATH '$.Cnt'
)
) AS jt2;