企业官网建设流程全解析

简介：一份围绕SQL Server自动生成JSON数据的实操指南，面向需要把数据库查询结果直接转成JSON供前端调用的开发人员，适合数据库开发与接口联调场景。文档先说明JSON键值对的基本形式，再重点演示如何声明@TableName、@sql、@CurPageFirstRow、@CurPageLastRow、@OrderByColumn等变量，利用SYS.SYSCOLUMNS获取表结构、结合WITH子句和ROW_NUMBER()函数生成分页序列，再借助ISNULL判断、动态SQL与EXEC执行，把查询结果自动拼装为JSON字符串；同时补充通过INSERT INTO将JSON存入数据表，以及前端用AJAX请求API接口读取JSON的示例代码。压缩包共1个docx文件，大小约30KB，内容紧凑，方便离线查阅。目前已有一千一百四十一人浏览学习。读者按照文中变量声明、SQL拼接逻辑和EXEC执行流程，即可在本地SQL Server环境中改造复用，减少手工拼JSON的重复工作，提升分页数据接口的开发效率。其中动态SQL片段涉及SELECT语句动态拼接、ISNULL初始化与EXEC调用，可帮助读者举一反三。

1. SQL自动生成JSON数据，不只是“SQL转JSON”这么简单

接口对接和数据快照经常一起催着要JSON：订单明细要有嵌套结构、每天凌晨要换一份新文件、下游还只认固定字段。最开始我习惯在后端代码里循环拼JSON字符串，直到一次字段转义漏了反斜杠，下游解析直接崩掉，才意识到这条路越走越重。

真正省事的做法，是让SQL查询本身把结果序列化成JSON——数据库算完，应用层只负责落盘。标题里“SQL自动生成JSON数据”说的就是这条流水线：一条带FOR JSON的查询，加上定时任务，替代手工拼串的接口脚本。它解决的是从关系表到JSON文件的自动产出问题，不是手工跑一条SQL看一眼结果。

适合后端开发、数据工程师和运维。负责过报表导出、第三方接口对接的人，看完可以直接照抄。

2. 数据库原生JSON输出：三种引擎的序列化入口与最小跑通命令

2.1 三种SQL引擎的JSON输出函数与选型对比

关系型结果集是“行×列”的平面表，JSON是“对象/数组”的树。让SQL自动生成JSON，本质是在查询阶段完成“表到树”的映射：列名变成键，行变成数组元素，主子表关系变成嵌套对象。三种主流引擎都内置了这种序列化能力，不需要在应用层再拼一遍。

MySQL和PostgreSQL按“函数组合”工作：SELECT每一行调用JSON_OBJECT生成一个对象，再包一层JSON_ARRAYAGG聚合成数组；SQL Server用一条FOR JSON子句直接包住整个SELECT，最接近“自动”两个字。MySQL的最小写法是：

SELECT JSON_ARRAYAGG( JSON_OBJECT('id', id, 'name', name, 'price', price) ) AS json_result FROM products;

这里JSON_OBJECT的键必须显式写成字符串字面量，列的别名在这里不起作用；参数顺序决定键的输出顺序。JSON_ARRAYAGG要求配合GROUP BY使用，不带GROUP BY时把整表聚合成一个数组。字段很多时手写JSON_OBJECT容易漏键，常见做法是先SELECT * FROM products LIMIT 1看一下工具输出，再用程序生成这段函数列表。

PostgreSQL的最小写法类似：

SELECT json_agg( json_build_object('id', id, 'name', name, 'price', price) ) AS json_result FROM products;

json_build_object的键同样要写成字符串，行对象由json_agg收集。PostgreSQL还提供to_jsonb(products)把整行直接转成对象，键名就是列名，配合jsonb_agg可以少写很多字段。注意json和jsonb两个类型：jsonb会重排键序并去重，导出给下游时键顺序不确定，在意字段顺序就用json_agg而不是jsonb_agg。

2.2 用FOR JSON PATH跑通最小命令与参数拆解

以SQL Server为例，最小可用的生成命令长这样：

SELECT TOP 3 ProductID, ProductName, UnitPrice FROM Products ORDER BY ProductID FOR JSON PATH;

执行后返回一个JSON数组，数组里每个元素对应一行：列名成为键，int和decimal保持数字类型，nvarchar输出字符串，数组默认不带根节点。输出本身是nvarchar(max)类型，可以直接写入某张json列，也可以被sqlcmd重定向成文件。

