如何使用MySQL/MariaDB查询JSON列所有行共有的键？

你原来的查询只是将键集合相同的行分组，无法得到所有行共有的键。可以用JSON_TABLE（MySQL 8.0+/MariaDB 10.5+支持）配合统计计数的方式实现，核心思路是把所有行的JSON键拆分为单独行，再筛选出出现次数等于表总行数的键——这类键就是所有行共有的。

具体查询语句 #

WITH all_keys AS (
    SELECT j.key_name
    FROM cars,
         JSON_TABLE(
             JSON_KEYS(content),
             '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
         ) AS j
),
total_rows AS (
    SELECT COUNT(*) AS cnt FROM cars
)
SELECT JSON_ARRAYAGG(DISTINCT key_name) AS common_keys
FROM all_keys, total_rows
GROUP BY key_name
HAVING COUNT(*) = total_rows.cnt;

语句解释 #

1. all_keys CTE：通过JSON_TABLE把每行content列的JSON键数组拆分为单独的行，每个键对应一行数据。
2. total_rows CTE：计算cars表的总数据行数，用来判断键是否在所有行都存在。
3. 最后分组统计每个键的出现次数，仅保留出现次数等于总行数的键，并用JSON_ARRAYAGG将这些键合并为一个JSON数组返回。

旧版本兼容方案（无CTE支持） #

如果你的MySQL/MariaDB版本不支持公共表表达式（CTE），可以改用子查询实现：

SELECT JSON_ARRAYAGG(DISTINCT key_name) AS common_keys
FROM (
    SELECT j.key_name
    FROM cars,
         JSON_TABLE(
             JSON_KEYS(content),
             '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
         ) AS j
) AS all_keys,
(SELECT COUNT(*) AS cnt FROM cars) AS total_rows
GROUP BY key_name
HAVING COUNT(*) = total_rows.cnt;

内容的提问来源于stack exchange，提问作者Kos-Mos