MySQL 解析数组 JSON|极客笔记

MySQL 解析数组 JSON

在 MySQL 中，JSON 类型的数据存储和处理越来越常见。而经常情况下，我们会需要解析 JSON 数据中的数组。本文将介绍如何在 MySQL 中解析数组类型的 JSON 数据。

什么是 JSON 数据类型

JSON（JavaScript Object Notation）是一种轻量级的数据交换格式，常用于在客户端和服务器之间传输数据。在 MySQL 中，JSON 数据类型是一种新加入的数据类型，可以存储 JSON 格式的数据。

一个简单的 JSON 数组如下所示：

["apple", "banana", "cherry"]

解析 JSON 数组

在 MySQL 中，我们可以使用一些内置的函数来解析 JSON 数组中的数据。

JSON_EXTRACT

JSON_EXTRACT 函数可以用来提取 JSON 数据中的特定值。

语法如下：

JSON_EXTRACT(json_doc, path)

其中 json_doc 是 JSON 字符串，path 是要提取的路径。路径格式类似于 JavaScript 对象的访问路径。

例如，我们有一个名为 fruits 的表，其中有一个名为 data 的 JSON 字段包含了水果的数组数据：

id	data
1	‘[“apple”, “banana”, “cherry”]’
2	‘[“orange”, “grape”, “kiwi”]’

我们可以使用以下查询来提取所有水果数据：

SELECT JSON_EXTRACT(data, "$[*]") AS fruits FROM fruits;

这将返回以下结果：

fruits
[“apple”, “banana”, “cherry”]
[“orange”, “grape”, “kiwi”]

JSON_TABLE

JSON_TABLE 函数可以将 JSON 数据解析为表格形式。

语法如下：

JSON_TABLE(json_doc, path COLUMNS (column_list) [AS alias])

其中 json_doc 是 JSON 字符串，path 是要提取的路径，column_list 是要提取的列名。

例如，我们可以使用 JSON_TABLE 来将 JSON 数据解析为表格：

SELECT j.*
FROM fruits,
JSON_TABLE(fruits.data, " $[*]" COLUMNS ( fruit VARCHAR(50) PATH '$ '
)) AS j;

这将返回以下结果：

fruit
apple
banana
cherry
orange
grape
kiwi

实际案例

假设我们有一个名为 orders 的表，其中有一个名为 products 的 JSON 字段，存储了每个订单中的产品数据：

order_id	customer_name	products
1	‘Alice’	‘[{“id”: 1, “name”: “Apple”, “quantity”: 3}, {“id”: 2, “name”: “Banana”, “quantity”: 2}]’
2	‘Bob’	‘[{“id”: 3, “name”: “Orange”, “quantity”: 5}, {“id”: 4, “name”: “Grape”, “quantity”: 1}]’

现在，我们想要提取出每个订单中的产品名称和数量：

SELECT o.order_id, o.customer_name, j.name, j.quantity
FROM orders o,
JSON_TABLE(o.products, " $[*]" COLUMNS ( name VARCHAR(50) PATH '$ .name',
  quantity INT PATH '$.quantity'
)) AS j;

这将返回以下结果：