MySQL高级函数之：`JSON_PRETTY()`：其在格式化`JSON`输出时的应用。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

MySQL 高级函数之：`JSON_PRETTY()`：格式化 JSON 输出

大家好，今天我们来深入探讨 MySQL 中一个非常有用的 JSON 函数：JSON_PRETTY()。在处理 JSON 数据时，可读性至关重要，特别是当我们需要调试、分析或者将 JSON 数据呈现给其他人时。JSON_PRETTY() 函数能够帮助我们格式化 JSON 数据，使其更易于阅读和理解。

什么是 JSON？

首先，让我们快速回顾一下 JSON (JavaScript Object Notation)。JSON 是一种轻量级的数据交换格式，它以易于人阅读和编写的方式组织数据。JSON 数据由键值对组成，类似于 Python 中的字典或 JavaScript 中的对象。

例如：

{
  "name": "John Doe",
  "age": 30,
  "city": "New York"
}

JSON_PRETTY() 函数简介

JSON_PRETTY() 是 MySQL 5.7.22 版本引入的函数，它的作用是将一个有效的 JSON 文档进行格式化，使其更易于阅读。该函数接受一个 JSON 文档作为输入，并返回一个格式化的 JSON 字符串。

JSON_PRETTY() 的语法

JSON_PRETTY(json_doc)

其中 json_doc 是一个有效的 JSON 文档，它可以是：

一个 JSON 字符串字面量
一个包含 JSON 数据的列
一个返回 JSON 数据的表达式

JSON_PRETTY() 的使用示例

让我们通过一些示例来了解 JSON_PRETTY() 的用法。

示例 1：格式化 JSON 字符串字面量

SELECT JSON_PRETTY('{"name": "John Doe", "age": 30, "city": "New York"}');

执行结果：

{
  "name": "John Doe",
  "age": 30,
  "city": "New York"
}

可以看到，原始的 JSON 字符串被格式化成更易读的结构。

示例 2：格式化表中的 JSON 数据

假设我们有一个名为 users 的表，其中包含一个名为 profile 的 JSON 列。

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    profile JSON
);

INSERT INTO users (name, profile) VALUES
('Alice', '{"age": 25, "city": "London", "interests": ["reading", "traveling"]}'),
('Bob', '{"age": 35, "city": "Paris", "skills": ["programming", "design"]}');

现在，我们可以使用 JSON_PRETTY() 来格式化 profile 列中的 JSON 数据。

SELECT id, name, JSON_PRETTY(profile) AS formatted_profile FROM users;

执行结果：

id	name	formatted_profile
1	Alice	`{<br> "age": 25,<br> "city": "London",<br> "interests": [<br> "reading",<br> "traveling"<br> ]<br>}`
2	Bob	`{<br> "age": 35,<br> "city": "Paris",<br> "skills": [<br> "programming",<br> "design"<br> ]<br>}`

从结果中可以看到，profile 列中的 JSON 数据已经被格式化。

示例 3：结合其他 JSON 函数使用

JSON_PRETTY() 可以与其他 JSON 函数结合使用，以实现更复杂的功能。

例如，我们可以使用 JSON_EXTRACT() 提取 JSON 数据中的特定值，然后使用 JSON_PRETTY() 格式化结果。

SELECT id, name, JSON_PRETTY(JSON_EXTRACT(profile, '$.interests')) AS formatted_interests FROM users;

执行结果：

id	name	formatted_interests
1	Alice	`[<br> "reading",<br> "traveling"<br>]`
2	Bob	`null`

在这个例子中，我们提取了 profile 列中 interests 字段的值，并使用 JSON_PRETTY() 进行了格式化。注意，由于 Bob 的 profile 中没有 interests 字段，所以结果为 null。

JSON_PRETTY() 的局限性

虽然 JSON_PRETTY() 非常有用，但它也有一些局限性：

MySQL 版本要求： JSON_PRETTY() 函数是在 MySQL 5.7.22 版本中引入的，因此，如果你使用的是更早的版本，则无法使用该函数。
性能影响： 格式化 JSON 数据需要一定的计算资源，因此，在处理大量数据时，可能会对性能产生一定的影响。建议在必要时使用，避免在生产环境中过度使用。
仅格式化输出： JSON_PRETTY() 仅仅是格式化输出，并不能修改 JSON 数据本身。如果需要修改 JSON 数据，你需要使用其他 JSON 函数，例如 JSON_SET(), JSON_REPLACE(), JSON_INSERT(), JSON_REMOVE() 等。

JSON_PRETTY() 的替代方案

如果你使用的 MySQL 版本低于 5.7.22，或者需要更高级的 JSON 格式化功能，可以考虑以下替代方案：

使用客户端工具： 许多数据库客户端工具（例如 DBeaver、SQL Developer 等）都提供了 JSON 格式化功能。你可以将 JSON 数据从数据库中导出，然后在客户端工具中进行格式化。
使用编程语言： 可以在编程语言（例如 Python、Java 等）中使用 JSON 库来格式化 JSON 数据。例如，在 Python 中，可以使用 json.dumps() 函数，并设置 indent 参数来控制缩进。

