MySQL SQL 处理 JSON 数据
源数据
[{"attrId":2762280,"attrValueId":3475578},{"attrId":2762279,"attrValueId":3475579}]
目的:提取出 json 中的 attrValueId
case 1
select
JSON_EXTRACT(t.`saleAttrValues`, '$[*].attrValueId') as tt
from `sp_unifiedsku` t
where t.`unifiedProductId` = 252329
and t.status = 0
group by tt
case 2
group by 用来去重
这里的$[0]指的是取 case 1 的第一个元素
select
JSON_EXTRACT(JSON_EXTRACT(t.`saleAttrValues`, '$[*].attrValueId'), '$[0]') as tt
from `sp_unifiedsku` t
where t.`unifiedProductId` = 252329
and t.status = 0
group by tt
case 3
将id拼接起来
select GROUP_CONCAT(tt) from (select
JSON_EXTRACT(JSON_EXTRACT(t.`saleAttrValues`, '$[*].attrValueId'), '$[0]') as tt
from `sp_unifiedsku` t
where t.`unifiedProductId` = 252329
and t.status = 0
group by tt) as ttt