从longtext类型存储的JSON中提取值为1的键

为什么JSON_SEARCH无返回结果？ #

JSON_SEARCH默认匹配字符串类型的JSON值，而你的示例JSON里的1是数字类型，直接搜索数字1会匹配不到。如果硬要用JSON_SEARCH，仅当JSON值为字符串"1"时生效，显然不适用于你的场景，更可靠的方案是用JSON_TABLE解析键值对后过滤。

纯SQL实现方案（以MySQL为例） #

1. 提取单个键（每行返回一个匹配的键）

SELECT j.key_name
FROM your_table
CROSS JOIN JSON_TABLE(
    CAST(attribute_value AS JSON),
    '$.*' COLUMNS(
        key_name VARCHAR(255) PATH '$',
        value INT PATH '$'
    )
) AS j
WHERE j.value = 1;

2. 合并所有匹配的键为单个字符串

如果需要把同一行中所有值为1的键合并成逗号分隔的字符串，可使用GROUP_CONCAT：

SELECT 
    your_table.id, -- 替换为表的主键/唯一标识字段，用于分组
    GROUP_CONCAT(j.key_name SEPARATOR ', ') AS target_keys
FROM your_table
CROSS JOIN JSON_TABLE(
    CAST(attribute_value AS JSON),
    '$.*' COLUMNS(
        key_name VARCHAR(255) PATH '$',
        value INT PATH '$'
    )
) AS j
WHERE j.value = 1
GROUP BY your_table.id;

代码说明 #

• CAST(attribute_value AS JSON)：将longtext类型的列转换为JSON类型，支持后续解析操作
• JSON_TABLE：把JSON对象的键值对拆分为行数据，每行包含对应的键（key_name）和值（value）
• 通过WHERE子句过滤出值等于1的行，即可得到目标键

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