[SQL ] 從son檔取出所需的值，取不出來
資料庫名稱：MySQL
資料庫版本：
內容/問題描述：
資料長相如下，是個json型態欄位內容。
## 語法 ##
select ticket_info from afbet_main.t_order_record limit 2
## 資料長相 ##
[1]
{"ticketId":"1711150622507107458287","selections":[{
"eventId":"sr:match:13020726","id":"uof:1/sr:sport:5/186/4","odds":"10100",
"banker":false}],"bets":[{"id":"1711150622507107458287-bet-0","selectionRefs":
[{"selectionIndex":0,"banker":false}],"selectedSystems":[1],"stake":{
"value":550000,"type":"total"},"sumOfWins":0}]}
[2]
{"ticketId":"1711150747267107466020","selections":[{
"eventId":"sr:match:11439959","id":"uof:3/sr:sport:1/1/1","odds":"14500",
"banker":false},{"eventId":"sr:match:11439965","id":"uof:3/sr:sport:1/1/2",
"odds":"30200","banker":false},{"eventId":"sr:match:11439955",
"id":"uof:3/sr:sport:1/1/2","odds":"41500","banker":false}],"bets":[{
"id":"1711150747267107466020-bet-0","selectionRefs":[{
"selectionIndex":0,"banker":false},{"selectionIndex":1,"banker":false},
{"selectionIndex":2,"banker":false}],"selectedSystems":[3],"stake":{
"value":1000000,"type":"total"},"sumOfWins":0}]}
任務：取出ticketID，eventID和odds。
困難點：
這兩個case的資料長度不同，要如能夠正確地取出所有的值？
直接地說，eventID和odds在第一筆資料中是各一筆資料，但在第二筆資料中卻是各三筆。
基於全要的要求，該如何完成？
作法一：使用substring_index
## 語法 ##
select
substring_index(substring_index(ticket_info, 'ticketId":"', -1), '"', 1)
as ticketId,
substring_index(substring_index(ticket_info, 'eventId":"', -1), '"', 1)
as eventID,
substring_index(substring_index(ticket_info, 'odds":', -1), ',', 1)
as odds
from afbet_main.t_order_record
## Error ##
若是第二筆資訊，只會捉到第一個，後面的會被忽略。
我當然可以自行算好有幾個後下指令，但是每筆資料的長度不同，如何可以一次搞定？
方法二：使用json_extract
## 語法 ##
select json_extract(ticket_info, '$.ticketId'),
json_extract(ticket_info, '$.selections'),
json_extract(ticket_info, '$.selections.odds')
from afbet_main.t_order_record
##Error##
照網路上的說明，json_extract(ticket_info, '$.selections.odds’) 這行應該可以，
但是卻會卡錯誤在這裡，連帶地更裡面的資訊就捉不出來了。
參考資訊：
stackoverflow.com/questions/37756438/query-a-multi-level-json-object-stored-in-mysql
我想把資料移到外部軟體（如R及Python等），然而許多教學文都是假設
json檔案的層級還有長度都相同，所以展開會是可預期的table長度，
但是我在這裡就是無法預料每筆資料的長度，很難幫預設的欄位都設計好......。
煩請各位大神幫幫忙，真的想破頭了......。
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 180.177.36.225 (臺灣)
※ 文章網址: https://www.ptt.cc/bbs/Database/M.1568133292.A.44A.html
→
09/12 15:29,
5年前
, 1F
09/12 15:29, 1F
Database 近期熱門文章
PTT數位生活區 即時熱門文章
