MySQL读取JSON转换的方式

MySQL 5.7及以上版本支持JSON数据类型，但在某些场景下我们需要将JSON数据进行读取、转换或者查询，以满足业务需求。

MySQL 5.7及以上版本支持JSON数据类型，但在某些场景下我们需要将JSON数据进行读取、转换或者查询，以满足业务需求。

以下是MySQL读取JSON转换的方式的完整攻略：

1. 查询JSON对象的属性

可以通过箭头运算符->或->>、JSON_EXTRACT函数查询JSON对象的属性。其中，->返回JSON属性的文本格式，->>及JSON_EXTRACT()返回JSON属性的值。

-- 查询JSON对象中user属性的值
SELECT json->'$.user' as user FROM table;
SELECT json->>'$.user' as user FROM table;
SELECT JSON_EXTRACT(json, '$.user') as user FROM table;

2. 获取JSON属性个数

可以使用JSON_LENGTH函数获取JSON对象的属性个数。

-- 获取JSON对象属性个数
SELECT JSON_LENGTH(json) as length FROM table;

3. 查询JSON对象的属性并转换为行

可以使用JSON_TABLE函数将JSON对象的属性转换为行，进而达到检索、过滤、分组和联接等目的。

-- 将JSON对象中id、name属性转换为行
SELECT jt.id, jt.name FROM table, 
  JSON_TABLE(json, '$' COLUMNS(
    id INT PATH '$.id',
    name VARCHAR(50) PATH '$.name'
  )) as jt;

4. 更新JSON对象属性

可以使用->或->>、JSON_SET或JSON_REPLACE函数来更新JSON对象的属性。

-- 更新user属性的值
UPDATE table SET json = JSON_SET(json, '$.user', 'updated user') WHERE id = 1;
UPDATE table SET json = JSON_REPLACE(json, '$.user', 'updated user') WHERE id = 1;
SET @json = (SELECT json FROM table WHERE id = 1);
SET @json = JSON_SET(@json, '$.user', 'updated user');
UPDATE table SET json = @json WHERE id = 1;

示例说明

以下分别以查询JSON对象、获取JSON对象属性个数两个场景为示例说明。

示例一：查询JSON对象

以查询JSON对象中score属性的值为例。假设有一张表user，其中有一列json存储了JSON格式的用户信息。

CREATE TABLE user (
  id INT PRIMARY KEY,
  json JSON
);

表中数据如下：

现在我们需要查询id为1的用户的数学成绩，可以使用以下SQL语句：

SELECT json->'$.score.math' as score FROM user WHERE id = 1;

结果：

示例二：获取JSON对象属性个数

以获取JSON对象属性个数为例。假设有一张表obj，其中有一列json存储了JSON格式的数据。

CREATE TABLE obj (
  id INT PRIMARY KEY,
  json JSON
);

表中数据如下：

现在我们需要查询每条数据的属性个数，可以使用以下SQL语句：

SELECT id, JSON_LENGTH(json) as length FROM obj;

结果：