获取 mysql 中 json 类型字段指定 key

有的时候博客内容会有变动，首发博客是最新的，其他博客地址可能会未同步, 认准 https://blog.zysicyj.top

可点击链接 https://blog-1253652709.cos.ap-guangzhou.myqcloud.com//picgo/202401180921373.png 解答疑问

Mysql5.7

1
2
3
4
5
6
SELECT 
  JSON_UNQUOTE(JSON_EXTRACT(post, CONCAT('$[', idx, '].post_id'))) AS post_id
FROM 
  biz_person_resume,
  (SELECT 0 AS idx UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS idx_table
WHERE JSON_UNQUOTE(JSON_EXTRACT(post, CONCAT('$[', idx, ']'))) IS NOT NULL;

Mysql8

1
2
3
4
5
6
SELECT 
  JSON_UNQUOTE(JSON_EXTRACT(post_data, '$.post_id')) AS post_id
FROM 
  biz_person_resume,
  JSON_TABLE(post, '$[*]' COLUMNS (post_data JSON PATH '$')) AS jt
WHERE id = '0033986f-32cd-42ea-a3d8-6dd853d379be'