简介：一份面向 SQL 开发与数据库运维人员的实操型文档，核心讲解如何利用 SQL Server 自动将表数据转换为 JSON 字符串，并支持分页、排序与动态表名，特别适合需要在接口层直接输出 JSON、或进行系统间数据交换的场景。包内仅有 1 个 docx 文档，压缩包大小约 30KB，篇幅虽短但代码密度高，可直接对照复制。目前已有 1141 人浏览学习。文档先说明 JSON 基础格式，再给出完整变量声明与动态 SQL 拼接示例，覆盖通过 SYS.SYSCOLUMNS 获取表结构、使用 WITH paging 与 ROW_NUMBER 实现分页、以及 EXEC 执行拼接语句返回 JSON 的完整流程；同时提供了把 JSON 写入数据表和前端 AJAX 调用的示范代码，能帮助读者快速搭建一套数据库直接输出 JSON 的自动化方案。
1. SQL自动生成JSON数据：从一句SELECT到可直接交付的接口文档
别再把JSON生成这件事交给后端程序去循环拼字符串了。SQL Server的FOR JSON、MySQL的JSON_OBJECT、PostgreSQL的json_build_object，都能让你在数据库里直接把关系表转成嵌套JSON，一条SQL出去，前端直接能消费。这篇笔记聚焦SQL Server的FOR JSON PATH和OPENJSON这对组合，讲透怎么生成、怎么改结构、怎么处理坑，最后给一个可复用的存储过程模板，让你把“自动生成JSON”真正落地成日常工具。
适合谁：被接口文档逼疯的后端、要快速给前端喂数据的DBA、以及所有不想再写“拼接JSON字符串”这种烂代码的人。读完你能直接抄作业，也能知道哪些场景下这方案会翻车。
2. FOR JSON语法拆解：PATH模式与INCLUDE_NULL_VALUES的取舍
2.1 为什么是FOR JSON而不是手动拼接字符串
手动拼JSON的痛，拼过的人都知道：字段漏了逗号、引号没转义、日期格式飘了、数字被加了引号、NULL被拼成"null"字符串而不是JSON的null……这些玄学问题在数据量上来之后几乎每个接口都要排查一遍。而FOR JSON是数据库引擎层直接生成合法JSON文本，语法层面就杜绝了“引号没转义”这类低级错误。
FOR JSON有两种模式：AUTO和PATH。AUTO模式根据SELECT的JOIN顺序自动推断嵌套结构，写起来最省事，但控制力弱——列名直接映射、嵌套层级不可控、别名处理很别扭。PATH模式用列别名里的点号.来指定路径，比如[order.id]这种写法，可以精确控制输出结构，还能生成数组嵌套。我基本只用PATH，AUTO只适合快速看个雏形。
2.2 PATH模式的最小可跑例子
先建一张测试表，别嫌麻烦，后面所有坑都在这张表上演示：
CREATE TABLE dbo.orders ( order_id INT PRIMARY KEY, customer_name NVARCHAR(50), order_date DATETIME2, total_amount DECIMAL(10,2), remark NVARCHAR(200) ); INSERT INTO dbo.orders VALUES (1, N'张三', '2024-01-15 10:30:00', 299.00, N'加急'), (2, N'李四', NULL, 1599.50, NULL), (3, N'王五', '2024-01-16 09:00:00', 88.00, N'');用FOR JSON PATH生成每个人的订单对象，别加数组方括号：
SELECT order_id AS 'order.id', customer_name AS 'order.customer', order_date AS 'order.date', total_amount AS 'order.total', remark AS 'order.remark' FROM dbo.orders WHERE order_id = 1 FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;输出结果是：
{"order":{"id":1,"customer":"张三","date":"2024-01-15T10:30:00","total":299.00,"remark":"加急"}}AS 'order.id'这种带点号的别名，意思是把order作为JSON对象名，id是该对象里的属性。WITHOUT_ARRAY_WRAPPER去掉外层的方括号，让输出直接是一个对象而不是数组。拿到这个字符串，你就能塞进接口响应体、写到消息队列、或者直接喂给前端渲染。
2.3 ROOT根节点与格式化输出
接口标准里常常要求最外层有个统一字段名，比如{"data": [...]}或{"result": {...}}。FOR JSON的ROOT关键字就是干这个的，不用你在外面包一层：
SELECT order_id AS 'id', customer_name AS 'customer' FROM dbo.orders WHERE order_id = 1 FOR JSON PATH, ROOT('data');输出：
{"data":[{"id":1,"customer":"张三"}]}注意ROOT不会去掉数组包裹——FOR JSON默认输出数组，ROOT只是在外层再包一个对象。想输出单个对象就用WITHOUT_ARRAY_WRAPPER和ROOT组合。我习惯这样写存储过程模板：默认带ROOT，返回{"code":0,"data":...}这种结构，前端拿到直接能用。
2.4 INCLUDE_NULL_VALUES的关键决策
SQL Server默认行为是：列值为NULL时，JSON里直接省略该字段。这对大部分接口是合理的——少传字段比传null更省流量，前端处理也更友好。但有些场景你必须显式输出null：比如对接的第三方系统要求字段必须存在、或者下游在做JSON Schema校验。
加上INCLUDE_NULL_VALUES，让NULL字段也出现在JSON里：
SELECT order_id AS 'id', remark AS 'remark' FROM dbo.orders WHERE order_id = 2 FOR JSON PATH, INCLUDE_NULL_VALUES;输出：
[{"id":2,"remark":null}]而不加时输出：
[{"id":2}]这里有个容易翻车的细节：空字符串''和NULL是两回事。上面测试表里order_id = 3的remark是空字符串，FOR JSON会把空字符串保留，输出"remark":""。所以别以为空值都会被省略，NULL才被省，空串不会被省。如果你的业务里空串和NULL语义一样，建议统一UPDATE成NULL再生成，否则下游会发现字段“消失了又出现”的诡异现象。
3. 处理嵌套与数组：用子查询或CROSS APPLY构造一对一、一对多结构
3.1 为什么单表SELECT不够用
真实接口十有八九是主子表结构——一个订单一堆明细。FOR JSON PATH处理不了多行子记录的自动嵌套，你必须手动用子查询或CROSS APPLY把子表聚合进去。这也是新手最容易卡住的地方：明明JOIN了子表，出来的JSON却是数组里每个元素都重复了主表字段，而不是嵌套结构。
3.2 用子查询构造数组嵌套
再加一张明细表：
CREATE TABLE dbo.order_items ( item_id INT PRIMARY KEY, order_id INT, product_name NVARCHAR(50), quantity INT, price DECIMAL(10,2) ); INSERT INTO dbo.order_items VALUES (1, 1, N'机械键盘', 1, 199.00), (2, 1, N'鼠标垫', 2, 50.00), (3, 2, N'显示器', 1, 1599.50);在FOR JSON PATH里去查子表：
SELECT o.order_id AS 'id', o.customer_name AS 'customer', ( SELECT i.product_name AS 'name', i.quantity AS 'qty', i.price AS 'price' FROM dbo.order_items i WHERE i.order_id = o.order_id FOR JSON PATH ) AS 'items' FROM dbo.orders o WHERE o.order_id = 1 FOR JSON PATH;输出结果：
[{"id":1,"customer":"张三","items":[{"name":"机械键盘","qty":1,"price":199.00},{"name":"鼠标垫","qty":2,"price":50.00}]}]子查询里的FOR JSON返回的是一个JSON字符串，外层再把它作为一个字段值塞进去。这里有个关键约束：子查询只能返回一行结果（一个字符串），否则报错“FOR JSON只能生成单个JSON对象”。因为整个子查询会被当做一个标量值处理，所以必须保证子查询结果集只有一行——用聚合函数或者让WHERE条件唯一匹配。
3.3 用CROSS APPLY处理复杂嵌套
子查询写法遇到“需要根据主表行做计算再生成子数组”时很别扭。CROSS APPLY配合FOR JSON能更清晰地表达“对每一行生成一段子JSON”：
SELECT o.order_id AS 'id', o.customer_name AS 'customer', items_json.items_value FROM dbo.orders o CROSS APPLY ( SELECT ( SELECT i.product_name AS 'name', i.quantity AS 'qty', i.price AS 'price' FROM dbo.order_items i WHERE i.order_id = o.order_id FOR JSON PATH ) AS items_value ) items_json WHERE o.order_id = 1 FOR JSON PATH;逻辑上和子查询一样，但好处是items_json这个派生表可以被多次引用，而且能在这层对items_value做ISNULL处理——子查询结果为空时，FOR JSON的输出是null字符串，你可以在APPLY层用ISNULL(items_value, '[]')替换成空数组，输出"items":[]而不是"items":null。这在接口契约上更友好，也是我推荐CROSS APPLY的原因。
4. OPENJSON反向解析：把JSON当表查，JSON_VALUE与路径表达式实战
4.1 为什么不直接程序解析
生成JSON只是第一步，数据库里收到的JSON总得查、改、校验。OPENJSON是SQL Server 2016+内置的表值函数，能把JSON文本转成行列结构，直接参与JOIN、WHERE、聚合。配合JSON_VALUE取标量、JSON_QUERY取对象或数组，你可以在不写一行C#/Java代码的情况下，完成“JSON进来→SQL处理→JSON出去”的闭环。
4.2 OPENJSON解析简单数组
DECLARE @json NVARCHAR(MAX) = N'[ {"id":1,"name":"张三"}, {"id":2,"name":"李四"} ]'; SELECT * FROM OPENJSON(@json) WITH ( id INT '$.id', name NVARCHAR(50) '$.name' );输出两行数据。WITH子句定义了输出列的JSON路径映射，同时指定了SQL Server数据类型——方便你直接CAST、JOIN或插入表。不加WITH时，OPENJSON只返回三列：key、value、type，适合先探查结构。
4.3 JSON_VALUE取出深层次字段
有时候你不需要展开整个数组，只想取深层路径上的某个值：
DECLARE @json NVARCHAR(MAX) = N'{ "order": { "id": 1001, "customer": { "name": "张三", "phone": "13800138000" } } }'; SELECT JSON_VALUE(@json, '$.order.id') AS order_id, JSON_VALUE(@json, '$.order.customer.name') AS customer_name;路径表达式里$代表JSON根，点号后跟字段名。这个例子取出1001和张三。JSON_VALUE只能取标量（字符串、数字、布尔），遇到对象或数组类型它返回NULL，这要用JSON_QUERY：
SELECT JSON_QUERY(@json, '$.order.customer') AS customer_object;返回的是{"name":"张三","phone":"13800138000"}这个JSON片段，注意是带着花括号的文本。新手最容易搞混：JSON_VALUE取叶子节点，JSON_QUERY取子树。取错了返回NULL，排查半天发现是函数用错了。
4.4 与FOR JSON配合使用
OPENJSON最大的价值是可以对接口传进来的JSON做过滤再返回：
DECLARE @payload NVARCHAR(MAX) = N'[ {"product":"键盘","qty":2}, {"product":"鼠标","qty":0} ]'; SELECT product, qty FROM OPENJSON(@payload) WITH ( product NVARCHAR(50) '$.product', qty INT '$.qty' ) WHERE qty > 0 FOR JSON PATH, ROOT('valid_items');这里先用OPENJSON展开数组，过滤掉qty为0的行，再用FOR JSON重新打包。整个过程不落临时表，内存里就完成。接口层收到无效数据、直接在数据库里拦截清洗，省掉一段程序逻辑。
5. 自动生成JSON的5个常见坑与排查手册
5.1 中文字符变成\uXXXX乱码
现象：生成的JSON里中文变成\u5f20\u4e09。原因：SQL Server的FOR JSON对非ASCII字符默认做Unicode转义，这是合法JSON但大多数前端看着头大。解决：别在SQL层面纠结，用JSON_VALUE或程序端Newtonsoft.Json再序列化一次自然还原；或者接受它，因为JSON标准本就允许。如果一定要明文中文，可以在查询时把FOR JSON PATH换成FOR JSON PATH, INCLUDE_NULL_VALUES——不行，这个没用。真正能做到明文的是把字段强制转换成NVARCHAR并提前把Unicode转义干掉——也没有正规的内置参数。实际项目里我的做法是让前端接受\u转义，因为JSON.parse会自动还原。别在这上面浪费时间。
5.2 datetime类型输出带T和毫秒，格式不对
现象："2024-01-15T10:30:00"不是你要的"2024-01-15 10:30:00"。原因：FOR JSON对datetime2默认输出ISO 8601格式，T是标准分隔符。解决：在SELECT里先转换格式：
SELECT order_id AS 'id', CONVERT(VARCHAR(19), order_date, 120) AS 'date' FROM dbo.orders WHERE order_id = 1 FOR JSON PATH;输出变成"date":"2024-01-15 10:30:00"。记住：要在SQL层做格式化，别指望JSON消费者理解你的T。
5.3 数字被加上引号变成字符串
现象："total":"299.00"，前端拿到字符串导致计算报错。原因：FOR JSON内部把numeric类型转成了NVARCHAR，这在某些CAST场景下会发生。解决：检查你的SELECT里是不是把数字列包了ISNULL或CONVERT，把列类型显式写成CAST(total_amount AS DECIMAL(10,2))，FOR JSON对明确数值类型输出不带引号。另外DECIMAL的精度会保留两位小数，这是正常的。
5.4 子查询FOR JSON为空时报错或者出现null
现象：主表有行，但子表没明细，FOR JSON返回null，下游解析崩盘。原因：子查询结果集为空，FOR JSON返回NULL文本。解决：用ISNULL(子查询FOR JSON, '[]')把空值替换成空数组。上面CROSS APPLY一节已经演示过，子查询的FOR JSON结果包一层ISNULL，全部搞定。
5.5 嵌套层级太深或循环引用导致生成失败
现象：三层以上嵌套、且每层都有数组，FOR JSON报“达到最大嵌套级别”或性能极差。原因：FOR JSON递归解析每层，层级越深开销越大，默认允许100层，一般够用。真正的问题往往是你在SELECT里写了一个返回多行的子查询FOR JSON，导致“FOR JSON只能生成一个JSON对象”错误。解决：先检查子查询的WHERE条件是否唯一；如果确实要多个嵌套数组，就用多层CROSS APPLY逐层生成，每层都包ISNULL兜底，别让引擎一次解析过多层级。
6. 存储过程模板：把"SQL自动生成JSON数据"固化成一键调用
最后给一个可以直接抄的存储过程模板，把FOR JSON和OPENJSON组合封装起来。设计思路是：传入一个查询SQL文本作为数据源，过程动态执行并输出JSON字符串。这听起来有点黑匣子的味道，但配合良好的参数约束反而比写死SELECT更实用。
CREATE PROCEDURE dbo.GenerateJsonFromQuery @QuerySql NVARCHAR(MAX), @ParamDefinitions NVARCHAR(MAX) = NULL AS BEGIN SET NOCOUNT ON; DECLARE @JsonResult NVARCHAR(MAX); DECLARE @ParmDefinition NVARCHAR(MAX) = @ParamDefinitions; BEGIN TRY SET @JsonResult = N''; -- 动态执行传入的SQL，并强制FOR JSON包装 DECLARE @FullSql NVARCHAR(MAX) = N' SELECT JSON_QUERY(''' + @QuerySql + N''' FOR JSON PATH, WITHOUT_ARRAY_WRAPPER) AS json_result'; -- 使用sp_executesql执行，支持参数定义，避免SQL注入 EXEC sp_executesql @FullSql, @ParmDefinition; END TRY BEGIN CATCH -- 返回错误信息便于排查 SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message, ERROR_LINE() AS error_line; END CATCH END;这个模板实际用来生成单个JSON对象，JSON_QUERY包在外层是为了让结果加上引号成为合法的JSON文本。如果传入的查询是SELECT order_id AS 'id' FROM orders WHERE order_id = @id，过程返回{"id":1}这样的对象。要输出数组可以把WITHOUT_ARRAY_WRAPPER去掉，默认就是数组。
参数@ParamDefinitions是必要的——动态SQL里如果直接拼接值，会被SQL注入。正确用法是传N'@id INT'作为参数定义，然后在调用时：
EXEC dbo.GenerateJsonFromQuery @QuerySql = N'SELECT order_id AS ''id'' FROM dbo.orders WHERE order_id = @id', @ParamDefinitions = N'@id INT';你还可以扩展这个模板：把结果直接插入一个表变量，或者用OPENJSON把返回文本重新解析做校验。实际项目里我习惯在存储过程里再包一层SELECT @JsonResult AS data FOR JSON PATH, ROOT('response')，让接口层直接拿标准结构。
最后说一句血泪经验：别让FOR JSON生成的文本沦为调试打印物，要让它成为接口数据的唯一源头，写进日志、对接消息队列、直接喂给BI报表——这样你才能体会到“SQL自动生成JSON”不是炫技，而是把一条流水线从程序层挪到了数据层，省下的每一分钟都是实实在在的交付时间。希望帮到你。
本文还有配套的精品资源，点击获取
