SQL - JSON 函数：初学者指南

你好，未来的数据库大师们！今天，我们将踏上一段激动人心的旅程，探索 SQL 和 JSON 函数的世界。如果你之前从未编写过一行代码，也不用担心——我会成为你友好的向导，我们会一步步地进行。在本教程结束时，你将能够像专业人士一样熟练地处理 JSON 数据！

什么是 JSON？

在我们深入 SQL 函数之前，让我们先来了解一下 JSON。JSON 是 JavaScript Object Notation 的缩写，它是存储和交换数据的一种流行方式。可以把它看作是一种组织信息的方式，这种格式既便于人类阅读，也便于计算机处理。

例如，以下是一些关于一本书的 JSON 数据：

{
"title": "SQL 冒险之旅",
"author": "简·编码者",
"year": 2023,
"genres": ["技术", "教育"]
}

看看这有多么整洁有序！这就是 JSON 的魅力所在！

为什么在 SQL 中使用 JSON？

你现在可能在想：“我们为什么需要在数据库中使用 JSON？”想象一下，你正在经营一个在线书店。有些书可能有多个作者，而有些书只有一个作者。有些书可能有多种类型，而有些书只有几种类型。JSON 允许我们灵活地存储这种多样化的信息，而无需创建大量独立的表。

SQL JSON 函数：你的新好朋友

让我们来认识一下我们在 SQL-JSON 游戏中的明星球员。这些函数帮助我们在 SQL 数据库中处理 JSON 数据。

1. JSON_VALUE()

这个函数就像一个寻宝者。它会深入你的 JSON 数据并提取一个特定的值。

SELECT JSON_VALUE('{"name": "Alice", "age": 30}', '$.name') AS FirstName;

这将返回：

FirstName
--------
Alice

在这里，我们要求 SQL 查看我们的 JSON 数据并找到与 "name" 键关联的值。

2. JSON_QUERY()

虽然 JSON_VALUE 只检索单个值，但 JSON_QUERY 可以获取整个对象或数组。

SELECT JSON_QUERY('{"books": ["SQL 101", "JSON 精通"]}', '$.books') AS BookList;

结果：

BookList
--------
["SQL 101", "JSON 精通"]

当需要提取嵌套的 JSON 结构时，这个函数非常完美。

3. JSON_MODIFY()

这个函数就像你的个人 JSON 编辑器。它允许你在 JSON 数据中更改值。

DECLARE @json NVARCHAR(100) = '{"name": "Bob", "age": 35}';
SELECT JSON_MODIFY(@json, '$.age', 36) AS UpdatedJSON;

结果：

UpdatedJSON
-----------
{"name": "Bob", "age": 36}

我们刚刚给 Bob 过了一个生日！我们把他的年龄从 35 改成了 36。

4. ISJSON()

这个函数是我们的 JSON 侦探。它会检查一个字符串是否为有效的 JSON。

SELECT ISJSON('{"name": "Charlie", "age": 40}') AS IsValidJSON;

结果：

IsValidJSON
-----------
1

返回值为 1 表示“是的，这是有效的 JSON”，而 0 表示“不，这不是有效的 JSON”。

5. JSON_OBJECT()

这个函数就像一个 JSON 工厂。它从你的 SQL 数据创建 JSON 对象。

SELECT JSON_OBJECT('name': 'Dana', 'age': 28) AS PersonJSON;

结果：

PersonJSON
----------
{"name":"Dana","age":28}

这是将你的常规 SQL 数据转换为 JSON 格式的一个很好的方式。

将所有内容结合起来

现在我们已经认识了我们的 JSON 函数，让我们看看如何在现实世界的场景中使用它们。想象一下我们在管理之前提到的那个在线书店。

-- 创建一个用于存储书籍信息的表
CREATE TABLE Books (
BookID INT PRIMARY KEY,
BookInfo NVARCHAR(MAX)
);

-- 插入一些示例数据
INSERT INTO Books (BookID, BookInfo)
VALUES
(1, '{"title": "SQL 基础", "author": "约翰· Doe", "year": 2020, "genres": ["技术", "教育"]}'),
(2, '{"title": "JSON 与你", "author": "简·史密斯", "year": 2021, "genres": ["技术"]}');

-- 获取书籍标题的查询
SELECT
BookID,
JSON_VALUE(BookInfo, '$.title') AS Title
FROM Books;

-- 获取所有类型的查询
SELECT
BookID,
JSON_QUERY(BookInfo, '$.genres') AS Genres
FROM Books;

-- 更新书籍的年份
UPDATE Books
SET BookInfo = JSON_MODIFY(BookInfo, '$.year', 2023)
WHERE BookID = 1;

-- 向书籍添加一个新的类型
UPDATE Books
SET BookInfo = JSON_MODIFY(
BookInfo,
'$.genres',
JSON_QUERY(
CONCAT(
'[',
SUBSTRING(JSON_QUERY(BookInfo, '$.genres'), 2, LEN(JSON_QUERY(BookInfo, '$.genres')) - 2),
',"编程"]'
)
)
)
WHERE BookID = 2;

-- 检查 BookInfo 是否为有效的 JSON
SELECT
BookID,
ISJSON(BookInfo) AS IsValidJSON
FROM Books;

就这样！我们已经创建了一个表，插入 JSON 数据，查询特定的值，更新我们的 JSON，甚至向 JSON 数组添加了新的信息。

结论

恭喜你！你已经迈出了进入 SQL JSON 函数世界的第一步。这些强大的工具允许我们在 SQL 数据库中处理灵活、嵌套的数据结构。在你继续旅程的过程中，你会发现更多利用这些函数创建健壮、灵活数据库解决方案的方式。

记住，掌握这些概念的关键是练习。所以不要害怕用你自己的 JSON 数据和 SQL 查询进行实验。快乐编码，愿你的数据库始终保持规范化！

函数	描述	示例
JSON_VALUE()	从 JSON 字符串中提取标量值	JSON_VALUE('{"name": "Alice", "age": 30}', '$.name')
JSON_QUERY()	从 JSON 字符串中提取对象或数组	JSON_QUERY('{"books": ["SQL 101", "JSON Mastery"]}', '$.books')
JSON_MODIFY()	在 JSON 字符串中修改值	JSON_MODIFY('{"name": "Bob", "age": 35}', '$.age', 36)
ISJSON()	检查字符串是否为有效的 JSON	ISJSON('{"name": "Charlie", "age": 40}')
JSON_OBJECT()	创建 JSON 对象	JSON_OBJECT('name': 'Dana', 'age': 28)

Credits: Image by storyset