sql-json-functions

SQL - JSON 函数

现代数据库内置了强大的功能，支持存储和查询 JSON (JavaScript Object Notation) 数据。这使您能够将无模式数据的灵活性与关系型数据库的健壮性结合起来。SQL 提供了一套函数，可以直接在查询中创建、查询和操作 JSON。

1. 为何在 SQL 数据库中使用 JSON？

• 灵活的模式：存储半结构化数据，如产品属性、用户偏好或设置，而无需为每个可能的属性创建单独的列。
• API 集成：轻松存储和处理来自 Web API 的 JSON 响应。
• 层次数据：自然地表示传统平面表格难以建模的嵌套数据结构。

不同数据库处理 JSON 存储的方式不同。PostgreSQL 有高度优化的二进制 JSONB 类型，而 MySQL 有 JSON 类型。SQL Server 将 JSON 存储为 NVARCHAR，但提供了函数来操作它，就像它是一个原生类型一样。

2. 创建 JSON 数据

您可以使用标准函数从关系数据构建 JSON 文本。

JSON_OBJECT

从键值对列表创建 JSON 对象。

-- 语法: JSON_OBJECT('key1', value1, 'key2', value2, ...)
SELECT JSON_OBJECT('id', 1, 'name', 'Laptop', 'in_stock', 1) AS ProductInfo;

JSON_ARRAY

从值列表创建 JSON 数组。

-- 语法: JSON_ARRAY(value1, value2, ...)
SELECT JSON_ARRAY('Laptops', 'Monitors', 'Keyboards') AS Categories;

3. 查询 JSON 数据

要从 JSON 列中提取数据，您可以使用“JSON 路径表达式”，这是一个指定数据位置的字符串（例如 $.name、$.tags[0]）。$ 表示 JSON 文档的根。

JSON_VALUE - 提取标量值

使用 JSON_VALUE 提取单个、简单的值，如字符串、数字或布尔值。它非常适合在 WHERE 子句或 SELECT 列表中使用。

-- 语法: JSON_VALUE(json_column, 'json_path')
SELECT JSON_VALUE(ProductData, '$.name') AS ProductName FROM Products;

JSON_QUERY - 提取对象或数组

当您想提取嵌套的 JSON 对象或数组，而不是简单值时，请使用 JSON_QUERY。

-- 语法: JSON_QUERY(json_column, 'json_path')
SELECT JSON_QUERY(ProductData, '$.specs') AS Specifications FROM Products;

4. 修改 JSON 数据

您还可以对 JSON 文档进行原地更新，而无需重写整个字符串。

JSON_MODIFY (SQL Server)

更新值、添加新属性或删除属性。

-- 语法: JSON_MODIFY(json_column, 'json_path', new_value)

-- 更新产品的价格
UPDATE Products
SET ProductData = JSON_MODIFY(ProductData, '$.price', 999.99)
WHERE ProductID = 123;

MySQL 使用 JSON_SET、JSON_INSERT 和 JSON_REMOVE 等函数实现类似目的。

5. 一个完整的实践示例

让我们模拟一个带有 JSON 列的 Products（产品）表，用于灵活的属性。

步骤 1：创建表（SQL Server 示例）。

CREATE TABLE Products (
    ProductID INT PRIMARY KEY IDENTITY,
    SKU VARCHAR(50) UNIQUE NOT NULL,
    Attributes NVARCHAR(MAX) -- Stores JSON data
    CONSTRAINT CHK_Attributes_Is_JSON CHECK (ISJSON(Attributes) > 0)
);

步骤 2：插入带有 JSON 属性的产品。

INSERT INTO Products (SKU, Attributes)
VALUES ('LP-BLK-15',
'{
  "name": "ProBook 15",
  "color": "Black",
  "specs": {
    "ram_gb": 16,
    "storage_gb": 512
  },
  "tags": ["business", "15-inch"]
}');

步骤 3：查询具有特定属性的产品。

SELECT SKU, Attributes
FROM Products
WHERE JSON_VALUE(Attributes, '$.color') = 'Black';

步骤 4：提取嵌套值。

SELECT SKU, JSON_VALUE(Attributes, '$.specs.ram_gb') AS RAM_GB FROM Products;

步骤 5：向 JSON 添加新属性。

UPDATE Products
SET Attributes = JSON_MODIFY(Attributes, '$.on_sale', 'true')
WHERE SKU = 'LP-BLK-15';

6. 性能和索引考虑

在 JSON 字符串内部查询可能比查询已索引的关系列慢。为了优化性能：

• 使用原生二进制类型：如果您的 RDBMS 支持（例如 PostgreSQL 中的 JSONB），请使用它。它对于查询来说要快得多。
• 在 JSON 属性上创建索引：某些数据库允许您在 JSON 内部的值上创建索引。在 SQL Server 中，您可以基于 JSON_VALUE 表达式创建计算列，然后为该列创建索引。在 PostgreSQL 中，您可以直接在 JSONB 列上创建 GIN 索引。
• 提升常用键：如果您发现经常根据 JSON 内部的特定键（例如我们示例中的 color）进行过滤，请考虑将其“提升”为表中独立的常规索引列，以获得最大性能。