MySQL 8.0中如何查询JSON嵌套对象结构里的子对象内容

针对嵌套JSON对象的查询需求，以下是适配MySQL 8.0的具体实现方案：

1. 单个userID查询指定行的用户属性 #

方式1：返回包含原外层键的用户对象 #

SELECT JSON_OBJECT(key_name, user_obj) AS user_data
FROM json_table t,
     -- 提取JSON字段的所有外层键
     JSON_TABLE(
         JSON_KEYS(t.json_column),
         '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
     ) keys,
     -- 根据外层键提取对应的用户对象
     JSON_TABLE(
         t.json_column,
         CONCAT('$."', key_name, '"') COLUMNS(user_obj JSON PATH '$')
     ) users
WHERE t.id = 1 AND user_obj->>'$.userID' = 123;

返回结果示例：

{"0": {"userID": 123, "name": "Name1", "age ": 20, "district": 5, "phone": 0}}

方式2：仅返回用户对象内容 #

SELECT user_obj AS user_data
FROM json_table t,
     JSON_TABLE(
         JSON_KEYS(t.json_column),
         '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
     ) keys,
     JSON_TABLE(
         t.json_column,
         CONCAT('$."', key_name, '"') COLUMNS(user_obj JSON PATH '$')
     ) users
WHERE t.id = 1 AND user_obj->>'$.userID' = 123;

返回结果示例：

{"userID": 123, "name": "Name1", "age ": 20, "district": 5, "phone": 0}

2. 多个userID查询指定行的用户属性 #

方式1：返回包含原键的组合JSON对象 #

SELECT JSON_OBJECTAGG(key_name, user_obj) AS user_data
FROM json_table t,
     JSON_TABLE(
         JSON_KEYS(t.json_column),
         '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
     ) keys,
     JSON_TABLE(
         t.json_column,
         CONCAT('$."', key_name, '"') COLUMNS(user_obj JSON PATH '$')
     ) users
WHERE t.id = 1 AND user_obj->>'$.userID' IN (123, 125);

返回结果示例：

{"0": {"userID": 123, "name": "Name1", "age ": 20, "district": 5, "phone": 0}, "2": {"userID": 125, "name": "Name3", "age ": 40, "district": 10, "relatedIDs": [50, 51]}}

方式2：返回用户对象组成的JSON数组 #

SELECT JSON_ARRAYAGG(user_obj) AS user_data
FROM json_table t,
     JSON_TABLE(
         JSON_KEYS(t.json_column),
         '$[*]' COLUMNS(key_name VARCHAR(255) PATH '$')
     ) keys,
     JSON_TABLE(
         t.json_column,
         CONCAT('$."', key_name, '"') COLUMNS(user_obj JSON PATH '$')
     ) users
WHERE t.id = 1 AND user_obj->>'$.userID' IN (123, 125);

返回结果示例：

[{"userID": 123, "name": "Name1", "age ": 20, "district": 5, "phone": 0}, {"userID": 125, "name": "Name3", "age ": 40, "district": 10, "relatedIDs": [50, 51]}]

优化建议：调整JSON结构 #

如果后续这类查询比较频繁，建议将外层的键值对结构改为数组结构，例如：

[
    {"userID": 123, "name": "Name1", "age ": 20, "district": 5, "phone": 0},
    {"userID": 124, "name": "Name2", "age ": 30, "district": 5, "cell": 0},
    {"userID": 125, "name": "Name3", "age ": 40, "district": 10, "relatedIDs": [50, 51]}
]

调整后查询会更简洁，例如单个userID查询：

SELECT user_obj AS user_data
FROM json_table t,
     JSON_TABLE(
         t.json_column,
         '$[*]' COLUMNS(user_obj JSON PATH '$')
     ) users
WHERE t.id = 1 AND user_obj->>'$.userID' = 123;

说明 #

你之前尝试的语句存在语法错误，JSON_CONTAINS的调用格式应为JSON_CONTAINS(json_column, '{"userID":123}')，但该函数仅能判断JSON是否包含指定值，无法精准提取目标用户对象，因此不适用当前需求。

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