简介面向SQL Server开发人员的实战型文档聚焦数据库层自动生成JSON数据并供前端或业务系统调用。文档从JSON基本概念切入重点讲解利用SYS.SYSCOLUMNS系统视图动态读取表结构结合WITH子句与ROW_NUMBER()完成分页查询通过变量拼接SQL并使用EXEC动态执行最终输出带rn、total等字段的JSON字符串。文中还详细介绍了核心实现步骤包括表名、页码、排序列等变量的声明与赋值以及如何借助ISNULL处理拼接初始值同时说明用INSERT INTO将JSON结果写入数据表并给出前端AJAX调用API的示例覆盖生成、存储、调用完整链路。资源包为1个docx文档容量30KB内容精炼适合具备SQL基础、希望减少后端转换工作的开发者。该文档以Word格式交付便于复制代码与标注学习整体短小精悍按步骤展开即可上手验证。目前已有1141人学习浏览对SQL Server场景下的动态JSON输出具有直接参考价值。1. 让 SQL 直接吐出 JSON再把 JSON 自动写成 Word这条链路解决什么问题“SQL自动生成JSON数据.docx”这标题听起来像某个小工具的备注实际上它对应的是一个高频到令人头疼的交付场景数据在数据库里躺着业务方或者下游系统要的却是两种东西——JSON 格式的接口对接材料或者一份体面的 Word 报表。你不想在程序里手工拼接 JSON 字符串也不想每天把查询结果复制到 Excel 再粘进 Word。常见做法是把 SQL 当成数据出口直接让查询语句产出 JSON再接一个脚本自动生成 .docx。这套链路对做报表、数据交付、接口 mock 都非常省事适合熟悉 SQL 但不想为一个小需求维护整套后端代码的从业者。下面这套方案不依赖任何重型框架一条能出 JSON 的 SQL 加一个 Python 脚本就能把最繁琐的环节自动化。2. 用数据库原生能力自动生成 JSON三套主流写法和选型先明确一个原则能由数据库完成的转换不要拿到代码里做。SQL 直接生成 JSON 的好处是减少一次网络传输和序列化开销也能让数据库的查询计划器帮你处理排序、分组和嵌套关系。不同数据库的 JSON 函数语法差异很大但思路一致把查询结果集映射成 JSON 对象或 JSON 数组。下面分别给出 SQL Server、MySQL、PostgreSQL 的常用写法并说明各自适用场景。2.1 SQL Server 的 FOR JSON PATH表格转向量最省事的写法SQL Server 2016 起提供FOR JSON子句2017 后又补了FOR JSON PATH的增强SQL Server 2022 里依然沿用。它不需要记忆 JSON 构造函数直接把 SELECT 的结果集转成 JSON 数组语法上最接近“给 SQL 加一个输出格式”。一个最小可跑的查询长这样SELECT TOP (50) e.emp_id, e.emp_name, d.dept_name FROM dbo.employee AS e LEFT JOIN dbo.department AS d ON e.dept_id d.dept_id ORDER BY e.emp_id FOR JSON PATH, ROOT(employees), INCLUDE_NULL_VALUES;这段查询的关键点在FOR JSON PATH它告诉 SQL Server 把每一行结果转成一个 JSON 对象整个结果集转成 JSON 数组。ROOT(employees)在最外层包一个根节点下游解析时可以直接取.employees。INCLUDE_NULL_VALUES决定 NULL 字段是否保留在输出里默认情况下 NULL 字段会被整个省略后面踩坑部分会细说。如果想像样例数据那样控制每条记录的字段顺序PATH 模式下 SELECT 列表的列顺序就是 JSON 键的顺序。嵌套结构是 PATH 模式最实用的场景。假如员工表关联了多张技能标签你希望输出成skills: [...]这样的一对多数组可以用子查询配合FOR JSON PATH实现SELECT e.emp_id, e.emp_name, ( SELECT s.skill_name FROM dbo.emp_skill es JOIN dbo.skill s ON es.skill_id s.skill_id WHERE es.emp_id e.emp_id ORDER BY s.skill_name FOR JSON PATH ) AS skills FROM dbo.employee AS e WHERE e.dept_id 10 FOR JSON PATH, ROOT(employees);注意子查询里的FOR JSON PATH返回的是字符串类型的 JSON 数组外层再序列化时不会再次转义最终会正确嵌套成数组。外层主查询如果还要对skills做过滤或排序是做不到的因为此时它只是普通字符串这一点要提前想清楚。FOR JSON AUTO也能自动嵌套但它按 SELECT 列表里表的顺序决定层级实际使用中不如 PATH 直观我一般固定用 PATH。2.2 MySQL 的 JSON_OBJECT 与 JSON_ARRAYAGG人工控制每个键MySQL 5.7 起支持JSON_OBJECT和JSON_ARRAYAGG它们更接近“用函数拼 JSON”的思路适合字段需要重命名、加固定值、或者要精确控制嵌套层级的场景。一个分组后输出 JSON 数组的典型查询SELECT JSON_OBJECT( dept_name, d.dept_name, total_emp, COUNT(e.emp_id), employees, JSON_ARRAYAGG( JSON_OBJECT( emp_id, e.emp_id, emp_name, e.emp_name ) ) ) AS dept_json FROM department d LEFT JOIN employee e ON d.dept_id e.dept_id GROUP BY d.dept_id, d.dept_name ORDER BY d.dept_name;这里外层用JSON_OBJECT拼出一个对象JSON_ARRAYAGG负责把分组内的多行变成 JSON 数组。函数嵌套顺序就是 JSON 的层级结构可读性比单纯字符串拼接好得多。JSON_ARRAYAGG里如果子查询结果为空MySQL 会返回NULL而不是空数组这是个很微妙的差异。想让空分组输出[]需要用IFNULL包裹或者借助COALESCEemployees, IFNULL( JSON_ARRAYAGG(JSON_OBJECT(emp_id, e.emp_id)), JSON_ARRAY() )MySQL 8.0 还提供了JSON_OBJECTAGG(key, value)可以把两列直接转成一个对象适合做键值对类的配置表导出。要注意JSON_OBJECTAGG的键列如果有重复后面的值会覆盖前面的导出配置项时必须保证键唯一。实际项目里我倾向于把 JSON 转换函数放在视图或存储过程里统一维护这样业务方拿到的查询 SQL 保持简单字段变更只改一处。2.3 PostgreSQL 的 json_agg / row_to_json 与大数据量下的慢 SQL 边界PostgreSQL 在 JSON 函数上一直比较激进9.4 之后提供了稳定的 JSON / JSONB 支持。生成 JSON 最常用的是json_agg配合row_to_json前者把多行聚合成数组后者把一行转成对象。一个带分组统计的例子SELECT d.dept_name, json_agg( row_to_json(e) ) AS emp_list FROM department d LEFT JOIN employee e ON d.dept_id e.dept_id GROUP BY d.dept_id, d.dept_name ORDER BY d.dept_name;row_to_json(e)会把整行的所有列都带进 JSON这在开发期很方便但上线前要警惕表结构一旦加了审计字段或大字段导出的 JSON 会莫名其妙变大。更稳妥的做法是row_to_json里显式列出列json_agg( row_to_json( json_build_object( emp_id, e.emp_id, emp_name, e.emp_name, hire_date, e.hire_date ) ) )PostgreSQL 还有一个json_build_object配合jsonb_pretty的方案可以把结果缩进格式化方便人工检查。数据量上来之后真正的问题其实不在 JSON 函数本身而在查询是否遵循了慢 SQL 优化的一般原则。json_agg需要把分组内的所有行先攒到内存再序列化分组基数特别大时容易把 work_mem 撑爆触发临时文件落盘。常见做法是在 SQL 里提前把不必要的列过滤掉、对排序字段建立索引、在导出任务里限制单个批次的行数而不是把几百万行一次性灌成 JSON。这条边界同样适用于 SQL Server 的 FOR JSON 和 MySQL 的 JSON_ARRAYAGGJSON 自动生成适合十万行量级的交付再往上就要考虑分页或者压缩传输。3. 从 JSON 到 docx用 Python 脚本把数据自动写成 Word 文档SQL 已经能稳定产出 JSON剩下的问题是如何自动生成 Word。业界最通用的做法是 Python 加python-docx库读取 SQL 生成的 JSON 文件按模板结构填充段落和表格最后另存为 .docx。这一节给出可以直接复制的脚本并说明字段映射逻辑。3.1 最小可跑的转换脚本读取 JSON 文件并生成基础报表先写一个不依赖任何模板库的最小脚本它完成三件事读取 JSON、创建文档对象、写入标题和表格。import json from docx import Document # 读取 SQL 导出的 JSON 文件 with open(employees.json, r, encodingutf-8) as f: data json.load(f) # 数据根节点与 SQL 里 ROOT(employees) 对应 emp_list data[employees] # 创建 Word 文档并设置基础标题 doc Document() doc.add_heading(员工名单报表, level0) # 依据 JSON 数组动态创建表格 table doc.add_table(rows1, cols3) table.style Light Grid Accent 1 hdr_cells table.rows[0].cells hdr_cells[0].text 工号 hdr_cells[1].text 姓名 hdr_cells[2].text 部门 for emp in emp_list: row_cells table.add_row().cells row_cells[0].text str(emp.get(emp_id, )) row_cells[1].text emp.get(emp_name, ) row_cells[2].text emp.get(dept_name, ) doc.save(employee_report.docx)这段脚本的逻辑很直接json.load把 SQL 导出的 JSON 字符串变成 Python 的列表和字典python-docx通过add_table创建表格再用add_row逐行写入。关键参数有两个打开文件时encodingutf-8必须加不然 Windows 默认编码可能读不了中文table.style决定表格的边框样式名不同版本 python-docx 内置样式名略有差异常用的是Table Grid和Light Grid Accent 1如果指定样式不存在会报 KeyError换一个内置样式即可。3.2 模板渲染思路占位符替换与多级标题生成最小脚本只覆盖了“单张表格”场景。真实交付往往需要固定的文档结构封面说明、数据概况、明细表、备注页。常见做法是把 docx 模板当成占位符容器在模板里写[[title]]、[[generated_date]]这类标记然后脚本读模板、替换占位符、填充表格后另存。import json from datetime import date from docx import Document # 打开预先设计好样式的模板文件 doc Document(report_template.docx) # 段落占位符替换形如 [[title]] placeholders { [[title]]: 第 10 部门员工名单, [[generated_date]]: date.today().strftime(%Y-%m-%d), } for paragraph in doc.paragraphs: for key, value in placeholders.items(): if key in paragraph.text: paragraph.text paragraph.text.replace(key, value) # 读取 JSON 并填充已存在的表格 with open(employees.json, r, encodingutf-8) as f: emp_list json.load(f)[employees] table doc.tables[0] for emp in emp_list: row_cells table.add_row().cells row_cells[0].text str(emp.get(emp_id, )) row_cells[1].text emp.get(emp_name, ) row_cells[2].text emp.get(dept_name, ) doc.save(final_report.docx)这里的核心思路是“模板只控制样式脚本只控制数据”。doc.tables[0]定位模板里的第一个表格之后的填充逻辑和最小脚本一致。占位符替换要注意一个陷阱paragraph.text在包含多个 run 时会被拆散直接给paragraph.text赋值会清掉原有样式。稳妥做法是只对包含占位符的整段重新设置字体或者准备模板时保证占位符单独占一个段落。我一般会在模板里把标题段落和正文段落分开占位符字段尽量独立成段这样替换后样式不会乱。3.3 把查询和生成串起来一个 Python 脚本完成全链路前面两个脚本都假设 JSON 文件已经由 SQL 工具导出。要做到真正自动化还得让脚本自己去数据库执行 SQL拿到结果直接生成 docx。常见做法是用 Python 的数据库驱动连接库把结果集序列化成 JSON再走 docx 生成逻辑。import json import pyodbc from docx import Document # 连接 SQL Server注意驱动名称要匹配已安装的 ODBC 驱动 conn pyodbc.connect( DRIVER{ODBC Driver 17 for SQL Server}; SERVERyour_server;DATABASEyour_db; UIDyour_user;PWDyour_password; TrustServerCertificateyes; ) sql SELECT TOP (100) emp_id, emp_name, dept_name FROM dbo.employee ORDER BY emp_id FOR JSON PATH, ROOT(employees); cursor conn.cursor() cursor.execute(sql) row cursor.fetchone() # SQL Server 的 FOR JSON 结果返回在单行单列中 emp_data json.loads(row[0]) doc Document() doc.add_heading(自动生成报表, level0) table doc.add_table(rows1, cols3) table.style Table Grid for emp in emp_data[employees]: cells table.add_row().cells cells[0].text str(emp[emp_id]) cells[1].text emp[emp_name] cells[2].text emp[dept_name] doc.save(auto_report.docx) cursor.close() conn.close()这段脚本把前面的环节压缩成了一个函数连接数据库、执行带 FOR JSON 的查询、解析 JSON、生成 docx。cursor.fetchone()取回的row[0]就是整个 JSON 字符串因为 FOR JSON 的输出是单行单列的字符串结果。如果用的是 MySQL执行SELECT JSON_OBJECT(...)返回的同样是一行一列读取方式一致。密码写在代码里有安全风险线上环境应该改成环境变量或者配置文件。这一节写完读者已经能从 SQL 一路跑到 docx接下来要解决的是参数调优和边界问题。4. 参数与边界JSON 序列化、编码、日期和 Word 样式必调参数自动生成链路能跑通不代表能交付。JSON 序列化参数、字符编码、日期格式、docx 表格样式都会影响最终产物是否可用。这一章把几个必调参数和边界场景说清楚。4.1 JSON 序列化的三个必调参数缩进、中文编码、日期格式如果脚本选择先在内存里把 Python 对象json.dumps成字符串再写入文件三个参数几乎每次都要设置import json from datetime import datetime payload { generated_at: datetime.now().strftime(%Y-%m-%d %H:%M:%S), employees: [ {emp_id: 101, emp_name: 张伟, hire_date: 2024-01-15}, {emp_id: 102, emp_name: 李静, hire_date: 2024-03-02}, ], } json_str json.dumps( payload, ensure_asciiFalse, indent2, separators(,, : ) ) with open(output.json, w, encodingutf-8) as f: f.write(json_str)ensure_asciiFalse是最容易被忽略的参数。不设置它时json.dumps 会把中文转成\u5f20这类 ASCII 转义序列人工排查 JSON 内容时几乎没法读而且某些下游 Java 系统的 JSON 解析器对\uXXXX能正常处理但对接方如果做了字符串直接入库就会把转义字符原封不动存进去。indent2控制格式化缩进方便人工抽查和 git diff生产环境如果为了省存储可以设indentNone并把separators设成紧凑模式但日常交付我建议保留缩进。日期字段的处理要提早统一口径最好在 SQL 阶段就用FORMAT(hire_date, yyyy-MM-dd)或DATE_FORMAT转成字符串因为 Python 侧的datetime对象不是 JSON 原生类型不做处理会直接报错。4.2 docx 生成的样式参数列宽、字体、页面方向和表格样式python-docx 生成的表格默认会尝试自动调整列宽多列数据多的时候经常出现列宽失衡。批量交付时列宽、字体、页边距这些样式参数值得单独维护。from docx import Document from docx.shared import Pt, Cm doc Document() section doc.sections[0] section.page_width Cm(29.7) section.page_height Cm(21.0) table doc.add_table(rows1, cols3) table.autofit False widths [Cm(3.0), Cm(4.5), Cm(6.0)] for i, width in enumerate(widths): for row in table.rows: row.cells[i].width width for row in table.rows: for cell in row.cells: for paragraph in cell.paragraphs: for run in paragraph.runs: run.font.size Pt(10)关键参数集中在三处table.autofit False关闭自动调整row.cells[i].width设置每一列的宽run.font.size控制字号。这里有个隐藏细节python-docx 里单独设置某个单元格宽度不一定生效必须对每一行的同一列都设置相同宽度表格才会稳定。横向报表如果列数多可以在section里把页面方向设为横向即宽高互换列宽的可容纳范围会大很多。准备模板时直接把页面设置和字体写在模板文件里脚本只改数据是维护成本最低的路子。4.3 适用边界哪些场景不该走“SQL 生成 JSON 再转 docx”这条链路不是万能的。第一种不适用的场景是数据量过大SQL 序列化出几十 MB 的 JSON 再交给 Python 解析内存压力全在脚本侧更容易 OOM这种量级更适合用流式处理或者直接导出 CSV。第二种是有 BLOB / 二进制字段JSON 里存放图片、附件内容会让生成物膨胀且无法在 docx 中直接展示。第三种是敏感数据SQL 里写了 WHERE 条件但不小心把不需要的字段选了出来JSON 一旦落盘就有泄露风险交付前要反复检查 SELECT 列表。此外如果下游对 JSON 字段顺序有严格依赖而数据库函数在嵌套子查询里可能调整键顺序就得在 SQL 里显式固定列顺序不能把希望寄托在“碰巧顺序一致”上。做选型时先判断交付物是什么、量级多大、字段里有没有特殊类型再决定要不要采用这套链路。5. SQL 生成 JSON 并转 Word 的常见问题排查现象、原因与解法链路越长翻车点越多。这里整理五条我踩过的坑按“现象 → 原因 → 解决”的方式写每条都对应前面章节里的某个参数或步骤。5.1 JSON 文件里中文变成 \uXXXX下游拿到后显示乱码现象SQL 导出的 JSON 文件在文本编辑器里中文全部是\u5f20这种转义序列交给业务方后对方明明按 UTF-8 解析界面上却显示出字面量的“\u5f20”。原因多数数据库客户端导出 JSON 时默认启用 ASCII 转义或者 Python 脚本json.dumps没设置ensure_asciiFalse。SQL Server Management Studio 的查询结果另存为文件时也会按 Unicode 转义处理非 ASCII 字符。解决如果 JSON 由脚本生成json.dumps时必须显式传ensure_asciiFalse并保证写入文件用encodingutf-8。如果 JSON 由数据库客户端手工导出换成结果另存为 UTF-8 文件或者把这条链路完全脚本化避免依赖客户端默认行为。5.2 NULL 字段在 JSON 里整个消失下游改造接口时字段缺失现象同一张表有的行某字段为 NULL生成 JSON 后这个 key 直接不存在下游 Java 解析时用getString(phone)报空指针或者前端渲染时对应列显示空白但与前后行结构不一致。原因SQL ServerFOR JSON PATH默认省略 NULL 字段MySQL 的JSON_OBJECT对 NULL 值的处理同样可能丢键。这属于合约层面的不一致下游解析是按固定 schema 设计的结果却拿到了缺失字段。解决在 SQL 里显式加上INCLUDE_NULL_VALUESSQL ServerMySQL 侧用IFNULL(field, )或COALESCE把 NULL 转成空字符串再包进JSON_OBJECT。更稳妥的做法是定义一份接口字段清单SQL 按清单逐列输出缺失字段宁愿给空值也不要省略。5.3 生成的 Word 表格列宽忽宽忽窄列太宽超出页面现象自动生成的 docx 表格打开后各列宽度不一致有的列被压缩到只能显示两个字有的列直接顶到页面边缘打印预览时右侧溢出。原因python-docx 的表格默认开启自动调整如果没为每一列指定相同宽度Word 会根据内容长度重新计算列宽叠加中文字符宽度计算差异列宽就会失控。解决在脚本里设置table.autofit False然后遍历所有行给每一列设相同的Cm宽度。还要检查页面宽度横向报表里 5 列以上的表格优先把 section 方向改成横向避免列宽总和超过页面可用宽度。5.4 日期字段输出成带时区的 UTC 字符串与预期格式不一致现象JSON 里有类似2024-01-15T08:00:00Z的时间值业务方期望的是2024-01-15导致他们按日期字符串做匹配时全部失败。原因PostgreSQL 的row_to_json碰上 timestamptz 类型会按数据库当前时区序列化SQL Server 的 FOR JSON 默认输出 ISO 8601 格式也可能带时区偏移。解决这是最典型的“字段口径要先对齐”案例。统一在 SQL 层做格式化SQL Server 用FORMAT(hire_date, yyyy-MM-dd)MySQL 用DATE_FORMAT(hire_date, %Y-%m-%d)PostgreSQL 用to_char(hire_date, YYYY-MM-DD)。SQL 里转好的字符串传给 JSON 函数就不会再有歧义。5.5 大表导出 JSON 时查询特别慢甚至把数据库实例拖垮现象一张几百万行的表直接跑FOR JSON PATH或json_agg查询执行时间从秒级变成分钟级数据库 CPU 飙高业务侧反馈接口变慢。原因JSON 聚合函数必须把分组内的所有行缓存后再序列化内存不够就落盘I/O 压力成倍放大。再加上有些 SQL 没有针对过滤字段建索引全表扫描和排序都要额外耗时整个任务就变成了一次慢 SQL。解决先按慢 SQL 优化的常规思路处理过滤条件下推、避免 SELECT 不必要的大字段、排序字段走索引。然后限制批量量级比如按WHERE hire_date 2024-01-01分段导出或SELECT TOP (10000)一档一档取。还有一个惯用技巧先在临时表里把要导出的列清洗好再从临时表做 JSON 聚合避免在源表大字段上反复扫描。6. 进阶用法用 JSON Schema 校验结果把自动化脚本变成可交付的批处理链路稳定之后值得再加一道校验工序给 SQL 产出的 JSON 定义一个 schema在生成 docx 之前先跑校验字段缺失、类型错误、日期格式不对都能在第一时间被脚本拦住而不是等业务方打开文档才发现。常见做法是用 Python 的jsonschema库加载一篇 schema 定义文件。import json from jsonschema import validate, ValidationError schema { type: object, properties: { employees: { type: array, items: { type: object, required: [emp_id, emp_name], properties: { emp_id: {type: integer}, emp_name: {type: string}, dept_name: {type: string} } } } }, required: [employees] } with open(employees.json, r, encodingutf-8) as f: data json.load(f) try: validate(instancedata, schemaschema) print(JSON 结构校验通过) except ValidationError as e: print(f校验失败: {e.message}) raise这段校验脚本的价值在于把“人肉检查 JSON”变成了机器检查。required字段列表就是你和下游约定的契约任何一边调整字段都要先改 schema类型声明则能提前暴露把工号输出成字符串这类问题。我现在做报表交付的日常流程是先跑一条 TOP 5 的 SQL确认 JSON 字段没有缺失再把全量数据跑完经过 schema 校验后生成 docx最后按日期和批次自动命名文件。这样一遍跑下来出错的地方几乎全在 SQL 阶段就能暴露到了文档生成环节基本不用返工。这套方案适合团队里没有专门后端资源、却要频繁交付结构化数据的场景SQL 出 JSON、脚本出 Word两条命令就能完成一单任务。希望这个思路帮到你。本文还有配套的精品资源点击获取