如何从LONGTEXT类型JSON字符串中提取rank为1的userkey

场景1：数据库支持原生JSON函数（推荐） #

如果你的数据库是MySQL 5.7+/MariaDB 10.2+这类支持JSON操作的版本，直接用原生JSON函数处理是最稳妥的，不用折腾字符串拆分：

MySQL/MariaDB 示例 #

SELECT DISTINCT j.userkey
FROM your_table t
JOIN JSON_TABLE(
  JSON_KEYS(t.attribute_value),
  '$[*]' COLUMNS(userkey VARCHAR(255) PATH '$')
) j
WHERE JSON_EXTRACT(t.attribute_value, CONCAT('$.', j.userkey)) = 1;

或者更简洁的MySQL 8.0+写法：

SELECT DISTINCT j.userkey
FROM your_table t,
     JSON_TABLE(
       JSON_OBJECTAGG(k, v),
       '$.*' COLUMNS(
         userkey VARCHAR(255) PATH '$[0]',
         rank INT PATH '$[1]'
       )
     ) j
WHERE j.rank = 1;

原理是把JSON对象的键值对转成关系型表的行，再筛选rank=1的记录，直接拿到所有符合条件的userkey。

场景2：数据库不支持JSON函数（老版本兼容方案） #

如果数据库版本太低没有JSON函数，只能用字符串拆分的方式，这里用递归CTE或数字辅助表实现：

先创建数字辅助表（覆盖JSON键值对数量） #

CREATE TABLE numbers (n INT PRIMARY KEY);
INSERT INTO numbers VALUES (1),(2),(3),...,(100); -- 根据实际数据量调整

字符串拆分查询 #

SELECT DISTINCT
  TRIM(
    BOTH '"' FROM
    SUBSTRING_INDEX(
      SUBSTRING_INDEX(
        REPLACE(REPLACE(t.attribute_value, '{', ''), '}', ''),
        ',"', n.n
      ),
      '":', -1
    )
  ) AS userkey
FROM your_table t
JOIN numbers n ON n.n <= LENGTH(t.attribute_value) - LENGTH(REPLACE(t.attribute_value, '":', ''))
WHERE SUBSTRING_INDEX(
        SUBSTRING_INDEX(
          REPLACE(REPLACE(t.attribute_value, '{', ''), '}', ''),
          ':', n.n
        ),
        ',', -1
      ) LIKE '1%';

原理是通过数字表逐个定位每个键值对，拆分出userkey和对应的rank，再筛选rank=1的记录。

注意事项 #

• 原生JSON函数方法性能远优于字符串拆分，能避免特殊字符导致的边界问题，优先使用。
• 字符串拆分方法要求JSON格式严格统一（无多余空格、键值对格式规范），否则可能出错。

内容的提问来源于stack exchange，提问作者STSA