JSON格式在业务系统里越来越常见，比如商品的扩展属性、订单的附加信息、接口回调的报文，往往都是以JSON字符串的形式直接存在表字段里。问题来了：这些数据存进去容易，取出来却麻烦。如果每次都把整张表拉到应用层再解析，数据量一大性能就崩了。其实在SQL Server 2016及以后的版本中，数据库引擎原生支持JSON解析，JSON_VALUE、JSON_QUERY、OPENJSON三个函数配合使用，完全可以把过滤和提取动作下沉到SQL层面完成。
一、准备工作：建表并插入测试数据
先建一张订单表，其中的items字段存JSON字符串，模拟最常见的业务场景：一个订单包含多个商品明细，每个明细有商品名、数量和单价。这样的结构在电商系统中非常典型。
-- 创建订单表，items字段存储JSON数组
CREATE TABLE Orders (
OrderID INT PRIMARY KEY IDENTITY(1,1),
CustomerName NVARCHAR(50),
OrderDate DATETIME DEFAULT GETDATE(),
Items NVARCHAR(MAX) -- 存放JSON字符串
);
-- 插入测试数据
INSERT INTO Orders (CustomerName, Items) VALUES
(N'张三', N'[{"name":"机械键盘","qty":2,"price":299.00},{"name":"鼠标垫","qty":1,"price":39.90}]'),
(N'李四', N'[{"name":"显示器","qty":1,"price":1599.00}]'),
(N'王五', N'[{"name":"U盘","qty":5,"price":49.00},{"name":"硬盘","qty":1,"price":459.00},{"name":"数据线","qty":3,"price":19.90}]');数据插好之后，items字段就是一段纯文本。要验证它是不是合法JSON，可以用ISJSON函数判断，返回1表示格式合法，这在数据清洗阶段很有用。
-- 检查JSON格式是否合法 SELECT OrderID, ISJSON(Items) AS IsValid FROM Orders;
二、JSON_VALUE：提取标量值的利器
JSON_VALUE的作用是从JSON字符串中取出一个标量值，也就是字符串、数字、布尔值这类单一值，返回类型是NVARCHAR(4000)。它的第一个参数是JSON表达式，第二个参数是路径，路径必须以$开头，$代表整个JSON文档，然后用点号逐层下钻。
来看一个稍微复杂一点的例子。假设每个订单还有一个info字段存对象类型的JSON，里面嵌套了收货地址：
SELECT OrderID,
JSON_VALUE(Info, '$.city') AS City,
JSON_VALUE(Info, '$.address.street') AS Street
FROM Orders
WHERE JSON_VALUE(Info, '$.city') = N'北京';这里有两点需要特别注意。第一，如果路径指向的不是标量而是对象或数组，JSON_VALUE会返回NULL而不是报错，这是它和JSON_QUERY最大的区别。第二，在WHERE子句里使用JSON_VALUE时，由于需要对每一行做函数计算，无法利用索引，全表扫描不可避免。如果JSON字段的某个属性要频繁作为过滤条件，可以用计算列加索引的方式优化，后面会讲到。
另外还有一个很容易踩的坑：JSON_VALUE默认只能返回4000个字符以内的值。如果标量值超长，会直接返回NULL。解决办法是在路径模式上做调整，写法是在路径前面加JSON_VALUE(expression, 'lax $.xxx')，但更通用的做法是改用OPENJSON配合WITH子句来明确指定返回类型。
三、OPENJSON：把JSON数组拆成行集
JSON_VALUE只能取单值，遇到数组就无能为力了。而实际业务里，恰恰是数组才是主流。比如订单表里的items就是一个商品数组，想统计每个订单买了多少件商品，或者想按商品名做筛选，就必须把数组“炸开”成一行一行的结构化数据，这正是OPENJSON的主场。
OPENJSON本质上是一个行集函数，它的返回结果可以像表一样参与JOIN、WHERE、GROUP BY。基础用法如下：
-- 把items数组拆成行，key是下标，value是元素内容
SELECT o.OrderID,
o.CustomerName,
json.[key] AS ItemIndex,
json.[value] AS ItemJson
FROM Orders o
CROSS APPLY OPENJSON(o.Items) AS json;这里的CROSS APPLY是关键。它对Orders表的每一行，把该行的items数组交给OPENJSON拆解，拆出来的每个元素生成一行。默认情况下返回三列：key列是数组下标（从0开始），value列是元素的原始JSON文本，type列是元素的数据类型编号。如果不加APPLY而直接写FROM OPENJSON(...)，那只适合JSON写死或在变量里的场景。
用WITH子句定义结构化输出
默认的value列还是JSON文本，取具体字段还得再套一层JSON_VALUE。更优雅的方式是用WITH子句直接声明返回的列名和类型，一步到位：
-- 使用WITH子句把数组元素映射成强类型列
SELECT o.OrderID,
o.CustomerName,
item.name AS ProductName,
item.qty AS Quantity,
item.price AS UnitPrice,
item.qty * item.price AS Amount
FROM Orders o
CROSS APPLY OPENJSON(o.Items)
WITH (
name NVARCHAR(100) '$.name',
qty INT '$.qty',
price DECIMAL(10,2) '$.price'
) AS item;这个写法的好处是类型转换在数据库内完成， qty直接是INT，price直接是DECIMAL，可以直接参与算术运算。上面的查询能算出每个订单中每个商品的金额，如果要按商品汇总销量，在外面套一层GROUP BY即可：
-- 按商品统计销量和销售额
SELECT item.name AS ProductName,
SUM(item.qty) AS TotalQty,
SUM(item.qty * item.price) AS TotalAmount
FROM Orders o
CROSS APPLY OPENJSON(o.Items)
WITH (
name NVARCHAR(100) '$.name',
qty INT '$.qty',
price DECIMAL(10,2) '$.price'
) AS item
GROUP BY item.name
ORDER BY TotalAmount DESC;四、常见问题与性能优化建议
第一类常见报错是路径写法错误。JSON路径必须以$开头，数组下标用方括号，比如$.items[0].name表示取items数组第一个元素的name。路径中的属性名如果包含特殊字符如空格或点号，需要用双引号包裹，写成$."属性 名"的形式。
第二类问题是找不到值返回NULL却误以为是数据错误。默认的lax模式下，路径不存在就返回NULL，不抛异常；如果希望路径必须存在，可以声明strict模式，此时路径无效会直接抛错。建议在数据来源不可控时用lax模式，配合ISNULL做兜底处理。
性能方面，最重要的一条优化手段是计算列加索引。假如经常按$.city过滤订单，可以在表上加一个持久化计算列，然后在该列上建索引：
-- 添加持久化计算列并建立索引 ALTER TABLE Orders ADD City AS CAST(JSON_VALUE(Info, '$.city') AS NVARCHAR(50)) PERSISTED; CREATE INDEX IX_Orders_City ON Orders(City); -- 之后的查询可以走索引 SELECT * FROM Orders WHERE City = N'北京';
对于超大JSON文档，还可以考虑用COMPRESS和DECOMPRESS做压缩存储，但要注意DECOMPRESS返回的是varbinary，需要再转成NVARCHAR才能交给JSON函数处理。最后提醒一句，JSON函数虽然好用，但它终究不如原生列高效。如果某个JSON属性已经成为高频查询条件，最根本的优化还是把它提取出来做成独立的实体列，JSON字段更适合存放那些结构多变、查询频率低的扩展信息。
JSON解析JSON_VALUEOPENJSON修改时间：2026-09-06 16:22:41