资讯评论 - PostgreSQL 9.2中将引入生成JSON数据功能 - OSCHINA

PostgreSQL 9.2中将引入生成JSON数据功能

PostgreSQL开发者Andrew Dunstan写到：

上周三，也就是PostgreSQL 9.2最后一个commitfest到来的前四天，Robert Haas提交了一个名为JSON for 9.2的补丁，欲将JSON列为PostgreSQL的核心类型。基本上，该补丁功能是对文本进行解析，确保它是有效的JSON数据，并加以存储。这个功能我原本打算在9.2版本中放弃的，不过我考虑了下发现Robert的补丁太小了点，因此决定继续并又添加了一些功能，包括：query_to_json()、 array_to_json()以及record_to_json()，完成了从ProstgreSQL生成JSON数据。

以下是来自回归测试中的一些简单示例：

SELECT query_to_json('select x as b, x * 2 as c from generate_series(1,3) x',false);
                query_to_json
---------------------------------------------
[{"b":1,"c":2},{"b":2,"c":4},{"b":3,"c":6}]
(1 row)

SELECT array_to_json('{{1,5},{99,100}}'::int[]);
array_to_json
------------------
[[1,5],[99,100]]
(1 row)

-- row_to_json
SELECT row_to_json(row(1,'foo'));
     row_to_json
---------------------
{"f1":1,"f2":"foo"}
(1 row)

更简单一点的：

SELECT row_to_json(q)
FROM (SELECT $$a$$ || x AS b,
         y AS c,
         ARRAY[ROW(x.*,ARRAY[1,2,3]),
               ROW(y.*,ARRAY[4,5,6])] AS z
      FROM generate_series(1,2) x,
           generate_series(4,5) y) q;
                            row_to_json
--------------------------------------------------------------------
{"b":"a1","c":4,"z":[{"f1":1,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
{"b":"a1","c":5,"z":[{"f1":1,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
{"b":"a2","c":4,"z":[{"f1":2,"f2":[1,2,3]},{"f1":4,"f2":[4,5,6]}]}
{"b":"a2","c":5,"z":[{"f1":2,"f2":[1,2,3]},{"f1":5,"f2":[4,5,6]}]}
(4 rows)