Mysql提取JSON对象和数组的方法示例代码_Javascript

1. json对象

{
  "age": 11,
  "name": "张三"
}

-- 定义变量json
set @json:='{"age":11,"name":"张三"}';

1.1 提取json的key

json_keys函数用于提取json的key值

-- 提取所有key
select json_keys(@json) as `keys`;

1.2 提取json的值

json_extract函数用于提取json的值

$.*取所有值
$.key取单个值

1.2.1 提取所有key的值

-- 提取所有key的值
select json_extract(@json, '$.*') as `values`;

1.2.2 提取指定key的值

-- 提取指定key的值
select json_extract(@json, '$."name"') as extracted_name;

1.2.3 提取指定key的值：去掉引号

json_unquote函数对单个结果进行去引号操作

-- 提取指定key的值：去掉引号
select json_unquote(json_extract(@json, '$."name"')) as extracted_name;

2. json数组

[
    {
        "age": 11,
        "name": "张三"
    },
    {
        "age": 12,
        "name": "李四"
    }
]

-- 定义变量json_array
set @json_array:='[{"age":11,"name":"张三"},{"age":12,"name":"李四"}]';

2.1 提取json数组的值

$[*] ：json数组所有对象

$[0] ：json数组第一个对象

$[*].key ：josn数组所有对象，对应key的值

$[0].key ：json数组第一个对象对应key的值

2.1.1 提取所有元素

json_table函数将 json 数组展开成关系表的形式，方便获取所有元素。

-- 提取所有元素：以表格形式展示每个对象的age和name字段
select element.*
from 
json_table(
    @json_array,
    '$[*]' columns (
        age int path '$."age"',
        name varchar(255) path '$."name"'
    )
) as element;

2.1.2 提取所有元素：指定key的值

-- 提取所有元素：指定key的值
select json_unquote(json_extract(@json_array, '$[*]."name"')) as extracted_name;

2.1.3 提取所有元素：指定key的值用逗号分隔

-- 提取所有元素：指定key的值用逗号分隔
select group_concat(names.name separator ',') as combined_names
from 
json_table(
    @json_array,
    '$[*]' columns (
        name varchar(255) path '$."name"'
    )
) as names;

2.1.4 提取指定索引的元素

-- 提取指定索引的元素
select json_extract(@json_array, '$[0]') as element_0;
-- 提取指定索引的元素中指定key的值
select json_extract(@json_array, '$[0]."name"') as name_0;

3. 总结

-- 定义变量json
set @json:='{"age":11,"name":"张三"}';
-- 提取json的key
select json_keys(@json) as `keys`;
-- 提取所有key的值
select json_extract(@json, '$.*') as `values`;
-- 提取指定key的值
select json_extract(@json, '$."name"') as extracted_name;
-- 提取指定key的值：去掉引号
select json_unquote(json_extract(@json, '$."name"')) as extracted_name;

-- ----------------------------------------------------------------------
-- 定义变量json_array
set @json_array:='[{"age":11,"name":"张三"},{"age":12,"name":"李四"}]';
-- 提取所有元素：以表格形式展示每个对象的age和name字段
select element.*
from 
json_table(
    @json_array,
    '$[*]' columns (
        age int path '$."age"',
        name varchar(255) path '$."name"'
    )
) as element;
-- 提取所有元素：指定key的值
select json_unquote(json_extract(@json_array, '$[*]."name"')) as extracted_name;
-- 提取所有元素：指定key的值用逗号分隔
select group_concat(names.name separator ',') as combined_names
from 
json_table(
    @json_array,
    '$[*]' columns (
        name varchar(255) path '$."name"'
    )
) as names;
-- 提取指定索引的元素
select json_extract(@json_array, '$[0]') as element_0;
-- 提取指定索引的元素中指定key的值
select json_extract(@json_array, '$[0]."name"') as name_0;

下面附一个具体的例子

假设你有一个名为users的表，其中有一个名为profile的列，该列存储了用户的json格式的个人资料：

create table users (
    id int auto_increment primary key,
    name varchar(100),
    profile json
);

insert into users (name, profile) values
('alice', '{"age": 30, "hobbies": ["reading", "hiking", "coding"]}'),
('bob', '{"age": 25, "hobbies": ["gaming", "cycling"]}'),
('carol', '{"age": 28, "hobbies": ["painting", "traveling"]}');

-- 获取alice的年龄
select json_extract(profile, '$.age') as age from users where name = 'alice';

-- 获取bob的爱好列表
select json_extract(profile, '$.hobbies') as hobbies from users where name = 'bob';

-- 给carol添加一个新的爱好
update users
set profile = json_insert(profile, '$.hobbies[2]', 'swimming')
where name = 'carol';

-- 移除alice的爱好列表中的“coding”
update users
set profile = json_remove(profile, '$.hobbies[2]')
where name = 'alice';

-- 检查json是否有效
select json_valid(profile) as is_valid from users;