PostgreSQL数据库模型

Question

我已经为我的移动应用程序后端选择了PostgreSQL，并试图找到存储和查询数据的最佳方法。问题是存储数据最好是SQL，但是检索数据最好是文档。

例如，我有表项：

+----+--------+------+
| id | title  | uuid | 
+----+--------+------+
|  1 | Hello  |  32  |
|  2 | World  |  25  |
|  3 | Tom    |  435 |
+----+--------+------+

然后是表记录：

+----+---------+----------+
| id | itemId  | resource | 
+----+---------+----------+
|  1 | 1       |  res1    |
|  2 | 1       |  res2    |
|  3 | 1       |  res3    |
|  4 | 2       |  res4    |
+----+---------+----------+

这几乎是标准的SQL方法。现在我想得到的是：

{
  id: 1,
  title: "Hello",
  uuid: 32,
  records: [
    {id: 1, resource: "res1"},
    {id: 2, resource: "res2"}, 
    {id: 3, resource: "res3"}
  ]
}

我想你明白了。我对PostgreSQL相当陌生，我相信在它的所有令人敬畏的地方，将会有一个优雅的解决方案。我所能想到的就是创建我可以查询的视图表，但不确定如何准确地构建这个查询。

Answer 1

如果您有一组要查询的表，并且希望将输出作为JSON数据结构返回，那么您现在有两个选择：

1. 执行查询，并在应用程序后端将结果转换为JSON。这是一种相当标准的方法，而且可能仍然是最简单的方法，特别是如果您正在编写后端代码的语言具有良好的JSON支持。
2. 构造查询，以便返回一个用JSON编码的结果，这要感谢PostgreSQL 9.2及更高版本。

这个文章很好地介绍了后一种方法。下面是一个查询，它提供了上面所要求的内容：

select row_to_json(t)
from (
  select items.id,
    items.title,
    items.uuid,
    (
        select array_to_json(array_agg(row_to_json(d)))
        from (
            select records.id,
                records.resource
            from records
            where items.id=records.itemid
        ) d
    ) as records
  from items
  where items.id=1
) as t;

结果：

{
  "id": 1,
  "title": "Hello",
  "uuid": "32",
  "records": [
    {
      "id": 1,
      "resource": "res1"
    },
    {
      "id": 2,
      "resource": "res2"
    },
    {
      "id": 3,
      "resource": "res3"
    }
  ]
}

我使用jsonprettyprint.com来使它看起来更好看--它实际上是以一行形式出现的，没有缩进，但仍然非常有效。

以这种方式创建JSON输出是很费心的，至少对我的口味来说是这样。我可能更喜欢在应用程序中这样做。但是随着JSON支持的成熟，我希望它会变得更容易。