Mysql解析json字符串/数组
原始json数据
解析之后
用replace 替换多余的"符号并用’/'代替空值
sql展示
SELECT
REPLACE ( JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[0].number' ), '"', '' ) AS number_1,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[0].endElec' ),'"','' ) ='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[0].endElec' ),'"','' )
end AS endElec_1,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[0].fixedElec' ),'"','' )='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[0].fixedElec' ),'"','' )
end AS fixedElec_1,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[0].startElec' ),'"','' ) ='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[0].fixedElec' ),'"','' )
end AS startElec_1,
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[1].number' ), '"', '' ) AS number_2,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[1].endElec' ),'"','' ) ='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[1].endElec' ),'"','' )
end AS endElec_2,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[1].fixedElec' ),'"','' )='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[1].fixedElec' ),'"','' )
end AS fixedElec_2,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[1].startElec' ),'"','' ) ='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[1].fixedElec' ),'"','' )
end AS startElec_2,
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[2].number' ), '"', '' ) AS number_3,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[2].endElec' ),'"','' ) ='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[2].endElec' ),'"','' )
end AS endElec_3,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[2].fixedElec' ),'"','' )='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[2].fixedElec' ),'"','' )
end AS fixedElec_3,
case when
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[2].startElec' ),'"','' ) ='' then '/'
else
REPLACE (JSON_EXTRACT( JSON_EXTRACT( spec_tran_dist_json, '$.tieredPrice' ), '$[2].fixedElec' ),'"','' )
end AS startElec_3
FROM
td_contract_other_info
WHERE
remove_tag = 1