MySQL解析json字符串

• 对于mysql5.7以上版本
  使用mysql的内置函数JSON_EXTRACT(column, ‘$.key’)，这个函数有两个参数，第一个参数column代表json列的列名；第二个参数key代表json字符串中的某一个key。

示例：
insert into icp.common.credit.icp_ranking_huabei (credit_no,ranking,totalScore,is_adopt,create_date)
select distinct credit_no,
REPLACE(JSON_EXTRACT(REPLACE(REPLACE(REPLACE(JSON_EXTRACT(ext_info,’ . m s g ′ ) , ′ " { ′ , ′ { ′ ) , ′ " ′ , ′ " ′ ) , ′ } " ′ , ′ } ′ ) , ′ .msg'),'"\{\\','\{'),'\\"','"'),'\}"','\}'),' .msg′),′"{′,′{′),′"′,′"′),′}"′,′}′),′.ranking’),’"’,’’) as ranking,
REPLACE(JSON_EXTRACT(REPLACE(REPLACE(REPLACE(JSON_EXTRACT(ext_info,’ . m s g ′ ) , ′ " { ′ , ′ { ′ ) , ′ " ′ , ′ " ′ ) , ′ } " ′ , ′ } ′ ) , ′ .msg'),'"\{\\','\{'),'\\"','"'),'\}"','\}'),' .msg′),′"{′,′{′),′"′,′"′),′}"′,′}′),′.totalScore’),’"’,’’) as totalScore,
is_adopt,
create_date
from icp.common.credit.icp_credit_node_result where proj_no=‘HUABEI’ and node_no=‘N1040’