MySQL jsonとかで保存した値の中から任意のキーの値の数値の合計値が欲しい
PHPでレコードを取得してループで合計値を出せばいいんだけど、重そうだからSQLだけで対応できないか調査。
作成日:2017-12-15, 更新日:2022-03-02
基本
jsonにしてもシリアライズにしても基本、決まった書式だから任意の値を取得することは可能なはず・・・。
取得したい値
1.「カラム(JSON型じゃなくTEXT型):detail」に取得対象が格納されたjsonが保存
2.jsonで保存した「キー:point」の値(数字)が対象
3.全レコードの「2」の合計値
取得:jsonで保存した「キー:point」の値(数字)
▼各レコードに保存されているJSONの例。
{"〇〇〇":"〇〇","points":3,"〇〇〇":"〇〇"}
コイツから「points」の値が欲しい。
条件としては
・「キー:points」の値は「数字」じゃなく「数値」
ココが変わると面倒。単純にやり方が分からないだけかもしれないけど。
問題は「キー:points」の次にくる「キー」が固定じゃない。
そのため、「,"points":」以降を取得し、「,」で区切った「一つ目」って感じで「points」の値を取得する。
※「,"points":」は「10文字」。
SUBSTRING_INDEX( SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), # 「カラム:detail」から「,"points":」の次以降を取得 ',', 1 # 「,」で区切った一つ目取得 )
「キー:points」が最後の場合もある→上記の結果が「123}」みたいな感じになる。
SUBSTRING_INDEX( SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), # 「カラム:detail」から「,"points":」以降取得 '}', 1 # 「}」で区切った一つ目取得 )
当然、「キー:points」が存在しない場合もあったりする。
・諸々考慮してcase-whenを使う。
・「キー:points」が最後の場合の判断は正規表現で数字かチェック(「123}」とかだったら除外)
・「キー:points」が存在しないなら「0」にする
case when 0<instr(`detail`, ',"points":') # 「,"points":」を含む場合のみ then case # 「,"points":」の次以降で、「,」で区切った一つ目が、数字のみ when SUBSTRING_INDEX(SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), ',', 1) REGEXP '^[0-9]+$' then SUBSTRING_INDEX(SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), ',', 1) # 「,"points":」の次以降で、「}」で区切った一つ目が、数字のみ when SUBSTRING_INDEX(SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), '}', 1) REGEXP '^[0-9]+$' then SUBSTRING_INDEX(SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), '}', 1) # 数字以外が含まれる # ココの値が使われる場合は、レコードの追加自体が間違っているので・・・そこまではチェックしない else 0 end # 「,"points":」を含まない else 0 end
合計値
『jsonで保存した「キー:point」の値(数字)』を「sum()」すればOK。
クエリ
select sum( case when 0<instr(`detail`, ',"points":') # 「,"points":」を含む場合のみ then case # 「,"points":」の次以降で、「,」で区切った一つ目が、数字のみ when SUBSTRING_INDEX(SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), ',', 1) REGEXP '^[0-9]+$' then SUBSTRING_INDEX(SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), ',', 1) # 「,"points":」の次以降で、「}」で区切った一つ目が、数字のみ when SUBSTRING_INDEX(SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), '}', 1) REGEXP '^[0-9]+$' then SUBSTRING_INDEX(SUBSTRING(`detail`, instr(`detail`, ',"points":')+10), '}', 1) # 数字以外が含まれる # ココの値が使われる場合は、レコードの追加自体が間違っているので・・・そこまではチェックしない else 0 end # 「,"points":」を含まない else 0 end ) as total_points from 〇〇〇
「キー:point」が含まれないレコード取得
select * from 〇〇〇 a left join ( select id, ( case when instr(`detail`, ',"points":')=0 then `detail` else "" end ) as _unknown from 〇〇〇 ) b on a.id=b.id where b._unknown!=''
関連項目
・MySQLで条件分岐(if文的なことをする:case-when)
・MySQL 文字列の一部を取得
・MySQLで行数や合計など色々調べたい(avg,count,max,min,sum)
・MySQL JSONで格納した値を検索したい(JSON_CONTAINS()、JSON_CONTAINS_PATH()、JSON_EXTRACT())