如何在SQL中处理JSON数据？JSON函数的实用指南

看不見的法師

发布时间：2025-09-07 11:49:01

756人浏览过

来源于php中文网

原创

掌握SQL中JSON函数的核心在于熟练使用提取、修改、构建和验证四类函数。首先，JSON_VALUE用于提取标量值，如通过$.name获取产品名称；JSON_QUERY则提取对象或数组，适用于获取$.specs完整信息。其次，JSON_MODIFY可更新、插入或删除JSON中的值，支持路径不存在时自动创建，而JSON_OBJECT和JSON_ARRAY可用于从关系数据构建JSON结构。为提升性能，应避免全表扫描，利用计算列或GIN/GiST索引加速查询；设计上宜将高频查询字段扁平化或独立为列；避免在JSON中存储大文本或二进制数据，改用外部引用；频繁修改场景下建议批量操作或重构模型。合理使用这些函数并结合索引与结构优化，能高效处理复杂JSON数据。

如何在sql中处理json数据？json函数的实用指南

在SQL中处理JSON数据，现在已经变得相当直观和强大，主要得益于现代数据库系统内置的各种JSON函数。这些函数允许我们像操作普通列一样，轻松地查询、提取、修改乃至构建JSON结构，极大地简化了原本复杂的字符串解析工作，让数据库能够更好地适应半结构化数据的存储和处理需求。

解决方案

要高效处理SQL中的JSON数据，核心在于掌握一系列专为JSON设计的功能函数。这些函数可以大致分为几类：提取数据、修改数据、构建数据以及验证数据。

1. 提取数据：

```
JSON_VALUE(json_string, json_path)
```
: 用于从JSON字符串中提取一个标量值（如数字、字符串、布尔值）。如果提取的是非标量值（如对象或数组），它会返回NULL。
```
JSON_QUERY(json_string, json_path)
```
: 用于从JSON字符串中提取一个JSON对象或JSON数组。如果提取的是标量值，它会返回NULL。

2. 修改数据：

```
JSON_MODIFY(json_string, json_path, new_value)
```
: 用于修改JSON字符串中指定路径的值。可以更新现有值、插入新键值对，甚至删除某个键。

3. 构建数据：

```
JSON_OBJECT(key1, value1, key2, value2, ...)
```
: 用于从键值对构建一个JSON对象。
```
JSON_ARRAY(value1, value2, value3, ...)
```
: 用于从一系列值构建一个JSON数组。

4. 验证数据：

```
ISJSON(json_string)
```
: 用于检查一个字符串是否是有效的JSON格式，返回1表示有效，0表示无效。

这些函数共同构成了在SQL环境中操作JSON数据的强大工具集，让我们能够直接在数据库层面处理半结构化数据，避免了将数据提取到应用程序层进行解析的性能开销和复杂性。

如何从复杂的JSON结构中精确提取特定数据？

坦白说，这可能是我们日常工作中遇到最多的场景。一个JSON字段里可能藏着多层嵌套的对象、数组，要从中精准捞出想要的信息，关键在于理解和正确使用JSON路径表达式。我个人觉得，一旦你掌握了JSON路径，就像打开了新世界的大门。

假设我们有一个名为

ProductDetails

的表，其中有一个

Metadata

列，存储着如下JSON数据：

{
  "name": "超级键盘",
  "specs": {
    "weight": "1.2kg",
    "color": ["black", "silver"],
    "dimensions": {
      "length": "45cm",
      "width": "15cm"
    }
  },
  "tags": ["gaming", "mechanical", "wireless"],
  "price": 129.99
}

现在，我们想提取产品名称、颜色列表中的第一个颜色以及键盘的长度。

提取产品名称 (标量值): 产品名称是顶层的一个简单字符串。我们用
```
JSON_VALUE
```
：
```
SELECT JSON_VALUE(Metadata, '$.name') AS ProductName
FROM ProductDetails;
```
这里的
```
$
```
代表JSON根对象，
```
.name
```
指向根对象下的
```
name
```
键。
提取颜色列表中的第一个颜色 (数组元素): 颜色是一个数组，我们想取第一个元素。数组索引从0开始。
```
SELECT JSON_VALUE(Metadata, '$.specs.color[0]') AS FirstColor
FROM ProductDetails;
```
```
$.specs.color
```
定位到颜色数组，
```
[0]
```
则选取数组的第一个元素。
提取键盘的长度 (嵌套对象中的标量值): 长度信息藏在
```
specs
```
对象里的
```
dimensions
```
对象里。
```
SELECT JSON_VALUE(Metadata, '$.specs.dimensions.length') AS KeyboardLength
FROM ProductDetails;
```
这种链式调用路径的方式，非常直观地反映了JSON的层级结构。

动态WEB网站中的PHP和MySQL：直观的QuickPro指南第2版
动态WEB网站中的PHP和MySQL详细反映实际程序的需求，仔细地探讨外部数据的验证(例如信用卡卡号的格式)、用户登录以及如何使用模板建立网页的标准外观。动态WEB网站中的PHP和MySQL的内容不仅仅是这些。书中还提到如何串联JavaScript与PHP让用户操作时更快、更方便。还有正确处理用户输入错误的方法，让网站看起来更专业。另外还引入大量来自PEAR外挂函数库的强大功能，对常用的、强大的包