FOR JSON PATH的关键参数：

PATH按SELECT列表手工控制层级，列名里带点号会被解析成嵌套；AUTO根据FROM和JOIN关系自动决定嵌套层级；ROOT('别名')在最外层包一个命名对象，适合接口协议要求有顶层节点的情况；INCLUDE_NULL_VALUES让值为NULL的列也输出键，默认是直接丢弃；WITHOUT_ARRAY_WRAPPER去掉外层方括号，多行时会产出多个拼接对象，不是合法JSON，只建议在“确定只返回一行”的配置类查询里用。

一次实际导出里，我一般固定写成FOR JSON PATH, ROOT('data')，这样下游不管是一行还是多行，读取时都从data里拿数组，结构是稳定的。WITHOUT_ARRAY_WRAPPER这个参数踩过一次坑：某次导出用户配置表，两行配置各成了一个顶层对象，下游json.loads直接报错，因为这个文件里有两个不连续的JSON对象。从那以后，只有确定单行结果的场景我才用它。

2.3 为什么不在应用层循环拼JSON

同样的数据，在Python里写for循环拼字符串也能出JSON。但三个问题让这条路越来越难走：第一，类型保持要靠手工判断，int列可能被写成"123"字符串，日期格式每段代码一个样；第二，数据里的特殊字符、换行、引号都要自己转义，少转一处下游就解析失败；第三，多一次应用层与数据库之间的往返，字段一多循环里的代码量并不比SQL少。

数据库原生序列化把这三个问题挡在查询层：类型由列定义决定，转义由JSON编码器完成，嵌套由子查询控制。应用层只需要把结果原样落盘。“SQL自动生成JSON”的自动，指的就是这种从查询到文件一气呵成的做法，而不是写脚本再把查询结果加工一遍。简单到只有几十行的静态配置，用代码拼未必不可；但只要涉及多层嵌套或定期刷新，让SQL直接输出JSON的收益是立竿见影的。

3. 把主子表拼成一个JSON树：嵌套子查询与PATH层级控制

3.1 订单+明细嵌套JSON的构造SQL

最常见的业务形状是主子表：一个订单对应多条明细，导出的JSON里订单对象下要挂一个items数组。用FOR JSON PATH构造这一步是标准写法：

SELECT o.OrderID, o.CustomerName, ( SELECT d.Sku, d.Quantity, d.Price FROM OrderDetails d WHERE d.OrderID = o.OrderID FOR JSON PATH ) AS Items FROM Orders o WHERE o.OrderID = 10248 FOR JSON PATH, ROOT('order');

这里有两个FOR JSON：内层负责把明细行聚合成数组，外层负责把订单行聚合成数组。SQL Server有一个关键行为：外层遇到来自子查询的FOR JSON结果时，会把它作为JSON片段直接嵌入Items键对应的值，而不是当成普通字符串加转义。所以输出的Items是一个真正的数组，不是一串\"开头的转义文本。

内层子查询注意两点：不要加ROOT，加了会把明细数组包成{"Details": [...]}，Items的类型从数组变成对象，下游遍历逻辑就要改；内层SELECT的列名规则和外层一样，想控制明细节点的嵌套就在内层列名里用点号。执行后典型输出为{"order":[{"OrderID":10248,"CustomerName":"...","Items":[{"Sku":"A01","Quantity":2,"Price":10.5}]}]}。

3.2 PATH的层级控制：列名中的点号决定树形结构

FOR JSON PATH之所以叫PATH，是因为列名里的点号会被解析成层级路径。比如：

SELECT ProductID, ProductName AS "Product.Name", UnitPrice AS "Product.Price" FROM Products FOR JSON PATH;

结果里ProductName和UnitPrice会自动收进同一个Product对象，形成{"ProductID":1,"Product":{"Name":"...","Price":10}}。前缀相同的点号列会自动合并成一个对象，不需要额外写嵌套子查询。这个特性在导出接口协议时很实用：接口希望把业务字段按命名空间分组，SQL里改个别名即可，应用层不用动。

合并行为有一个盲区：假设两张表各有一个字段，别名恰好写成Customer.Name和Customer.Nickname，它们会合并进同一个Customer对象，键名变成Name和Nickname。如果这不是你想要的层级，要么改别名避免共享前缀，要么老老实实用子查询JSON_OBJECT。另一个习惯是，别在生成JSON的大查询里写SELECT *，因为新加列会无预警地改变JSON结构，下游看到多字段不一定崩，但看到缺字段一定崩。

