简介一份围绕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本质是在查询阶段完成“表到树”的映射列名变成键行变成数组元素主子表关系变成嵌套对象。三种主流引擎都内置了这种序列化能力不需要在应用层再拼一遍。引擎核心函数/子句行转对象多行成数组适用场景SQL ServerFOR JSON PATH / AUTO自动自动报表快照、接口输出MySQLJSON_OBJECT() JSON_ARRAYAGG()JSON_OBJECTJSON_ARRAYAGG 聚合单表/分组导出PostgreSQLjson_build_object() json_agg()json_build_objectjson_agg 聚合复杂嵌套查询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( server127.0.0.1, useretl_user, password******, databaseSalesDB, charsetutf8, ) cursor conn.cursor(as_dictTrue) cursor.execute(open(order_snapshot.sql, encodingutf-8).read()) rows cursor.fetchall() with open(orders.json, w, encodingutf-8) as f: json.dump(rows, f, ensure_asciiFalse, indent2, defaultconvert)cursor里那句as_dictTrue让每一行以字典返回列名变成键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_asciiFalse否则所有中文都会变成\u5f20\u4e09这种转义序列文件能解析但人没法看排查问题时要瞪着眼睛数Unicode。写成False之后中文明文落盘文件头UTF-8无BOM常见解析器都能直接读。Windows下有些老程序要求带BOM的UTF-8如果下游明确要求再改成encodingutf-8-sig不要默认加。第二是缩进indent2方便人工排查但会明显增大文件体积。对给程序消费的JSONindent直接省掉默认的紧凑输出能小一半以上。如果同事要打开看格式再用jq美化别在生产文件里留一堆空格。第三是行数与内容大小fetchall()适合百万行以内的导出超过这个量级内存会顶不住。大结果集改成fetchmany分批写文件思路是手写左方括号每批循环把行用json.dump写进去并用逗号分隔最后补右方括号。这种方式内存占用恒定不会因为表数据涨了而把调度任务跑挂。4.3 定时调度与sqlcmd导出自动化的两条路脚本就位后定时执行是最后一步。Linux上用cron0 2 * * * cd /opt/etl /usr/bin/python3 export_orders.py /var/log/etl/export.log 21这条任务会在每天凌晨两点于指定目录下运行日志单独落文件。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遇到它直接报错很多人图省事写defaultstr结果Decimal、date、datetime全部被转成字符串整个JSON的类型体系就崩了。解决明确设计类型映射金额类字段在SQL层CAST AS FLOAT再输出或在Python端只对Decimal转float别用defaultstr一把梭。另一条规则是给下游JS系统导大整数ID时主动转字符串因为JS的Number超过2^53会丢精度SQL生成得再对下游算错等于白导。类型这件事没有万能解只能在导出脚本顶部写清楚每个字段的目标类型。5.3 日期格式一格一个样统一成“无时区本地时间”还是“UTC”现象同一批文件里SQL Server的FOR JSON输出带T和毫秒2026-05-01T10:00:00.0000000MySQL的JSON_ARRAYAGG输出空格分隔2026-05-01 10:00:00Python兜底落盘的又是另一种isoformat下游排序比较直接乱套。原因每个引擎的JSON编码器各自决定日期序列化格式没有统一标准。解决在SQL层把日期字段格式写死。SQL Server用CONVERT(varchar(23), OrderDate, 120)得到yyyy-MM-dd HH:mm:ssMySQL用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 Norders.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生成这事后端看似简单翻车全在下游消费的一瞬间。多留一份校验少一次大半夜的紧急回滚。希望帮到你。本文还有配套的精品资源点击获取