sql中几个常用的json解析函数

data：代表json格式的字段名
name：代表json中的元素

普通解析

mysql/ADB MYSQL

json_extract(data,’$.name’)

clickhouse

data[‘name’]

特殊字符

mysql

json_extract(data,’$.“name”’)

ADB MYSQL

json_array_get(data,索引)
索引为int型，从0开始，也就是直接解析array中的第几个元素

解析json元素中的列表

mysql

json_extract(data,’$.name[*].author_id’)

AnalyticDB MySQL

json_extract(data,’$.name[0].name1’)

例

insert into report_data.mall_goods_data
select 
	 g.data_date
	,g.brand
	,replace(json_extract(g.goods_stat,'$.product_base_result.product_name'),'"','') as goods_name
	,replace(replace(json_extract(g.goods_stat,'$.author_result.author_info[*].author_id'),'[',''),']','') as author_id
	,replace(replace(replace(json_extract(g.goods_stat,'$.pay_gmv'),'"',''),',',''),'¥','')*1 as 成交金额
	,case when json_extract(g.goods_stat,'$.comment_num') like '%万"' then replace(replace(replace(json_extract(g.goods_stat,'$.comment_num'),'"',''),',',''),'万','')*10000 else replace(replace(json_extract(g.goods_stat,'$.comment_num'),'"',''),',','')*1 end as 评价数
	,replace(replace(json_extract(g.goods_stat,'$.dh_live_d_o'),'"',''),'%','')*0.01 as 直播间支付转化率
from 
(
	select * from dy_shop.goods_stats where data_date=date_add(curdate(),interval -1 day)
)g