MySQL解析JSON数组|极客笔记

MySQL解析JSON数组

介绍

JSON（JavaScript Object Notation）是一种常用的数据交换格式，广泛应用于前后端数据交互、配置文件等方面。MySQL 5.7及以上版本开始支持JSON数据类型，并提供了丰富的JSON函数和操作符，方便对JSON数据进行解析和处理。

本文将详细介绍在MySQL中如何解析JSON数组，包括创建JSON数组、查询和过滤数组元素、数组操作等方面的内容，并通过示例代码演示相关操作。

创建JSON数组

在MySQL中，可以使用函数JSON_ARRAY()创建一个JSON数组。该函数可以接受多个参数，每个参数将作为新数组的一个元素。下面是一个示例：

SELECT JSON_ARRAY('apple', 'banana', 'orange');

运行结果：

["apple", "banana", "orange"]

可以看到，函数JSON_ARRAY()将传入的参数封装为一个JSON数组。

查询JSON数组元素

基本查询

要查询JSON数组中的元素，可以使用->或->>操作符。两者的区别在于，->返回的是JSON对象或数组的子元素，而->>返回的是子元素的字符串形式。

假设我们有一个表fruits，其中包含一个名为data的JSON列，存储了水果的信息。下面是一个示例表：

CREATE TABLE fruits (
  id INT PRIMARY KEY AUTO_INCREMENT,
  data JSON
);

INSERT INTO fruits (data) VALUES
  ('[{"name": "apple", "color": "red"}, {"name": "banana", "color": "yellow"}, {"name": "orange", "color": "orange"}]')

要查询第一个水果的名称，可以使用如下语句：

SELECT data->'$[0].name' FROM fruits;

运行结果：

["apple"]

要查询所有水果的名称，可以使用如下语句：

SELECT data->>'$[*].name' FROM fruits;

运行结果：

["apple", "banana", "orange"]

条件查询

如果想要查询满足某些条件的JSON数组元素，可以使用JSON_CONTAINS()函数和WHERE子句。下面是一个示例：

SELECT data->'lax $.name' FROM fruits WHERE JSON_CONTAINS(data, '{"name": "banana"}');

运行结果：

["banana"]

这个示例中，JSON_CONTAINS()函数用于判断JSON数组中是否包含某个元素。WHERE子句用于过滤满足条件的记录。

修改JSON数组

增加元素

要向JSON数组中添加元素，可以使用JSON_ARRAY_APPEND()函数。该函数需要三个参数，分别是要修改的JSON字符串、要新增的元素路径和要新增的元素值。

下面是一个示例，向表fruits中的data字段添加一个新的水果：

UPDATE fruits SET data = JSON_ARRAY_APPEND(data, '$', '{"name": "grape", "color": "purple"}');

运行结果：

Query OK, 1 row affected (0.01 sec)

删除元素

要从JSON数组中删除元素，可以使用JSON_REMOVE()函数。该函数接受两个参数，第一个参数是要修改的JSON字符串，第二个参数是要删除的元素路径。

下面是一个示例，从表fruits中的data字段删除名为”banana”的水果：

UPDATE fruits SET data = JSON_REMOVE(data, ' $[1]') WHERE JSON_EXTRACT(data, '$ [1].name') = 'banana';

运行结果：

Query OK, 1 row affected (0.01 sec)

结论

本文介绍了在MySQL中解析JSON数组的方法，包括创建JSON数组、查询和过滤数组元素、数组操作等方面的内容。通过使用MySQL提供的丰富的JSON函数和操作符，可以方便地处理和操作JSON数据。

MySQL解析JSON数组