MySQL jsonとかで保存した値の中から任意のキーの値の数値の合計値が欲しい

PHPでレコードを取得してループで合計値を出せばいいんだけど、重そうだからSQLだけで対応できないか調査。

作成日:2017-12-15, 更新日:2022-03-02

基本

jsonにしてもシリアライズにしても基本、決まった書式だから任意の値を取得することは可能なはず・・・。

取得したい値
１．「カラム（JSON型じゃなくTEXT型）：detail」に取得対象が格納されたjsonが保存
２．jsonで保存した「キー：point」の値（数字）が対象
３．全レコードの「２」の合計値

取得：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()）