数据库对json的支持

json以其完全透明的文本格式，已成为新的web service的事实数据标准。

数据库纷纷增加了对它的支持：

PostgreSQL 9.3 引入 json_agg

SELECT json_agg(t1) FROM t1 返回json数组：[{"varchar":"开始","numeric":null,"int":null,"bigint":null,"text":null,"date":"2026-10-04","json":{"f1":"vaa"},"bool":null,"time":"07:43:35","timestamp":"2026-10-04T07:45:00"}]

sql server2019引入 select..JSON PATH子句

SELECT 
    id, 
    firstName AS "info.name",   -- 创建嵌套对象: { "info": { "name": "..." } }
    lastName AS "info.surname",
    age
FROM People
FOR JSON PATH;

　　返回：

[{"id":1,"info":{"name":"John","surname":"Doe"},"age":30}]

mysql5.7+:

SELECT JSON_ARRAYAGG(
    JSON_OBJECT('id', id, 'name', name, 'email', email)
) AS result_json
FROM users;

　　oracle12.1.0.2+:

SELECT JSON_ARRAYAGG(JSON_OBJECT('id' VALUE id, 'name' VALUE name)) 
FROM your_table;