在业务系统开发过程中，很多场景需要存储结构不固定的扩展数据，使用JSON类型字段存储这类数据是非常常见的方案。JSON字段支持多层嵌套结构，同时可以包含动态新增的字段，这给数据查询带来了一定的挑战，需要掌握对应的解析技巧才能高效获取目标数据。
不同数据库的JSON解析基础语法
主流关系型数据库都提供了JSON字段的解析函数，不同数据库的语法存在一定差异，下面分别介绍MySQL和PostgreSQL的基础解析方式。
MySQL JSON解析基础
MySQL提供了->和->>两个运算符，以及JSON_EXTRACT函数来解析JSON字段。->返回的是JSON类型的结果，->>返回的是字符串类型的结果。
-- 假设有表user_info，其中ext_json是JSON类型的字段，存储用户扩展信息
-- ext_json示例值：{"name": "张三", "address": {"city": "北京", "area": "海淀区"}, "hobbies": ["篮球", "阅读"]}
-- 查询address下的city字段，返回JSON类型
SELECT ext_json->'$.address.city' AS city_json FROM user_info WHERE id = 1;
-- 查询address下的city字段，返回字符串类型
SELECT ext_json->>'$.address.city' AS city_str FROM user_info WHERE id = 1;
-- 使用JSON_EXTRACT函数查询，效果和->一致
SELECT JSON_EXTRACT(ext_json, '$.address.city') AS city_extract FROM user_info WHERE id = 1;
PostgreSQL JSON解析基础
PostgreSQL支持json和jsonb两种JSON类型，解析时使用->和->>运算符，->返回json/jsonb类型，->>返回文本类型。
-- 假设有表user_info，其中ext_json是jsonb类型的字段 -- 查询address下的city字段，返回jsonb类型 SELECT ext_json->'address'->'city' AS city_json FROM user_info WHERE id = 1; -- 查询address下的city字段，返回文本类型 SELECT ext_json->'address'->>'city' AS city_str FROM user_info WHERE id = 1; -- 使用->>直接链式获取多层字段 SELECT ext_json->'address'->'area' AS area_str FROM user_info WHERE id = 1;
解析动态键名的JSON字段
有时候JSON中的键名是动态生成的，无法直接写死路径，这时候需要结合其他函数来动态获取键对应的值。
MySQL动态键名处理
MySQL可以使用JSON_KEYS获取JSON对象的所有键名，再结合变量或者子查询处理动态键。
-- 假设ext_json中有动态的业务字段，键名是业务类型编码，需要获取所有动态字段的值
-- 先获取所有键名，再遍历提取对应值，这里用存储过程的方式实现
DELIMITER //
CREATE PROCEDURE get_dynamic_json_fields(IN user_id INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE key_name VARCHAR(255);
DECLARE cur CURSOR FOR SELECT JSON_UNQUOTE(JSON_EXTRACT(JSON_KEYS(ext_json), CONCAT('$[', idx, ']')))
FROM user_info,
(SELECT 0 AS idx UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4) AS indexes
WHERE id = user_id AND idx < JSON_LENGTH(JSON_KEYS(ext_json));
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
read_loop: LOOP
FETCH cur INTO key_name;
IF done THEN
LEAVE read_loop;
END IF;
-- 动态拼接查询语句获取对应键的值
SET @sql = CONCAT('SELECT ext_json->>''$.', key_name, ''' AS ', key_name, ' FROM user_info WHERE id = ', user_id);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END LOOP;
CLOSE cur;
END //
DELIMITER ;
-- 调用存储过程
CALL get_dynamic_json_fields(1);
PostgreSQL动态键名处理
PostgreSQL可以使用jsonb_object_keys函数获取JSON对象的所有键名，再结合循环处理动态字段。
-- 使用PL/pgSQL函数处理动态键名
CREATE OR REPLACE FUNCTION get_dynamic_json_fields(user_id INT)
RETURNS TABLE(field_name TEXT, field_value TEXT) AS $$
DECLARE
key_name TEXT;
json_val JSONB;
BEGIN
-- 获取目标用户的JSON字段
SELECT ext_json INTO json_val FROM user_info WHERE id = user_id;
-- 遍历所有键名
FOR key_name IN SELECT jsonb_object_keys(json_val) LOOP
field_name := key_name;
field_value := json_val->>key_name;
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- 调用函数查询
SELECT * FROM get_dynamic_json_fields(1);
解析JSON数组嵌套的多层字段
JSON字段中经常包含数组结构，数组元素也可能是对象，需要进一步解析数组内对象的字段。
MySQL解析JSON数组
MySQL使用JSON_TABLE函数可以将JSON数组转换为关系型行数据，方便后续查询。
-- 假设ext_json中的hobbies是数组，需要解析数组中的所有元素
SELECT jt.hobby
FROM user_info,
JSON_TABLE(ext_json, '$.hobbies[*]' COLUMNS (hobby VARCHAR(255) PATH '$')) AS jt
WHERE id = 1;
-- 如果数组元素是对象，比如ext_json中有orders字段，每个元素是{"order_id": 1, "amount": 100}
-- orders示例值：[{"order_id": 1, "amount": 100}, {"order_id": 2, "amount": 200}]
SELECT jt.order_id, jt.amount
FROM user_info,
JSON_TABLE(ext_json, '$.orders[*]' COLUMNS (
order_id INT PATH '$.order_id',
amount DECIMAL(10,2) PATH '$.amount'
)) AS jt
WHERE id = 1;
PostgreSQL解析JSON数组
PostgreSQL使用jsonb_array_elements函数展开JSON数组，再解析每个元素的字段。
-- 解析hobbies数组
SELECT jsonb_array_elements(ext_json->'hobbies')->>'value' AS hobby
FROM user_info
WHERE id = 1;
-- 解析对象类型的数组元素
SELECT
(elem->>'order_id')::INT AS order_id,
(elem->>'amount')::DECIMAL(10,2) AS amount
FROM user_info,
jsonb_array_elements(ext_json->'orders') AS elem
WHERE id = 1;
解析技巧总结
- 优先使用数据库原生的JSON解析运算符，性能比通用函数更好
- 处理动态键名时，尽量通过数据库内置的键名提取函数结合动态SQL实现，避免硬编码
- 解析多层嵌套的JSON数组时，使用数组展开函数将JSON结构转换为行数据，更符合SQL的查询逻辑
- 如果JSON字段的查询频率很高，可以考虑给JSON字段的常用路径创建函数索引，提升查询效率
注意不同数据库的JSON函数存在差异，实际使用时需要根据项目使用的数据库版本选择对应的语法，避免语法错误。