MySQL查询JSON数组语法求助：提取数据与检索特定ID

1. 提取scores数组中的id和score #

要将JSON数组中的数据展开为关系型行，需使用MySQL 8.0及以上版本支持的JSON_TABLE函数：

SELECT 
    ag.id AS game_record_id, -- 若表有主键可添加，用于区分不同游戏记录
    jt.score_id,
    jt.score_value
FROM 
    activegames ag
JOIN 
    JSON_TABLE(
        ag.gameresults->'$.scores',
        '$[*]' COLUMNS(
            score_id VARCHAR(20) PATH '$.id',
            score_value INT PATH '$.score'
        )
    ) jt;

该查询会把每条activegames记录里scores数组的每个元素拆分为单独行，输出对应的id和score值。

2. 查询包含特定id的记录 #

检查scores数组是否包含指定id，可使用JSON_CONTAINS函数，需构造匹配的JSON对象结构：

-- 将'目标ID'替换为实际要查询的id值
SELECT * 
FROM activegames 
WHERE JSON_CONTAINS(gameresults->'$.scores', '{"id": "目标ID"}');

如果在PHP脚本中动态传入id，建议用预处理语句避免SQL注入：

// PHP PDO示例代码
$targetId = "14916624";
$stmt = $pdo->prepare("SELECT * FROM activegames WHERE JSON_CONTAINS(gameresults->'$.scores', :jsonObj)");
$stmt->bindValue(':jsonObj', json_encode(["id" => $targetId]), PDO::PARAM_STR);
$stmt->execute();
$results = $stmt->fetchAll();

补充说明 #

• 确保MySQL版本为8.0或更高，JSON_TABLE及部分高级JSON函数在5.7版本中不支持。
• 若使用MySQL 5.7，可通过JSON_EXTRACT结合JSON_LENGTH遍历数组，但写法繁琐，推荐升级到8.0版本简化查询。

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