下载
提取整个
```
specs
```
对象 (非标量值): 如果我需要
```
specs
```
里的所有信息，而不是某个具体的值，那就用
```
JSON_QUERY
```
：
```
SELECT JSON_QUERY(Metadata, '$.specs') AS ProductSpecs
FROM ProductDetails;
```
这会返回一个完整的JSON字符串，代表
```
specs
```
对象。理解
```
JSON_VALUE
```
和
```
JSON_QUERY
```
的区别至关重要：前者提取“叶子节点”的原始值，后者提取“分支节点”的JSON结构。搞混了它们，你可能会得到
```
NULL
```
或者不想要的结果。

在SQL中如何高效地修改或构建JSON数据？

在SQL中修改或构建JSON数据，我觉得这才是真正体现数据库处理半结构化数据灵活性的地方。以前，你可能需要先把JSON取出来，在应用层解析、修改，再序列化存回去，那过程简直是噩梦。现在，

JSON_MODIFY

、

JSON_OBJECT

和

JSON_ARRAY

简直是救星。

1. 修改现有JSON数据： 假设我们想把

超级键盘

的价格从

129.99

改为

139.99

，并添加一个

manufacturer

字段。

UPDATE ProductDetails
SET Metadata = JSON_MODIFY(Metadata, '$.price', 139.99);

-- 添加新字段
UPDATE ProductDetails
SET Metadata = JSON_MODIFY(Metadata, '$.manufacturer', 'KeyCorp');

JSON_MODIFY

的第三个参数可以是任何SQL表达式，数据库会根据其类型自动进行JSON序列化。如果路径不存在，它会尝试创建。例如，如果

$.manufacturer

不存在，它就会在根级别添加这个键值对。如果我想删除一个键，比如

tags

，我可以这样做：

UPDATE ProductDetails
SET Metadata = JSON_MODIFY(Metadata, '$.tags', NULL); -- 设置为NULL通常会删除该键

不过，不同数据库对

JSON_MODIFY

的

NULL

行为可能略有差异，有些数据库可能需要明确的

DELETE

选项或特定的函数（如PostgreSQL的

jsonb_set

结合

jsonb_strip_nulls

）。在SQL Server中，将值设为

NULL

确实会移除该键。

2. 构建新的JSON数据： 想象一下，我们有一些散列的列数据，比如

ProductId

ProductName

ProductPrice

，现在想把它们整合成一个JSON对象存起来。

SELECT ProductId,
       JSON_OBJECT('name', ProductName, 'price', ProductPrice, 'available', TRUE) AS ProductJson
FROM Products;

JSON_OBJECT

接受一系列键值对，非常适合从关系型数据构建JSON。

如果我们需要构建一个JSON数组，比如把多个标签组合起来：

SELECT ProductId,
       JSON_ARRAY('gaming', 'ergonomic', 'bluetooth') AS ProductTags
FROM Products;

这些构建函数在数据迁移、数据集成或者为API准备数据时特别有用。它们让我们可以直接在SQL层面对数据进行“塑形”，省去了很多中间环节。我发现用它们来生成一些简单的日志或配置JSON，简直不要太方便。

处理JSON数据时常见的性能陷阱和最佳实践是什么？

处理JSON数据，尤其是当数据量庞大时，性能问题总是绕不开的话题。我见过不少人因为不恰当的使用方式，导致JSON查询慢如蜗牛。这里有几个我总结的经验和需要注意的地方：

1. 避免全表扫描： 如果你经常需要根据JSON字段中的某个值进行过滤或排序，但又没有相应的索引，那么每次查询都可能导致全表扫描，这在大型表上是灾难性的。

最佳实践：使用JSON索引。 许多现代数据库都提供了JSON索引功能，这简直是性能的救星。

SQL Server: 支持在JSON路径上创建计算列，然后在这个计算列上创建索引。例如：

ALTER TABLE ProductDetails ADD ProductNameComputed AS JSON_VALUE(Metadata, '$.name');
CREATE INDEX IX_ProductName ON ProductDetails(ProductNameComputed);

这样，当你查询

WHERE JSON_VALUE(Metadata, '$.name') = '超级键盘'

时，优化器就可以使用这个索引了。

PostgreSQL: 提供了

GIN

(Generalized Inverted Index) 或

GiST

(Generalized Search Tree) 索引，可以直接在

jsonb

类型列上创建。例如：

CREATE INDEX idx_metadata_name ON ProductDetails ((Metadata->>'name')); -- for specific key
CREATE INDEX idx_metadata_gin ON ProductDetails USING GIN (Metadata); -- for full text search within JSON

这些索引能显著加速基于JSON内容的查询。

2. 谨慎使用复杂的JSON路径： 虽然JSON路径很强大，但过于复杂、层级过深的路径可能会增加解析开销。如果你发现某个深层路径的查询频率非常高，可以考虑将其提升到更高的层级，或者在设计JSON结构时尽量扁平化常用字段。

最佳实践：优化JSON结构设计。 对于经常需要查询的字段，考虑将其放在JSON结构的顶层，或者甚至将其提升为独立的列。虽然这可能牺牲一些JSON的灵活性，但对于性能敏感的查询来说，这种“非规范化”是值得的。

3. 避免在JSON中存储大量二进制数据或超大文本： JSON设计初衷是存储结构化文本数据。将图片、大型文档等二进制数据直接编码成Base64存储在JSON中，不仅会使JSON体积膨胀，增加存储和传输成本，还会导致解析效率低下。

最佳实践：外部存储和引用。 对于大型二进制数据或文本，更好的做法是将其存储在文件系统、Blob存储或单独的数据库表中，然后在JSON中只存储一个引用（如文件路径或ID）。

4. 频繁的JSON修改操作：