3.3 FOR JSON AUTO的自动嵌套与三个盲区

FOR JSON AUTO是另一条路：它根据FROM子句和JOIN关系自动决定嵌套层级。订单连接明细的查询，如果写成FOR JSON AUTO，引擎会把被连接的表自动放进子数组，不需要手写内层子查询。做原型和快速看结构时很省事。

实际落到生产，我很少用AUTO，原因是三个盲区：第一，列的选择和顺序由引擎决定，你没法只挑需要的字段而丢掉大字段；第二，嵌套层级的触发点是JOIN顺序，稍不注意就多包一层或少包一层；第三，AUTO对同名列的处理策略不好预测，两个表都有Remark时，后者会被改名，下游拿到突然变化的键名很难排查。而FOR JSON PATH的手工层级虽然代码长一点，但每个键的来历都在SQL里写得明明白白，出了问题对着SELECT列表查即可。

一句话选型：探索阶段用AUTO看整体结构，交付给下游的脚本用PATH，固定每个字段的路径和类型。第4章讲怎么把查询变成定时落盘的JSON文件。

4. 自动化落地：把查询结果定时写成JSON文件

4.1 Python直连数据库并落盘的最小脚本

FOR JSON跑通后，离“自动生成JSON数据”还差一步：定时把查询结果写进文件。用得最多的落地方式是一个Python脚本，连库执行查询，把结果直接json.dump到指定路径。最小骨架如下：

import json import datetime from decimal import Decimal import pymssql def convert(value): if isinstance(value, Decimal): return float(value) if isinstance(value, (datetime.date, datetime.datetime)): return value.isoformat() return value conn = pymssql.connect( server="127.0.0.1", user="etl_user", password="******", database="SalesDB", charset="utf8", ) cursor = conn.cursor(as_dict=True) cursor.execute(open("order_snapshot.sql", encoding="utf-8").read()) rows = cursor.fetchall() with open("orders.json", "w", encoding="utf-8") as f: json.dump(rows, f, ensure_ascii=False, indent=2, default=convert)

cursor里那句as_dict=True让每一行以字典返回，列名变成键，SELECT顺序就是键顺序；Python 3.7之后字典保序，所以JSON里的字段顺序和SQL里一致，不会随机漂移。convert函数是给json.dump的default参数用的，兜底处理Decimal和日期类型；不加它，碰到Decimal会直接抛“Object of type Decimal is not JSON serializable”。

pymssql以user/password/database这种连接方式为主，MySQL用pymysql或mysql-connector时连接参数几乎一样，只是驱动包的导入名不同。生产环境里不要把密码写死在脚本里，常见做法是从环境变量或密钥文件读取，脚本本身只负责执行。

4.2 写文件的三个细节：编码、中文转义、压缩

第一次写这个脚本最容易翻车的是三个文件细节。第一是编码：json.dump必须带ensure_ascii=False，否则所有中文都会变成\u5f20\u4e09这种转义序列，文件能解析但人没法看，排查问题时要瞪着眼睛数Unicode。写成False之后中文明文落盘，文件头UTF-8无BOM，常见解析器都能直接读。Windows下有些老程序要求带BOM的UTF-8，如果下游明确要求，再改成encoding="utf-8-sig"，不要默认加。

第二是缩进：indent=2方便人工排查，但会明显增大文件体积。对给程序消费的JSON，indent直接省掉，默认的紧凑输出能小一半以上。如果同事要打开看格式，再用jq美化，别在生产文件里留一堆空格。

第三是行数与内容大小：fetchall()适合百万行以内的导出，超过这个量级内存会顶不住。大结果集改成fetchmany分批写文件，思路是手写左方括号，每批循环把行用json.dump写进去并用逗号分隔，最后补右方括号。这种方式内存占用恒定，不会因为表数据涨了而把调度任务跑挂。

4.3 定时调度与sqlcmd导出：自动化的两条路

脚本就位后，定时执行是最后一步。Linux上用cron：

0 2 * * * cd /opt/etl && /usr/bin/python3 export_orders.py >> /var/log/etl/export.log 2>&1

