MySQL表中未知键名时提取JSON格式数据前5条记录

前提说明 #

假设你的表名为data_table，存储JSON数据的字段名为json_col，以下示例基于MySQL 8.0+（用到的JSON_TABLE函数需8.0及以上版本支持）。

1. 筛选包含多个键的记录并取前5条 #

如果需要找出JSON对象里至少包含2个键的记录，取前5条，可通过JSON_LENGTH和JSON_KEYS组合实现：

SELECT 
    id,
    json_col,
    JSON_LENGTH(JSON_KEYS(json_col)) AS total_keys
FROM data_table
WHERE JSON_LENGTH(JSON_KEYS(json_col)) >= 2
ORDER BY total_keys DESC
LIMIT 5;

• JSON_KEYS(json_col)返回当前JSON对象的所有键组成的数组
• JSON_LENGTH计算数组长度，也就是JSON键的总数量
• 通过WHERE筛选键数≥2的记录，最后用LIMIT 5取前5条结果

2. 提取前5条记录的所有键值对 #

如果要把前5条目标记录的所有键和对应值拆分为单独行展示，可嵌套子查询结合JSON_TABLE实现：

SELECT 
    dt.id,
    j.key_name,
    JSON_UNQUOTE(JSON_EXTRACT(dt.json_col, CONCAT('$.', j.key_name))) AS key_value
FROM (
    -- 先筛选出前5个符合键数要求的记录
    SELECT id, json_col
    FROM data_table
    WHERE JSON_LENGTH(JSON_KEYS(json_col)) >= 2
    LIMIT 5
) dt,
-- 将每个记录的JSON键展开为多行
JSON_TABLE(
    JSON_KEYS(dt.json_col),
    '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
) j;

• 子查询先锁定目标前5条记录
• JSON_TABLE把JSON_KEYS返回的键数组拆分为单行单键的格式
• JSON_EXTRACT根据键名取值，JSON_UNQUOTE去除字符串值的引号

3. 基于值的条件筛选（未知键名） #

如果要筛选JSON中至少有2个值满足特定条件（比如值为整数且大于10）的记录，取前5条：

SELECT 
    id,
    json_col,
    COUNT(*) AS matching_values
FROM data_table,
JSON_TABLE(
    JSON_KEYS(json_col),
    '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
) j
-- 先判断值的类型为整数，再校验值的大小
WHERE JSON_TYPE(JSON_EXTRACT(json_col, CONCAT('$.', j.key_name))) = 'INTEGER'
  AND JSON_EXTRACT(json_col, CONCAT('$.', j.key_name)) > 10
GROUP BY id, json_col
-- 要求至少有2个值符合条件
HAVING matching_values >= 2
ORDER BY matching_values DESC
LIMIT 5;

• JSON_TYPE用于判断值的类型，避免类型转换报错
• 分组后通过HAVING筛选符合条件的值数量≥2的记录

低版本MySQL兼容方案（8.0以下） #

如果你的MySQL版本低于8.0，没有JSON_TABLE，可以用递归CTE展开键：

WITH RECURSIVE json_keys AS (
    SELECT 
        id,
        json_col,
        JSON_KEYS(json_col) AS keys_array,
        0 AS idx
    FROM data_table
    WHERE JSON_LENGTH(JSON_KEYS(json_col)) >= 2
    UNION ALL
    SELECT 
        id,
        json_col,
        keys_array,
        idx + 1
    FROM json_keys
    WHERE idx < JSON_LENGTH(keys_array) - 1
)
SELECT 
    id,
    json_col,
    JSON_UNQUOTE(JSON_EXTRACT(keys_array, CONCAT('$[', idx, ']'))) AS key_name,
    JSON_UNQUOTE(JSON_EXTRACT(json_col, CONCAT('$.', JSON_EXTRACT(keys_array, CONCAT('$[', idx, ']'))))) AS key_value
FROM json_keys
ORDER BY id
LIMIT 5;

• 递归CTE逐个取出键数组中的每个键名，再提取对应值
• 最后用LIMIT 5控制结果数量

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