MySQL如何查询JSON字段存在包含指定文本键名的行

你当前用JSON_EXTRACT的思路不适用于该场景，JSON_EXTRACT的作用是提取指定键对应的值，而你需要先获取JSON字段的所有键名，再匹配是否存在包含abc的键。不同数据库的具体实现写法如下：

仅匹配JSON顶层键 #

SELECT * 
FROM table1 t 
WHERE JSON_SEARCH(JSON_KEYS(t.json_data), 'one', '%abc%') IS NOT NULL;

• JSON_KEYS(t.json_data)：返回JSON字段顶层所有键组成的数组
• JSON_SEARCH：查找数组中是否存在符合%abc%模糊匹配的元素，存在则返回对应路径，否则返回NULL

匹配JSON所有层级的键 #

WITH RECURSIVE json_keys AS (
    -- 提取顶层键
    SELECT 
        t.id,
        JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.json_data), CONCAT('$[', n.idx, ']'))) AS `key`,
        JSON_EXTRACT(t.json_data, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(t.json_data), CONCAT('$[', n.idx, ']'))))) AS `value`
    FROM table1 t
    JOIN (SELECT 0 AS idx UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) n
    WHERE idx < JSON_LENGTH(JSON_KEYS(t.json_data))
    UNION ALL
    -- 递归提取嵌套JSON的键
    SELECT 
        jk.id,
        JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(jk.value), CONCAT('$[', n.idx, ']'))) AS `key`,
        JSON_EXTRACT(jk.value, CONCAT('$.', JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(jk.value), CONCAT('$[', n.idx, ']'))))) AS `value`
    FROM json_keys jk
    JOIN (SELECT 0 AS idx UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) n
    WHERE JSON_TYPE(jk.value) = 'OBJECT' AND idx < JSON_LENGTH(JSON_KEYS(jk.value))
)
SELECT DISTINCT t.* 
FROM table1 t
WHERE EXISTS (SELECT 1 FROM json_keys jk WHERE jk.id = t.id AND jk.key LIKE '%abc%');

仅匹配JSON顶层键（jsonb类型） #

SELECT *
FROM table1 t
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_object_keys(t.json_data) AS keys(k)
    WHERE k LIKE '%abc%'
);

如果字段是json类型，把jsonb_object_keys替换为json_object_keys即可。

匹配JSON所有层级的键 #

SELECT DISTINCT t.*
FROM table1 t
WHERE EXISTS (
    SELECT 1
    FROM jsonb_each_recursive(t.json_data) AS j(key, value)
    WHERE j.key LIKE '%abc%'
);

仅匹配JSON顶层键 #

SELECT DISTINCT t.*
FROM table1 t
CROSS APPLY OPENJSON(t.json_data) AS j
WHERE j.[key] LIKE '%abc%';

匹配JSON所有层级的键 #

SELECT DISTINCT t.*
FROM table1 t
CROSS APPLY OPENJSON(t.json_data, '$') WITH (
    [key] NVARCHAR(100) '$.key',
    [value] NVARCHAR(MAX) '$.value' AS JSON
) AS j
WHERE j.[key] LIKE '%abc%'
OR EXISTS (
    SELECT 1 FROM OPENJSON(j.value) AS nj WHERE nj.[key] LIKE '%abc%'
);

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