这条任务会在每天凌晨两点于指定目录下运行，日志单独落文件。Windows计划任务的操作路径相同：建一个“每日”触发器，程序填python.exe，参数填脚本绝对路径，起始目录填脚本目录。注意起始目录不填的话，脚本里open("order_snapshot.sql")这类相对路径会找不到文件，这是计划任务最常见的报错来源。

不想写Python时，另一个常见做法是用sqlcmd把FOR JSON的结果直接重定向到文件：

sqlcmd -S . -d SalesDB -E -y 0 -Q "SET NOCOUNT ON; SELECT ... FOR JSON PATH" -o orders.json

-y 0一定要带，表示不限制可变长度类型的显示宽度，否则默认256字符的宽度会把超长JSON在中间截断或折行。SET NOCOUNT ON用来抑制“行数受影响”的消息，避免它混进JSON文件。sqlcmd这条路更适合临时导一次或服务器上没有Python环境的场景；需要校验、转换、定制文件名的场景，还是走Python更可靠。无论哪条路，调度任务运行时最好把日期写进文件名（orders_20260501.json），避免当天失败时把昨天的文件覆盖掉，这是给自动任务留的后悔药。

5. SQL生成JSON的避坑清单：五个高频翻车现场

5.1 NULL字段失踪：INCLUDE_NULL_VALUES的两面性

现象：导出文件里某个订单缺少remark字段，因为源表里该字段是NULL。下游代码拿obj.get("remark")取到None，业务方却坚称“这个订单有备注，是导出丢了”。

原因：SQL Server的FOR JSON默认不输出NULL列，整列值为NULL时这个键直接消失；MySQL和PostgreSQL的JSON函数则会把NULL写成null值。同一套数据在不同引擎导出后结构不一致，下游基于“键在不在”的判断就会出问题。

解决：SQL Server端在FOR JSON后追加INCLUDE_NULL_VALUES，让NULL列输出为"remark":null，键名结构保持固定。代价是文件体积变大，且下游如果一直用“键在不在”判断空值，加了NULL后反而会误判。所以这个参数一旦定了就不要中途增减，否则下游要跟着改判断逻辑。

5.2 金额变成字符串：DECIMAL类型在Python端和MySQL端的漂移

现象：导出的JSON里金额字段是"123.45"而不是123.45，下游Java用BigDecimal或者Go用float64解析时行为完全不同。

原因往往不在SQL，而在Python端：pymysql和pymssql默认把DECIMAL列返回为Decimal对象，json.dump遇到它直接报错，很多人图省事写default=str，结果Decimal、date、datetime全部被转成字符串，整个JSON的类型体系就崩了。

解决：明确设计类型映射，金额类字段在SQL层CAST AS FLOAT再输出，或在Python端只对Decimal转float，别用default=str一把梭。另一条规则是给下游JS系统导大整数ID时主动转字符串，因为JS的Number超过2^53会丢精度，SQL生成得再对，下游算错等于白导。类型这件事没有万能解，只能在导出脚本顶部写清楚每个字段的目标类型。

5.3 日期格式一格一个样：统一成“无时区本地时间”还是“UTC”

现象：同一批文件里，SQL Server的FOR JSON输出带T和毫秒（2026-05-01T10:00:00.0000000），MySQL的JSON_ARRAYAGG输出空格分隔（2026-05-01 10:00:00），Python兜底落盘的又是另一种isoformat，下游排序比较直接乱套。

原因：每个引擎的JSON编码器各自决定日期序列化格式，没有统一标准。

解决：在SQL层把日期字段格式写死。SQL Server用CONVERT(varchar(23), OrderDate, 120)得到yyyy-MM-dd HH:mm:ss，MySQL用DATE_FORMAT(OrderDate, '%Y-%m-%d %H:%i:%s')，Python里则用strftime显式指定。格式统一后还要约定时区：如果源库存的是UTC时间，导出时字段名加_utc后缀，并在JSON里写死一个timezone字段，避免下游按本地时间理解差八小时的乌龙。日期这块越早约定成本越低，等下游开始消费了再改格式，每改一次都要通知所有对接方。

5.4 内层JSON被转义成字符串：嵌套被展平或变成纯文本

现象：订单明细没有变成Items数组，而是变成"I\":[{\"sku\":\"A01\"}]"这一长串带反斜杠的文本；或者Items键直接消失，明细字段散落在订单对象里。

