- 对于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’