json ぽいデータを引き抜くクソクエリ
背景
json に対応していない mysql 5.7 未満のシステムに、json で値が格納されている。
対応
クソにはクソで。 LOCATE と、SUBSTRING を駆使し、抽出を試みる。
ここに書かれているは方法は、「一般的に」JSONをパースして抽出する方法でなく、利用範囲が限られるアドホックな方法であること（だからクソ）に注意されたい。
戦略
SUBSTR は、
SUBSTRING(文字列, 開始位置, 文字数)
で開始位置から文字数分、文字列を抽出できる。よって、 抽出したい文字列の開始位置 と 抽出したい文字列の文字数 が分かればいいことになる。
多くの場合は、抽出対象となる文字列は ..., <key>: <value>, ... みたいな形式になっており、抽出したい文字列自体（ここでは、<value> とする）は不定で、<key> をヒントに、「<key> の後のコロンの後に続く文字列」であることが多い。ここでは、これを例として話をすすめる。
抽出したい文字列の開始位置
とりあえず、
LOCATE("<key>:", 非抽出カラム名)
で取れる。よってこれに <key>: の長さを追加すれば、「LENGTH("<key>:") と計算してもいいし、自分で数えても良い。
空白の有無はそこはデータ依存によるが、後で TRIM を使うのであれば、空白が含まれていても問題ないだろう。
よって抽出したい文字列の開始位置は、
LOCATE("<key>:", 被抽出カラム名) + LENGTH("<key>:")
となる。
抽出したい文字列の文字数
抽出したい文字列の文字数は次のように求められる。
抽出したい文字列の文字数 = （抽出したい文字列の先頭からの文字列における）抽出したい文字列の次の文字列の位置 -1
である。ここで、 抽出したい文字列の先頭からの文字列 は次のようして求まる。
SUBSTRING(被抽出カラム名, LOCATE("<key>:", 被抽出カラム名) + LENGTH("<key>:"))
よって、LOCATEを使い、 （抽出したい文字列の先頭からの文字列における）抽出したい文字列の次の文字列の位置 は、
LOCATE(",", SUBSTRING(被抽出カラム名, LOCATE("<key>:", 被抽出カラム名) + LENGTH("<key>:")))
となる。
まとめると
最後にすべてまとめると、
SUBSTRING(被抽出カラム名, 抽出したい文字列の開始位置, 抽出したい文字列の文字数)
に、抽出したい文字列の開始位置 を代入すると、
SUBSTRING(被抽出カラム名, LOCATE("<key>:", 被抽出カラム名) + LENGTH("<key>:"), 抽出したい文字列の文字数)
となり、ここに、抽出したい文字列の文字数 を代入すると、
SUBSTRING(被抽出カラム名, LOCATE("<key>:", 被抽出カラム名) + LENGTH("<key>:"), LOCATE(",", SUBSTRING(被抽出カラム名, LOCATE("<key>:", 被抽出カラム名) + LENGTH("<key>:")))-1)
となる。クソクエリの完成である。
まとめ
テンプレートを作った。
SUBSTRING(column,LOCATE(prefix, column)+LENGTH(prefix),LOCATE(suffix,SUBSTRING(column,LOCATE(prefix, column)+LENGTH(prefix)))-1)
ここで、column: 被抽出カラム名、prefix: 抽出したい文字列の前に来る文字列、suffix: 抽出したい文字列の次に来る文字列 である。
ただし、これは抽出したい文字列の次に来る文字列が固定されている場合に限定されるクエリである。最後にカンマがあったりなかったりするとうまくいかない。クソクエリだからである。