原因有两种：一是内层子查询漏了FOR JSON，返回的是普通聚合结果；二是内层FOR JSON的列在传输中被截断或类型不对，外层没识别出它是合法JSON片段，于是按普通字符串输出。SQL Server对外层是否“自动嵌入”内层JSON，取决于内容是否由FOR JSON产生且完整可解析。

解决：内层子查询的FOR JSON PATH不要省略，并把结果列显式CAST AS nvarchar(max)，防止默认长度截断。排查时不要盯着控制台看，把生成结果保存成文件后搜\"，出现这个序列说明外层把它当字符串转义了，优先检查内层查询和列类型。嵌套段落的SQL乍一看没毛病，但类型不对就是不行，这一条是嵌套导出翻车率最高的地方。

5.5 大结果集导出：fetchall和sqlcmd默认宽度的两个坑

现象：一个500万行的导出任务，Python脚本跑到一半内存暴涨被杀；另一个用sqlcmd导出的JSON文件，json.loads解析时报错，错误位置在一行很长的订单备注中间。

原因：fetchall()把所有行一次性加载进内存，行数一涨就超过容器限制；sqlcmd默认把超长字段截断或折行显示，折行发生时会在JSON字符串里插入换行符，而JSON标准不允许字符串里有裸换行，文件自然坏掉。

解决：Python端把fetchall改成fetchmany分批写，内存占用基本恒定，脚本能在1GB内存的机器上跑完整个表；sqlcmd端加-y 0仍不保险，导出后马上用python -c "import json;print(json.load(open('orders.json')))"做一次加载校验，加载失败立刻重导，别等下游发现。真正常跑的大表导出，建议按主键区间分片，每个分片一个文件，最后按顺序合并，既控制单次SQL执行时间，也方便失败后只重跑坏掉的切片。

6. 生成结果不轻信：回读校验与增量刷新两个实用技巧

6.1 用OPENJSON/json.loads回读校验行数

生成JSON文件后第一件事永远是校验，不是直接丢给下游。最简单的方法是回读比对行数：Python脚本里先执行SELECT COUNT(*)得到源行数，再把JSON文件load回来数数组长度，两者不一致就直接抛错并终止调度，不生成当天文件。SQL Server 2016以后还能用OPENJSON把JSON读回表，和源库对比：

DECLARE @json NVARCHAR(MAX); SELECT @json = BulkColumn FROM OPENROWSET(BULK N'orders.json', SINGLE_CLOB) AS j; SELECT COUNT(*) AS row_count FROM OPENJSON(@json) WITH (OrderID INT '$.OrderID', CustomerName NVARCHAR(100) '$.CustomerName');

这里WITH里的路径是相对于数组元素的；如果文件外层用ROOT包过，就把OPENJSON第二个参数写成'$.data'再取元素。把上面查到的数量和源表COUNT比对，逐字段类型也能用WITH里的类型定义校验。行数对上只说明没丢行，不保证字段没错，但它是成本最低的守卫，能让大部分低级错误在发送前暴露。

6.2 增量刷新：别让定时任务每天导全表

“自动生成”跑一段时间后，全表导出的慢SQL会拖垮源库。增量刷新的正确姿势是维护一个last_export_time，下次只导updated_at大于它的数据。这里有个容易被忽略的父子表细节：订单和明细每天都在变，如果过滤条件写在明细表上，会漏掉“主表没更新但明细新增”的订单；如果只过滤订单主表，子查询里取该订单全部明细，不会漏也不会重。SQL骨架是：

SELECT o.OrderID, o.UpdatedAt, (SELECT Sku, Quantity FROM OrderDetails d WHERE d.OrderID = o.OrderID FOR JSON PATH) AS Items FROM Orders o WHERE o.UpdatedAt > @lastExportTime FOR JSON PATH;

子查询不过滤时间，明细变化由主表的UpdatedAt兜住；主表的UpdatedAt加索引，增量查询就不会演变成全表扫描。增量任务跑完后，把本次最大UpdatedAt写回控制文件，再更新调度里下次的@lastExportTime。

我自己跑这类导出有个习惯：每个脚本最前面固定一个schema_version字段，每次改键名或改类型，先改schema再跑一次校验脚本，校验通过才替换正式任务里的SQL，绝不直接改线上导出。JSON生成这事后端看似简单，翻车全在下游消费的一瞬间。多留一份校验，少一次大半夜的紧急回滚。希望帮到你。

本文还有配套的精品资源，点击获取