Mysqlで JsonデータをTable化する方法について説明します。
JsonTableを使えば簡単にできます。
例文 1
SET @strjson = '[{"LogDate":"2020-12-14","Cnt":10, "ReMark" : "test1"},{"LogDate":"2020-12-15","Cnt":30, "ReMark" : "test2"}]';
SELECT * FROM JSON_TABLE(@strjson ,
"$[*]"
COLUMNS(
LogDate datetime PATH "$.LogDate",
Cnt int PATH "$.Cnt",
Remark varchar(50) PATH "$.ReMark"
)
)as t1;
例文 2
SET @j =
'[
{"LogDate": "2020-01-01 12:34","Sn":1, "Cnt": 700 }
,{"LogDate": "2020-01-01 12:34","Sn":2, "Cnt": 600 }
,{"LogDate": "2020-01-01 12:34","Sn":3, "Cnt": 800 }
]';
SELECT * FROM JSON_TABLE(@j ,
"$[*]"
COLUMNS(
LogDate datetime PATH "$.LogDate",
ServerNo INT PATH "$.Sn",
Cnt INT PATH "$.Cnt"
)
)as t1;
例文 3
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 * FROM JSON_TABLE(JSON_EXTRACT(@j, "$.Data.Detail") ,
"$[*]"
COLUMNS(
Sn int PATH "$.Sn",
Cnt INT PATH "$.Cnt"
)
)as t1;
例文 4
SET @apijson = '[{"GroupID":3},{"GroupID":2}]';
select @apijson;
SELECT * FROM JSON_TABLE(@apijson ,
"$[*]"
COLUMNS(
GroupID int PATH "$.GroupID"
)
)as t1;
例文 5
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;