【MySQL基础】 JSON函数入门

JSON（JavaScript Object Notation）是一种轻量级的数据互换格式，MySQL 从 5.7+版本开始支持JSON数据类型，并提供了丰富的JSON操纵函数。本文将详细介绍JSON数据操纵函数和JSON函数的应用，并通过具体示例帮助你掌握JSON的高效使用方法。    1 JSON数据操纵函数

      MySQL提供了多种JSON处理函数，重要包罗提取、修改、构建JSON等功能。    1.1 json_extract()与json_unquote()

      

• json_extract(json_doc, path)：从 JSON 文档中提取指定路径的值
  
• json_unquote(json_val)：去除 JSON 字符串的引号，返回纯文本
  

     

1. -- 提取JSON字段值
2. -- 示例数据
3. select json_extract('{"name": "张三", "age": 25}', '$.name') as name;
4. -- 输出: "张三"（带引号）
5. mysql> select json_extract('{"name": "张三", "age": 25}', '$.name') as name;
6. +----------+
7. | name     |
8. +----------+
9. | "张三"   |
10. +----------+
11. 1 row in set (0.01 sec)
13. mysql>
16. -- 使用json_unquote去掉引号
17. select json_unquote(json_extract('{"name": "张三", "age": 25}', '$.name')) as name;
18. -- 输出: 张三（纯文本）
19. mysql> select json_unquote(json_extract('{"name": "张三", "age": 25}', '$.name')) as name;
20. +--------+
21. | name   |
22. +--------+
23. | 张三   |
24. +--------+
25. 1 row in set (0.01 sec)
27. mysql>

复制代码

1.2 json_array()与json_object()

      

• json_array(val1, val2, ...)：构建JSON数组
  
• json_object(key1, val1, key2, val2, ...)：构建JSON对象
  

     

1. -- 构建json数组
2. select json_array(1, 'mysql', true, null) as json_array;
3. -- 输出: [1, "mysql", true, null]
4. mysql> select json_array(1, 'mysql', true, null) as json_array;
5. +--------------------------+
6. | json_array               |
7. +--------------------------+
8. | [1, "mysql", true, null] |
9. +--------------------------+
10. 1 row in set (0.01 sec)
12. mysql>
14. -- 构建json对象
15. select json_object('id', 1, 'name', '张三', 'is_active', true) as json_obj;
16. -- 输出: {"id": 1, "name": "张三", "is_active": true}
17. mysql> select json_object('id', 1, 'name', '张三', 'is_active', true) as json_obj;
18. +------------------------------------------------+
19. | json_obj                                       |
20. +------------------------------------------------+
21. | {"id": 1, "name": "张三", "is_active": true}   |
22. +------------------------------------------------+
23. 1 row in set (0.00 sec)
25. mysql>

复制代码

2 JSON 函数在实际开辟中的应用

2.1 解析嵌套JSON字段

2.1.1 数据准备

  

1. create table user (
2.     user_id int primary key,
3.     profile_data json
4. );
6. insert into user values
7. (1, '{"name": "张三", "contact": {"email": "zhangsan@example.com", "phone": "13800138000"}}'),
8. (2, '{"name": "李四", "contact": {"email": "lisi@example.com", "phone": null}}');
9. commit;

复制代码

2.1.2 查询嵌套字段

  

1. -- 查询所有用户的邮箱
2. select
3.     user_id,
4.     profile_data ->> '$.name' as name,
5.     profile_data ->> '$.contact.email' as email
6. from user;
8. mysql> select
9.     ->     user_id,
10.     ->     profile_data ->> '$.name' as name,
11.     ->     profile_data ->> '$.contact.email' as email
12.     -> from user;
13. +---------+--------+----------------------+
14. | user_id | name   | email                |
15. +---------+--------+----------------------+
16. |       1 | 张三   | zhangsan@example.com |
17. |       2 | 李四   | lisi@example.com     |
18. +---------+--------+----------------------+
19. 2 rows in set (0.00 sec)
21. mysql>

复制代码

2.2 存储和查询复杂结构数据

2.2.1 数据准备

  

1. create table orders (
2.     order_id int primary key,
3.     order_info json
4. );
6. insert into orders values
7. (1001, '{
8.     "order_date": "2023-10-01",
9.     "customer": "张三",
10.     "items": [
11.         {"product_id": 101, "name": "手机", "price": 5999, "quantity": 1},
12.         {"product_id": 205, "name": "耳机", "price": 399, "quantity": 2}
13.     ]
14. }');
15. commit;

复制代码

2.2.2 数据查询

  

1. -- 提取第一个商品名称
2. select
3.     order_id,
4.     order_info ->> '$.customer' as customer,
5.     order_info -> '$.items[0].name' as first_product
6. from orders;
8. mysql> select
9.     ->     order_id,
10.     ->     order_info ->> '$.customer' as customer,
11.     ->     order_info -> '$.items[0].name' as first_product
12.     -> from orders;
13. +----------+----------+---------------+
14. | order_id | customer | first_product |
15. +----------+----------+---------------+
16. |     1001 | 张三     | "手机"        |
17. +----------+----------+---------------+
18. 1 row in set (0.01 sec)
20. mysql>

复制代码

3 总结

   

场景	推荐函数
提取JSON字段	json_extract()
构建JSON	json_array()，json_object()
解析嵌套JSON	json_table()(MySQL 8.0+)
存储动态结构数据	直接使用JSON数据类型

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。