PostgreSQL 输出 JSON 结果

PostGreSQL 从 9.2 开始增加对 JSON 的支持。9.5 已经支持多个 JSON 函数，见 http://www.postgres.cn/docs/9.5/functions-json.html

关于如何查询返回 JSON，这里有例子，翻译如下：

一个简单的用法就是使用 row_to_json() 函数，它接受 “行值”并返回 JSON 对象：

1	`select` `row_to_json(tableName)` `from` `tableName;`

上面查询语句返回结果类似如下：

1	`{"id":6013,"text":"advancement","pronunciation":"advancement",...}`

但是有时候我们只需要查询指定的列，那么我们可以使用 row() 结构函数：

select row_to_json(row(id, text)) from tableName;

上面查询语句返回了我们想要的结果，可惜丢失了列名：

1	`{"f1":6013,"f2":"advancement"}`

为了完善这个需求，我们必须创建一个行类型且将结果转换（cast）到这个行类型，或者使用子查询。子查询会更容易一些：

select row_to_json(t)
from (
  select id, text from tableName
) AS t

上面查询语句返回了我们希望的样子：

1	`{"id":6013,"text":"advancement"}`

另一种常用的技术是 array_agg 和 array_to_json。array_agg 是一个聚合函数 sum 或 count。它聚集成一个 PostgreSQL 数组参数。array_to_json 以 PostgreSQL数组拼合成一个单一的JSON值。

我们来看看 array_to_json 的用法：

select array_to_json(array_agg(row_to_json(t)))
from (
  select id, text from tableName
) AS t

上面查询语句返回了一个由 JSON 对象组成的数组：

1	`[{"id":6001,"text":"abaissed"},{"id":6002,"text":"abbatial"},{"id":6003,"text":"abelia"},...]`

我们来一个复杂的例子（注：这个例子可能有问题）：

select row_to_json(t)
from (
  select text, pronunciation,
    (
      select array_to_json(array_agg(row_to_json(d)))
      from (
        select part_of_speech, body
        from definitions
        where word_id=words.id
        order by position asc
      ) d
    ) as definitions
  from words
  where text = 'autumn'

上面查询语句返回结果如下：

{
  "text": "autumn",
  "pronunciation": "autumn",
  "definitions": [
    {
        "part_of_speech": "noun",
        "body": "skilder wearifully uninfolded..."
    },
    {
        "part_of_speech": "verb",
        "body": "intrafissural fernbird kittly..."
    },
    {
        "part_of_speech": "adverb",
        "body": "infrugal lansquenet impolarizable..."
    }
  ]
}