jsonを分割してから横持ち→縦持ちに変換するクエリ（備忘録）

jsonを分割して横持ちし、そのあとに縦持ちに変換するクエリ（replaceはデータによる）

select
	id
    ,member_id
    ,
  (
    CASE t2.`key`
      WHEN 'tmp1'  THEN tmp1
      WHEN 'tmp2' THEN tmp2
      WHEN 'tmp3'      THEN tmp3
    END
  ) AS value
from
(SELECT
	id
    ,member_id
    ,SUBSTRING_INDEX(replace(replace(replace(JSON_EXTRACT(data,'$.list'),"[",""), "]",""),'"',""),',',1) as tmp1
	,SUBSTRING_INDEX(SUBSTRING_INDEX(replace(replace(replace(JSON_EXTRACT(data,'$.list'),"[",""), "]",""),'"',""),',',2),',',-1) as tmp2
	,SUBSTRING_INDEX(SUBSTRING_INDEX(replace(replace(replace(JSON_EXTRACT(data,'$.list'),"[",""), "]",""),'"',""),',',3),',',-1) as tmp3
from
	json_table
) as t1
CROSS JOIN
(
  SELECT 'tmp1' AS `key`
  UNION ALL SELECT 'tmp2' AS `key`
  UNION ALL SELECT 'tmp3' AS `key`
)  as t2