掌握MySQL中的JSON: 第一部分 – 数组操作

在这个以数据为中心的时代里，无论我们是从事数据分析、开发还是业务管理，掌握 SQL（结构化查询语言）几乎成了通行证。SQL 不仅是我们与数据库对话的语言，更是我们洞悉数据背后故事的钥匙。作为一名长期与数据打交道的专业人士，我可以说，没有什么工具比 SQL 更能贴近我的日常工作了。它伴随我走过了无数个项目，从解析复杂的数据迷阵到提升查询的效率和精准度，每一步都充满挑战和乐趣。

今天，我想和大家聊聊一个特别的话题 —— 如何在 MySQL 中高效处理 JSON 数据。在信息技术飞速发展的今天，JSON（JavaScript Object Notation）已经成为了数据存储和交换的热门格式，它以其灵活性和易读性受到了广泛的青睐。尤其在 MySQL 中，JSON 数据类型的引入为我们处理这种数据格式提供了强大的工具。然而，正如所有好工具一样，如果不知道如何使用它，那它也只是一件摆设。

经过这些年的实践，我发现，尽管 JSON在业界如此流行，但真正了解如何在 MySQL 中有效利用这一数据类型的人却并不多。因此，希望通过分享我个人的一些经验、技巧和最佳实践，能够帮助您在使用 MySQL 处理 JSON 数据时如鱼得水。无论您是初学者还是希望进一步提升技能的专业人士，我相信这里的内容都会对您有所帮助。

在业务开发中，有时候我们有存储数组的需求，但是可能方便查询，这时候我们可以通过函数json_table将数组转换成行，从而便于我们进行查询。

🚀 Step 1 数据准备

我们首先生成一些示例数据：

create table user_preferences (  
    id int primary key,  
    name varchar(50),  
    age int,  
    salary decimal(10, 2),  
    preferences json  
);  
  
insert into user_preferences (id, name, age, salary, preferences) values  
    (1, 'Alice', 25, 50000, '["Swimming", "Reading"]'),  
    (2, 'Bob', 30, 60000, '["Gaming", "Traveling"]'),  
    (3, 'Charlie', 35, 55000, '["Cooking", "Hiking"]'),  
    (4, 'David', 40, 45000, '["Cycling", "Photography"]'),  
    (5, 'Eva', 45, 65000, '["Writing", "Painting"]');  

🚀 Step 2 数组转行

我们现在有个需求，需要把preferences数组里的内容查询来，并且拆分转成列，该怎么办呢

select  
    up.id,   
    up.name,  
    up.age, up.salary,  
    user_preferences_temp.sp_preferences  
from  
     user_preferences up,  
     json_table(  
     up.preferences,  
     "$[*]"  
        columns(  
        sp_preferences varchar(100) path "$"   
               )  
) as user_preferences_temp;

结果：

好了，可以看到，我们已经拿出来了。但是有时候我们会遇到一些错误异常，所以我们还得修改一下：

select  
    up.id,   
    up.name,  
    up.age, up.salary,  
    user_preferences_temp.sp_preferences  
from  
     user_preferences up,  
     json_table(  
     up.preferences,  
     "$[*]"  
        columns(  
        sp_preferences varchar(100)   
          path "$" default '9999' on error  
               )  
) as user_preferences_temp;  

如果记录中数组结果为空，但是想给一些默认值，此时可以加on empty 。

比如，这时我们需要查询喜欢阅读的用户：

select  
    up.id,   
    up.name,  
    up.age, up.salary,  
    user_preferences_temp.sp_preferences  
from  
     user_preferences up,  
     json_table(  
     up.preferences,  
     "$[*]"  
        columns(  
        sp_preferences varchar(100)   
          path "$" default '9999' on error  
               )  
) as user_preferences_temp  
  where user_preferences_temp.sp_preferences='Reading'

结果：

🚀 Step 3 嵌套数组

接下来，我们在这个表上加一个字段，然后生成一些数据。

该字段是一个嵌套数组，例如[{"level": ["Beginner", "Intermediate"], "skill": "Data Analysis"}, {"level": ["Intermediate"], "skill": "Programming"}]

ALTER TABLE user_preferences  
ADD COLUMN details JSON;  
  
  
  
UPDATE user_preferences SET details =   
CASE   
    WHEN id = 1 THEN '[{"skill": "Data Analysis", "level": ["Beginner", "Intermediate"]}, {"skill": "Programming", "level": ["Intermediate"]}]'  
    WHEN id = 2 THEN '[{"skill": "Project Management", "level": ["Advanced"]}, {"skill": "Public Speaking", "level": ["Beginner", "Intermediate"]}]'  
    WHEN id = 3 THEN '[{"skill": "Graphic Design", "level": ["Intermediate", "Advanced"]}]'  
    WHEN id = 4 THEN '[{"skill": "Web Development", "level": ["Advanced"]}, {"skill": "Photography", "level": ["Beginner"]}]'  
    WHEN id = 5 THEN '[{"skill": "Writing", "level": ["Intermediate", "Advanced"]}, {"skill": "Editing", "level": ["Advanced"]}]'  
END;  

数据准备好之后，我们怎么可以把`skill` 和`level` 直观地拿出来呢？  

select  
    id, name, skill, `level`  
from  
     user_preferences up,  
     json_table(  
     up.details,  
     "$[*]"  
        columns(  
        skill varchar(50) path '$.skill',  
        nested path '$.level[*]' columns (`level` varchar(60) path '$')  
               )  
) as user_preferences_temp  

结果：

🚀 Step 4 常用函数

❇️ json_array_append

• 在指定的数组后追加元素

set @customers = '["Tom", ["Jack", "Tony"], "David"]';  
  
select json_array_append(@customers,'$[1]','Beck');  
-- ["Tom", ["Jack", "Tony", "Beck"], "David"]  
  
select json_array_append(@customers,'$[0]','Beck');  
-- [["Tom", "Beck"], ["Jack", "Tony"], "David"]  

❇️ json_array_insert

• 向数组中插入元素

set @customers = '["Tom", ["Jack", "Tony"], "David"]';  
  
select json_array_insert(@customers,'$[0]','Beck');  
-- ["Beck", "Tom", ["Jack", "Tony"], "David"]

❇️ json_merge

• 合并两个json对象

select json_merge('[1, 2]', '[8, 9]');  
-- [1, 2, 8, 9]  
  
select json_merge('[1, 2]', '{"stat": "success"}');  
-- [1, 2, {"stat": "success"}]

⚒️ 总结

今天就到这里了，其中json_table的使用场景还是蛮多的，比如通过json_table转化数据进行后续join等。