Loading

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

posted @ 2021-12-15 16:45  李帆1998  阅读(482)  评论(0编辑  收藏  举报