从 2016 年开始SQL Server 终于原生支持了 JSON 处理而其中我用的最多的一个函数就是 OPENJSON。先说说它解决了什么问题在它出现之前数据库里想要查一个存了 JSON 的字段基本只能靠 LIKE 模糊匹配或者写一堆老长老长的字符串截取函数。遇到嵌套结构、数组字段那更痛苦逻辑复杂、性能还差。OPENJSON 做的事情很简单也很关键把一段 JSON 文本直接拆成一张关系表接下来你想 JOIN、GROUP BY、WHERE 过滤、聚合统计全都按普通表来处理。这篇文章适合谁读如果你是后端开发经常要存接口返回的 JSON、前端埋点日志或者要用存储过程接收批量数据OPENJSON 属于你迟早要掌握的核心技能。如果你是 DBA 或数据分析需要从 JSON 字段里抽字段做统计这篇文章也能帮你少踩很多坑。1. OPENJSON 到底解决了什么问题SQL Server 在 2016 版本之前对 JSON 的处理能力约等于零。数据库里存 JSON 文本很容易但用起来全是眼泪。我早期在某项目中接过一批前端埋点数据表结构很简单就是一张大宽表其中一个核心字段是payload里面存了各种事件参数可能有几十个键值对。产品想统计某个按钮的点击次数、不同端的上报比例当时只能写这种逻辑WHERE payload LIKE %button_id:12345%这种写法的坑很明显只要 JSON 里键的顺序变一下、空格变一下LIKE 就匹配不上了如果只看某个属性还要再配合 CHARINDEX 和 SUBSTRING 去截字符串代码维护起来特别痛苦性能就更不用提了全表扫描加字符串匹配数据量过百万基本就跑不动。OPENJSON 的出现等于给 SQL Server 装了一个“JSON 转表格”的转换器。你给它一段 JSON 字符串它返回一张可以直接查询的表。处理方式从“字符串匹配”升级成了“结构化查询”逻辑不再依赖路径顺序和空格格式性能也完全不一样。我整理了几个最典型的使用场景日志解析日志数据以 JSON 存入数据库后续按字段过滤、聚合、统计分析。接口响应入库调用第三方接口返回 JSON需要在入库前提取关键字段。配置管理系统配置以 JSON 保存运行时读取并解析出具体配置项。批量数据提交前端一次性提交多条明细后端存储过程用 OPENJSON 拆开后写入明细表。动态属性存储业务对象有很多可选属性全部塞进一个 JSON 字段查询时再按需解析。这几类场景在后端开发和数据分析工作中非常常见。掌握了 OPENJSON遇到这类需求就不需要再到处写可怕的字符串截取逻辑直接用一套标准方法解决。2. OPENJSON 的两种基本用法2.1 不写 WITH 子句直接查看 JSON 结构和内容先用最简单的形式看看 OPENJSON 长什么样。当你不写 WITH 子句时OPENJSON 会返回一个三列的结果集key、value、type。举个例子DECLARE json NVARCHAR(MAX) N[ { name: 张三, age: 30 }, { name: 李四, age: 25 } ]; SELECT * FROM OPENJSON(json);执行结果大致如下keyvaluetype0{name:张三,age:30}51{name:李四,age:25}5这三列的含义需要弄清楚key如果是 JSON 数组这里返回数组索引的字符串表示形式从 0 开始如果是 JSON 对象这里返回键名。value返回该键对应的值对于非标量类型数组或对象返回原始 JSON 文本注意此时它是 NVARCHAR(MAX) 类型。type返回该值的类型代码。0 表示 null1 表示字符串2 表示数字3 表示 true/false4 表示数组5 表示对象。如果不写 WITHOPENJSON 更多是给开发人员查看结构时用的或者处理那些连自己都不知道里面有什么结构的 JSON。生产环境更常用的其实是下一种写法显式指定字段类型和映射关系。2.2 配合 WITH 子句把 JSON 当成表来查大多数场景下我们关心的是 JSON 里的具体字段希望把它们映射成表里的列。这时候需要配合 WITH 子句使用。DECLARE json NVARCHAR(MAX) N[ { name: 张三, age: 30 }, { name: 李四, age: 25 } ]; SELECT * FROM OPENJSON(json) WITH ( name NVARCHAR(50) $.name, age INT $.age );执行结果nameage张三30李四25看起来很像把 JSON 数组“拍平”成了关系表。这里是核心用法所有复杂查询都是建立在这两行代码之上的。WITH 子句的语法是WITH ( 列名 数据类型 [JSON路径] [AS JSON] )有几个点需要说明列名可以不和 JSON 里的键名一致只要路径写对就行。如果省略 JSON 路径默认按照列名去匹配 JSON 对象的同名键。比如上面把列名写成name路径$.name其实可以省略。JSON 路径是大小写敏感的。JSON 里如果键名是Name路径写成$.name会返回 NULL这点我踩过不止一次。实际开发中我强烈建议即使列名和键名一致也把路径写全。理由有两个第一路径明确后代码的可读性更好第二当遇到特殊字符键名时提前把路径写对可以避免很多诡异的 NULL 问题。2.3 path 参数只解析 JSON 的某个子树OPENJSON 的完整签名是OPENJSON( jsonExpression [ , path ] [ , inputType ] )其中path可以让我们只解析 JSON 的某个子树避免先拆外层再逐层处理。比如这段 JSON{ user: { id: 100, profile: { nickname: 小明, level: 8 } }, orders: [ { orderId: A001, amount: 99 }, { orderId: A002, amount: 188 } ] }如果我们只关心 user.profile 里的信息SELECT * FROM OPENJSON(json, $.user.profile) WITH ( nickname NVARCHAR(50) $.nickname, level INT $.level );这样写的好处是逻辑清晰不需要先把整个 JSON 拆开再去定位子节点。如果 JSON 结构很庞大只拆需要的子树性能也会好一些。3. 路径表达式和类型映射最容易被忽略的细节3.1 JSON 路径怎么写OPENJSON 使用的是简化的 JSONPath 语法。日常用到的就下面这些路径含义$整个 JSON 的根节点$.name根节点下名为 name 的属性$.a.b.c多层嵌套属性$[0]数组的第一个元素$.items[2]items 数组的第三个元素$.items[*]items 数组的全部元素$.first name键名包含空格或特殊字符时用双引号包裹有一点要特别注意OPENJSON 的路径表达式不支持过滤条件。虽然 JSONPath 标准里有$.items[?(.price 100)]这样的过滤写法但 SQL Server 的 OPENJSON 不支持。遇到需要过滤数组元素的场景正确做法是先拆成行再在 WHERE 子句里过滤。另外路径模式分为 lax 和 strict 两种。默认是 lax 模式路径不存在时返回 NULL不会报错。如果在路径前加 strict 关键字比如strict $.user.name路径不存在时直接报错。查错调试时可以临时用 strict 模式但生产环境我建议保持默认 lax避免一条数据路径缺失导致整个查询失败。3.2 嵌套 JSON 怎么继续拆OPENJSON 一层只能拆一层。如果 JSON 里还有嵌套数组或嵌套对象需要在对应列后面加上AS JSON表示这个列的值整体是 JSON 文本取出来后才能继续套 OPENJSON。场景举例[ { orderId: A001, items: [ { sku: P001, qty: 2 }, { sku: P002, qty: 1 } ] }, { orderId: A002, items: [ { sku: P003, qty: 5 } ] } ]要把订单和明细都拆出来需要两层 OPENJSONSELECT o.orderId, item.sku, item.qty FROM OPENJSON(json) WITH ( orderId NVARCHAR(50) $.orderId, items NVARCHAR(MAX) $.items AS JSON ) AS o CROSS APPLY OPENJSON(o.items) WITH ( sku NVARCHAR(50) $.sku, qty INT $.qty ) AS item;这里的重点在AS JSON。如果 items 列不加 AS JSONOPENJSON 会把 items 数组当成一个字符串返回并且由于类型是 NVARCHAR(MAX)后续 CROSS APPLY OPENJSON 解析时就会因为传入了非法 JSON 而导致错误或空值。我见过不少刚接触 OPENJSON 的朋友在这里卡了很久。他们以为只要 WITH 子句里写了 nested 和路径就能直接得到嵌套的子表数据。实际上 OPENJSON 不是这样工作的它不做递归展开每个层级都要显式地拆一层。3.3 类型映射表WITH 子句里指定列的数据类型时SQL Server 会尝试把 JSON 值转换为目标类型。有一个常见误区是JSON 里的数字看起来是整数但实际可能超过 INT 范围或者带有小数。下面这张表可以作为参考SQL Server 列类型可接受的 JSON 类型说明INT / BIGINT数字type2超出范围会转换失败DECIMAL / FLOAT数字type2推荐用于金额、分数BITtrue/falsetype3不能用字符串 true 替代DATETIME / DATE字符串type1必须是可解析的日期格式NVARCHAR(MAX)任意类型标量转字符串数组/对象返回 JSON 文本UNIQUEIDENTIFIER字符串type1需要标准 GUID 格式有一个细节值得注意当 OPENJSON 转换失败时它不会直接报错而是把该字段置为 NULL。这意味着如果某条数据里 age 字段写成了 abc你把它定义为 INT最后查出来的结果里 age 是 NULL而不是报错提示。这在某些数据分析场景下会掩盖数据质量问题所以如果你对数据质量要求比较高可以结合源 JSON 的原始值做对比检查。补充一个实用技巧需要保留数组或对象原始值时把列类型定义为 NVARCHAR(MAX) 并加上 AS JSON这样既能拿到完整 JSON 片段又不影响后续处理。这种策略在“先拆主干、再拆枝叶”的两层方案中非常常用。4. 实操解析从日志表中提取统计字段4.1 场景描述某业务系统的操作日志表结构如下LogID日志编号LogTime记录时间EventType事件类型PayloadJSON 格式的详细数据Payload 示例{ user_id: 12345, source: mobile, device: Android, action: click, target: button_order, duration: 3.2, location: { province: 浙江, city: 杭州 } }现在要做两个统计第一不同来源source的日志数量第二各城市的用户操作次数。4.2 先拆主干字段单层级解析直接用 CROSS APPLY 把 OPENJSON 和主表关联起来SELECT L.LogID, J.user_id, J.source, J.device, J.action, J.target, J.duration FROM dbo.LogTable AS L CROSS APPLY OPENJSON(L.Payload) WITH ( user_id INT $.user_id, source NVARCHAR(50) $.source, device NVARCHAR(50) $.device, action NVARCHAR(50) $.action, target NVARCHAR(50) $.target, duration FLOAT $.duration ) AS J WHERE L.LogTime 2025-01-01 AND L.LogTime 2025-02-01;为什么用 CROSS APPLY 而不是 JOIN因为 CROSS APPLY 可以对每一行主表数据执行一次表值函数起到的效果是“把每行的 JSON 字段拆开并横向扩展”这正是 OPENJSON 的用法。配合 WHERE 条件先按 LogTime 圈定数据范围可以避免解析所有历史日志。4.3 继续拆嵌套对象location 是一个嵌套对象。如果也想拿到省和城市可以在外层 WITH 里将 location 定义为 NVARCHAR(MAX)并加上 AS JSON然后再套一次 OPENJSONSELECT J.user_id, J.source, Loc.province, Loc.city FROM dbo.LogTable AS L CROSS APPLY OPENJSON(L.Payload) WITH ( user_id INT $.user_id, source NVARCHAR(50) $.source, location NVARCHAR(MAX) $.location AS JSON ) AS J CROSS APPLY OPENJSON(J.location) WITH ( province NVARCHAR(50) $.province, city NVARCHAR(50) $.city ) AS Loc WHERE L.LogTime 2025-01-01 AND L.LogTime 2025-02-01;第一次 OPENJSON 把外层字段拆开location 保持为 JSON 文本第二次 OPENJSON 再把这个 JSON 文本解析成省和城市两列。两个 CROSS APPLY 顺序执行逻辑上非常清晰。4.4 聚合统计有了拆好的明细行后面的统计就简单了SELECT J.source, COUNT(*) AS cnt FROM dbo.LogTable AS L CROSS APPLY OPENJSON(L.Payload) WITH ( source NVARCHAR(50) $.source ) AS J WHERE L.LogTime 2025-01-01 AND L.LogTime 2025-02-01 GROUP BY J.source ORDER BY cnt DESC;这段 SQL 的重点在于OPENJSON 的结果集可以当作普通表使用。JOIN、GROUP BY、ORDER BY、窗口函数全都支持。这也是它价值最大的地方——解析完直接进入结构化查询体系。4.5 性能优化建议在实际项目中我建议注意以下几点尽量避免在大表的每一行上都执行 OPENJSON先把数据范围圈定到足够小。哪怕 JSON 解析本身不慢量级上去了压力也会很大。如果某个 JSON 字段被你反复查询可以考虑在写入时提前抽出核心字段做成独立列。分析查询如果频繁用同一个 JSON 路径可以先把数据解析到临时表再对临时表做后续操作。一次性解析、多次查询比每次都重复解析要高效得多。当 OPENJSON 解析出来的中间结果需要多次引用时临时表或表变量比嵌套 CTE 更可控。这里我多说一句很多人以为 OPENJSON 慢是因为函数本身效率低其实很多时候慢在“重复解析”。如果每条记录要被聚合、被过滤、被排序好几轮最优做法是一次性拆到临时表然后所有后续查询都基于临时表进行。5. 常见问题与排查技巧5.1 常见报错和异常速查表现象原因解决办法提示“JSON 文本格式不正确”传入的字符串不是合法 JSON先用 ISJSON 判断再决定是否解析查询结果全是 NULL路径写错或键名大小写不一致检查路径写法特殊键名用双引号包裹嵌套数组解析不出数据列未加 AS JSON将对应列定义为 NVARCHAR(MAX) 并加 AS JSON类型转换后值变成 NULLJSON 值与目标类型不匹配检查数据格式改用兼容的类型数据库版本支持但函数不存在数据库兼容级别低于 130修改数据库兼容级别解析速度越来越慢数据量大或没加过滤条件缩小数据范围先拆到临时表再分析数组拆出来后顺序乱了OPENJSON 不保证结果顺序如果需要顺序可以用 key 列排序5.2 类型转换失败不报错的问题这是我用 OPENJSON 时记忆最深的一个点。WITH 子句中定义age INT如果 JSON 里某个对象的 age 是字符串30其实 SQL Server 会自动尝试转换。但如果 JSON 里某个值是三十结果就是 NULL而且查询不会报错。这个问题在数据量大的时候特别隐蔽。你会看到统计结果莫名其妙少了数据排查半天才发现是类型转换失败导致部分行被置为 NULL。我的建议是在做关键统计之前先跑一次数据体检把原始 JSON 值和解析出的值做对比找出差异。比如SELECT COUNT(*) AS total_rows, COUNT(J.age) AS parsed_rows, COUNT(CASE WHEN ISJSON(Payload) 1 THEN 1 END) AS valid_json_rows FROM dbo.LogTable AS L CROSS APPLY OPENJSON(L.Payload) WITH ( age INT $.age ) AS J;这里 COUNT(J.age) 会自动忽略 NULL通过对比 total_rows 和 parsed_rows 的差值就能发现类型转换失败的记录数量。5.3 空字符串和 null 的区别JSON 里的 null 和字符串 null 含义完全不同。null 在 OPENJSON 解析时对应 SQL 的 NULL字符串 null 则是一个包含 4 个字符的普通字符串。如果需要把两者区分建议在 WITH 子句里同时输出原始 JSON 原文避免信息丢失。5.4 数组内对象属性缺失遇到数组里部分对象缺失某个键的情况解析结果为 NULL 是正常现象不是错误。比如订单数组里大部分订单有discount字段少部分没有那解析结果里 discount 列自然会出现 NULL。这属于正常业务数据分布聚合时请注意使用 ISNULL 或 COALESCE 处理避免统计口径出错。5.5 兼容级别和版本限制这一点容易忽略。OPENJSON 虽然在 SQL Server 2016 中已经可用但要求数据库的兼容级别至少是 130否则即使实例版本足够新也无法使用该函数。检查兼容级别的语句SELECT compatibility_level FROM sys.databases WHERE name DB_NAME();如果发现兼容级别是 100 或 120执行下面的语句调整注意评估对现有应用的影响ALTER DATABASE [YourDatabase] SET COMPATIBILITY_LEVEL 130;另外SQL Server 2016、2017、2019、2022 的 OPENJSON 函数和语法保持一致使用上没有明显差异这算是微软做得比较好的地方学习成本低跨版本迁移基本不用改代码。6. 扩展思路OPENJSON 与存储设计6.1 和 FOR JSON 配合使用OPENJSON 负责把 JSON 转成表而 FOR JSON 负责把表转成 JSON两者可以形成一个完整闭环。比如接口需要返回某些数据可以直接在 SQL 里用 FOR JSON 生成 JSONSELECT id, name, age FROM dbo.UserProfile WHERE age 18 FOR JSON AUTO;前端或服务端拿到这个结果后如果需要进一步统计聚合又可以通过 OPENJSON 拆开分析。整个流程不需要中间件做额外的 JSON 处理全程数据库内部解决。6.2 什么时候适合用 JSON 字段存储用 OPENJSON 解析数据的前提是数据以 JSON 形式存在关系表的某个字段中。这引出另一个问题什么时候应该用 JSON 存储我的经验是动态属性多且属性集合经常变化。属性只用于展示或低频查询不涉及高频强关联操作。数据来自外部接口结构不完全可控。多数据源异构格式统一以 JSON 落地。不适合用 JSON 的场景需要和其他表频繁 JOIN 并做复杂运算的属性。需要唯一约束、外键约束的核心业务字段。统计分析价值高、查询频率高的核心指标。简单来说动态扩展性和灵活性优先的场景适合 JSON强一致和高性能关联场景请用规范化的列。OPENJSON 是给 JSON 存储兜底的工具但不应该成为把所有业务数据都塞进 JSON 字段的借口。6.3 JSON 字段的写入端注意事项如果 JSON 是应用层写入的建议在写入前做校验。SQL Server 端可以使用 ISJSON 约束字段定义时还可以指定为ALTER TABLE dbo.LogTable ADD CONSTRAINT CK_Log_Payload_IsJSON CHECK (ISJSON(Payload) 1);这样非法 JSON 就直接在数据库层面被拦截不给后续解析留隐患。如果允许某些行没有 JSON 内容可以增加允许 NULL 的逻辑约束写成CHECK (Payload IS NULL OR ISJSON(Payload) 1);我在实际项目中加上这个约束后解析时遇到的诡异错误明显减少。6.4 对编写存储过程的建议OPENJSON 最常见的生产用途之一是在存储过程中接收前端批量提交的数据。前端传过来的是一个 JSON 数组存储过程用 OPENJSON 拆开逐行写入明细表。这种方式比逐条调用数据库接口效率高很多。一个典型的写法框架是CREATE PROCEDURE dbo.SubmitOrder orderHeader NVARCHAR(MAX), orderItems NVARCHAR(MAX) AS BEGIN SET NOCOUNT ON; DECLARE orderId INT; INSERT INTO dbo.Orders (CustomerId, TotalAmount, Status) SELECT customer_id, total_amount, pending FROM OPENJSON(orderHeader) WITH ( customer_id INT $.customer_id, total_amount DECIMAL(12,2) $.total_amount ); SET orderId SCOPE_IDENTITY(); INSERT INTO dbo.OrderItems (OrderId, Sku, Quantity, Price) SELECT orderId, sku, quantity, price FROM OPENJSON(orderItems) WITH ( sku NVARCHAR(50) $.sku, quantity INT $.quantity, price DECIMAL(12,2) $.price ); END这里要注意orderItems 是一个 JSON 数组OPENJSON 拆开后可以直接多行插入。这种方式非常适合接口层的批量数据接收既减少了网络往返次数又保持了 SQL 层的可维护性。个人使用体会我用 OPENJSON 处理过的数据没有一百亿也有几十亿行了总体最大的体会是这个函数并不复杂但细节容易出问题。路径大小写、AS JSON 的用法、类型转换的静默失败、兼容级别限制这些坑每一个我都真实踩过所以特意把它们都记下来。如果让我给一条最实用的建议那就是生产环境里用 OPENJSON 拆完数据后先落到临时表再做后续聚合和计算。这个习惯让我至少少写了上百次重复解析的逻辑也让查询性能稳定了很多。另外一个值得投入的时间点是在表设计阶段就考虑好 JSON 字段的约束和校验。数据源干净了下游解析就顺了。别等到数据已经堆了几千万行才发现里面混进了非法 JSON那时候的处理成本会高出好几个量级。OPENJSON 是个越用越顺手的工具但用好它的前提是对它的边界条件足够熟悉。希望这篇笔记能让你少走一些弯路。
