SQL中如何查询并返回XML或JSON格式的字段内容

SQL Server中FOR XML字段值被转义是默认安全行为，需用FOR XML PATH(''), TYPE配合.value()提取原始字符串以还原特殊字符。

SQL Server里用 FOR XML 生成XML结果时，字段值被转义怎么办？

默认情况下，FOR XML 会把字段里的特殊字符（比如 &、、<code>>）自动转义成 &、<、>，导致原始内容变形。这不是bug，是安全设计，但如果你明确知道字段内容可信且需要原样输出，得加 TYPE 或改用 FOR XML RAW('row'), ELEMENTS XSINIL 配合 .value() 提取。

• 用 SELECT ... FOR XML RAW, TYPE 可返回真正的XML数据类型，避免字符串级转义
• 如果字段是文本且含HTML/标签，先用 REPLACE 清洗再拼接，比依赖自动转义更可控
• FOR XML EXPLICIT 灵活性高，但写法复杂，小批量数据建议避开，容易漏掉 NULL 处理逻辑

PostgreSQL中 row_to_json() 和 to_jsonb() 的行为差异

row_to_json() 接收一行记录（如 SELECT row_to_json(t) FROM (SELECT 1 AS id, 'a' AS name) t），而 to_jsonb() 可直接处理任意表达式或标量值，且对 NULL、数字精度、时间戳时区更严格。JSONB 还支持索引和键路径查询，但序列化后丢失字段顺序。

• 想保留字段定义顺序 → 用 row_to_json()
• 要做 @> 包含查询或建 GIN 索引 → 必须用 to_jsonb()
• 数值字段如果来自 float8，to_jsonb() 可能显示为科学计数法（如 1e-5），而 row_to_json() 更倾向保留小数点格式

MySQL 5.7+ 用 JSON_OBJECT() 拼接字段时，NULL值怎么控制？

JSON_OBJECT() 默认跳过 NULL 键值对，不像传统 SQL 函数那样报错或留空。这在构造API响应时很实用，但也容易漏掉预期字段——比如你写了 JSON_OBJECT('status', status_col)，而 status_col 是 NULL，整个 "status" 键就消失了。

• 强制保留键：用 IFNULL(status_col, 'unknown') 或 COALESCE(status_col, '')
• 嵌套对象要分层构建：JSON_OBJECT('user', JSON_OBJECT('id', u.id, 'name', u.name))，不能直接传子查询
• 字段名含空格或特殊字符？必须用字符串字面量：JSON_OBJECT('full name', u.name)，不能写 JSON_OBJECT(full name, u.name)

Oracle中 XMLAGG() 和 JSON_OBJECTAGG() 对重复键的处理

Oracle 12c+ 的 JSON_OBJECTAGG() 在遇到重复键时默认报错 ORA-40478: maximum number of JSON object members exceeded（其实是键冲突，不是数量超限），而 XMLAGG() 会无脑拼接，不校验唯一性。两者都不自动去重。

• 去重必须前置：SELECT JSON_OBJECTAGG(k:v) FROM (SELECT DISTINCT key_col k, val_col v FROM t)
• 想合并同键多值？用 JSON_ARRAYAGG() 套一层：JSON_OBJECTAGG(key_col VALUE JSON_ARRAYAGG(val_col))
• XMLAGG(XMLELEMENT(...)) 如果源数据有换行符，生成的XML里会带 实体，解析端需注意是否自动还原

实际用的时候，别只看函数名像不像“转JSON”，先确认你数据库版本是否真支持——比如 MySQL 5.6 就没有 JSON_OBJECT()，强行用会报 FUNCTION does not exist；Oracle 11g 也压根没 JSON_OBJECTAGG()。字段类型、NULL策略、编码边界，这些细节一错，返回的就是不可用的碎片数据。