[MySQL] JsonデータをTable化する JsonTable 使い方

inno
2021-11-24 14:10 37 0

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;

コメント