如何利用SQL视图生成复杂的XML/JSON输出

sql server for xml auto 默认忽略null字段，需用isnull/coalesce配合xsinil；postgresql json_agg嵌套须子查询+group by防笛卡尔积；mysql json_object要求key为字符串字面量，需cast和ifnull处理。

如何利用sql视图生成复杂的xml/json输出_格式化处理

SQL Server 用 FOR XML AUTO 生成嵌套 XML 容易漏掉 NULL 字段

SQL Server 的 FOR XML 默认会跳过值为 NULL 的列，导致生成的 XML 结构不完整，前端解析时可能报错或丢数据。这不是 bug，是设计行为，但常被误认为“输出异常”。

实际做法是显式用 ISNULL() 或 COALESCE() 替换 NULL，并配合 XSINIL 属性强制输出空元素：

SELECT 
  id,
  ISNULL(name, '') AS name,
  ISNULL(email, '') AS email
FROM users
FOR XML AUTO, ELEMENTS XSINIL

XSINIL 会让 NULL 变成 <email xsi:nil="true"></email>，而非直接省略
如果下游系统不支持 xsi:nil，就改用 ISNULL(email, '') 输出空字符串，再加 TYPE 保证返回 XML 类型
避免用 FOR XML RAW 做复杂结构——它只生成扁平标签，嵌套需靠子查询或 FOR XML EXPLICIT（后者难维护）

PostgreSQL 中 json_agg() 嵌套聚合必须用子查询或 CTE

想从订单表连查订单项并生成 JSON 数组？直接 json_agg(orders.*) + JOIN 会导致笛卡尔积，json_agg 把重复行全塞进去，结构爆炸。

正确路径是先聚合子表，再关联主表：

SELECT 
  o.id,
  o.customer_name,
  COALESCE(items.item_list, '[]'::json) AS items
FROM orders o
LEFT JOIN (
  SELECT 
    order_id,
    json_agg(
      json_build_object(
        'id', id,
        'sku', sku,
        'qty', qty
      )
    ) AS item_list
  FROM order_items
  GROUP BY order_id
) items ON items.order_id = o.id;

子查询里用 GROUP BY order_id 是关键，否则 json_agg 不知道按什么粒度聚
COALESCE(..., '[]'::json) 防止无订单项时字段为 NULL，前端 JSON.parse() 不崩溃
别在视图里写 json_agg 后再用应用层二次处理——PostgreSQL 9.4+ 的 jsonb_path_query 能直接过滤，但视图不支持参数化，得用函数封装

MySQL 8.0+ json_object() 和 json_arrayagg() 不支持别名自动推导

MySQL 的 json_object(key, value) 要求 key 必须是字符串字面量或表达式，不能直接写列别名。写 json_object(name, email) 会报错 ERROR 3142 (HY000): Invalid data type for JSON object key。

必须显式转成字符串，且注意 NULL 处理：

SELECT 
  JSON_OBJECT(
    'id', CAST(id AS CHAR),
    'name', IFNULL(name, ''),
    'tags', COALESCE(JSON_ARRAYAGG(tag_name), JSON_ARRAY())
  ) AS user_profile
FROM users u
LEFT JOIN user_tags ut ON u.id = ut.user_id
GROUP BY u.id;

CAST(id AS CHAR) 是必须的，数字类型不能当 JSON key
IFNULL(name, '') 比 COALESCE 更适合单值替换，语义清晰
JSON_ARRAYAGG 在无匹配行时返回 NULL，所以外层套 COALESCE(..., JSON_ARRAY()) 确保始终是数组
如果字段含特殊字符（如点、斜杠），需用 JSON_OBJECT('`full.name`', full_name) 加反引号包裹 key

跨数据库视图输出 JSON/XML 时，字段别名和大小写敏感性要手动对齐

同一个视图在 PostgreSQL 返回 {"user_id": 123}，在 SQL Server 用 FOR XML 转 JSON（需额外函数）可能变成 {"UserID": 123}——因为 SQL Server 默认 PascalCase，PostgreSQL 默认小写下划线。这不是格式问题，是元数据映射差异。

统一方案只有两个：要么在视图定义里全部用双引号/方括号强制小写别名，要么在应用层做 key normalize（比如转成 kebab-case 再解析）。

SQL Server：SELECT id AS [user_id], name AS [user_name] FROM users FOR JSON PATH
PostgreSQL：SELECT id AS "user_id", name AS "user_name" FROM users（双引号保小写）
MySQL：SELECT id AS `user_id`, name AS `user_name` FROM users（反引号）
别依赖数据库默认命名规则——哪怕都用小写，SQL Server 的 FOR JSON 在 ROOT 模式下会多包一层对象，而 MySQL 不会

最麻烦的不是语法，是团队里有人用视图字段名写前端 key，有人用原始列名，一上线就 500。字段名一旦进视图，就得当成 API contract 管理。