作者paranoia5201 (社会黑暗、行路难)
看板Database
标题[SQL ] 从son档取出所需的值，取不出来
时间Wed Sep 11 00:34:49 2019
资料库名称：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://webptt.com/cn.aspx?n=bbs/Database/M.1568133292.A.44A.html
1F：→ cocobox: 你先确定一下你的资料库版本 09/12 15:29