import json

data = {"name": "John Doe", "age": 30, "city": "New York"}
formatted_json = json.dumps(data, indent=2)
print(formatted_json)

自定义存储过程/函数： 如果需要特别定制的格式化方式, 可以在 MySQL 中创建自定义的存储过程或函数来实现更复杂的 JSON 格式化逻辑。这需要对 MySQL 存储过程和 JSON 处理有较深入的了解。

更复杂的例子：使用JSON_PRETTY格式化嵌套的JSON结构

假设我们有如下的products表：

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    details JSON
);

INSERT INTO products (name, details) VALUES
('Laptop', '{"brand": "Dell", "specs": {"cpu": "Intel i7", "ram": "16GB", "storage": "512GB SSD"}}'),
('Monitor', '{"brand": "LG", "specs": {"resolution": "4K", "size": "27 inch", "panel_type": "IPS"}}');

现在，我们可以使用 JSON_PRETTY() 来格式化 details 列中的 JSON 数据，该数据包含嵌套的 JSON 结构。

SELECT id, name, JSON_PRETTY(details) AS formatted_details FROM products;

执行结果：

id	name	formatted_details
1	Laptop	`{<br> "brand": "Dell",<br> "specs": {<br> "cpu": "Intel i7",<br> "ram": "16GB",<br> "storage": "512GB SSD"<br> }<br>}`
2	Monitor	`{<br> "brand": "LG",<br> "specs": {<br> "resolution": "4K",<br> "size": "27 inch",<br> "panel_type": "IPS"<br> }<br>}`

可以看到，嵌套的 JSON 结构也被正确地格式化了，提高了可读性。

在存储过程中使用JSON_PRETTY

为了方便使用，我们可以创建一个存储过程来格式化 JSON 数据。

DELIMITER //
CREATE PROCEDURE FormatJSON(IN json_data JSON)
BEGIN
    SELECT JSON_PRETTY(json_data);
END //
DELIMITER ;

然后，我们可以调用该存储过程来格式化 JSON 数据。

CALL FormatJSON('{"name": "John Doe", "age": 30, "city": "New York"}');

错误处理

如果传递给 JSON_PRETTY() 函数的参数不是有效的 JSON 文档，则会返回 NULL。因此，在使用 JSON_PRETTY() 函数时，应该确保输入的 JSON 数据是有效的。

SELECT JSON_PRETTY('invalid json'); -- 返回 NULL

为了避免这种情况，可以使用 JSON_VALID() 函数来验证 JSON 数据的有效性。

SELECT JSON_PRETTY(IF(JSON_VALID('invalid json'), 'invalid json', NULL)); -- 返回 NULL
SELECT JSON_PRETTY(IF(JSON_VALID('{"name": "John Doe"}'), '{"name": "John Doe"}', NULL)); -- 返回格式化的 JSON

与其他数据库系统的对比

不同的数据库系统对于 JSON 数据的处理方式有所不同。

PostgreSQL: PostgreSQL 提供了 jsonb_pretty() 函数，其功能类似于 MySQL 的 JSON_PRETTY()。
SQL Server: SQL Server 提供了 JSON_QUERY() 函数，可以用于提取 JSON 数据，但没有直接提供格式化 JSON 输出的函数。可以使用客户端工具或编程语言来格式化 JSON 数据。

表格：JSON_PRETTY() 与其他数据库系统的对比

数据库系统	格式化 JSON 函数
MySQL	`JSON_PRETTY()`
PostgreSQL	`jsonb_pretty()`
SQL Server	无内置函数，需借助客户端工具或编程语言

总结：JSON_PRETTY() 提高了JSON数据可读性

JSON_PRETTY() 函数是 MySQL 中一个非常有用的 JSON 函数，它可以帮助我们格式化 JSON 数据，使其更易于阅读和理解。虽然它有一些局限性，但在许多情况下，它仍然是一个非常有效的工具。通过结合其他 JSON 函数，我们可以实现更复杂的 JSON 数据处理功能。掌握 JSON_PRETTY() 函数，可以大大提高我们在 MySQL 中处理 JSON 数据的效率。

关于其他格式化方案

除了 JSON_PRETTY() 之外, 还可以考虑在应用程序层面进行JSON格式化, 特别是在需要更精细的控制或者在MySQL版本低于5.7.22时。例如, 使用Python的json.dumps()方法可以方便地控制缩进和排序等格式化选项。

考虑性能影响

虽然JSON_PRETTY() 提高了可读性，但在处理大量数据时，格式化操作可能会影响性能。在性能敏感的场景中，应该权衡可读性和性能，避免过度使用格式化。

有效性验证至关重要

在使用JSON_PRETTY() 之前，始终应该使用JSON_VALID() 验证JSON数据的有效性，以避免出现意外的错误或NULL结果。

MySQL 高级函数之：JSON_PRETTY()：格式化 JSON 输出

发表回复 取消回复

MySQL 高级函数之：`JSON_PRETTY()`：格式化 JSON 输出

发表回复取消回复