去掉中间层：直接从 Postgres 生成 JSON

飞象数据 2022-05-13

301

很多时候，Web 层充满了样板文件，除了将结果集转换为 JSON 之外什么都不做。中间层可以像调用返回JSON的函数一样简单。我们所需要的只是一种在数据库中将结果集转换为JSON的简单方法。

PostgreSQL 具有内置的 JSON 生成器，可用于在数据库中创建结构化的 JSON 输出，从而提高性能并从根本上简化 Web 层。

幸运的是，PostgreSQL有这样的函数，可以在数据上直接运行，以获得更好的性能和更低的带宽使用。

一些示例数据

要试用这些示例，请加载这些数据：

CREATE TABLE employees (
  employee_id serial primary key,
  department_id integer references departments(department_id),
  name text,
  start_date date,
  fingers integer,
  geom geometry(point, 4326)
  );


CREATE TABLE departments (
  department_id bigint primary key,
  name text
  );


INSERT INTO departments
 (department_id, name)
VALUES
 (1, 'spatial'),
 (2, 'cloud');


INSERT INTO employees
 (department_id, name, start_date, fingers, geom)
VALUES
 (1, 'Paul',   '2018/09/02', 10, 'POINT(-123.32977 48.40732)'),
 (1, 'Martin', '2019/09/02',  9, 'POINT(-123.32977 48.40732)'),
 (2, 'Craig',  '2019/11/01', 10, 'POINT(-122.33207 47.60621)'),
 (2, 'Dan',    '2020/10/01',  8, 'POINT(-122.33207 47.60621)');
复制

四名员工，分成两个部门，每个员工都有一些详细信息。

使用 row_to_json 轻松实现 JSON

最简单的 JSON 生成器是接收一个元组值并返回等效的 JSON 字典。

SELECT row_to_json(employees)
FROM employees
WHERE employee_id = 1;
复制

生成的 JSON 使用列名作为键，所以你得到一个整洁的字典。

{
  "employee_id": 1,
  "department_id": 1,
  "name": "Paul",
  "start_date": "2018-09-02",
  "fingers": 10,
  "geom": {
    "type": "Point",
    "coordinates": [
      -123.329773,
      48.407326
    ]
  }
}
复制

看看geometry
类型的字段会发生什么！因为PostGIS包括从几何到 JSON 的转换，所以几何列在转换中会自动映射到GeoJSON。对于任何自定义类型，这都是一个有用的技巧：定义转换为 JSON，然后您会自动与本地 PostgreSQL JSON 生成器集成。

使用 json_agg 的完整结果集

将单行转换为字典对于基本记录访问来说很好，但查询经常需要转换多行。

幸运的是，有一个聚合函数，它执行 JSON 转换并将多个结果转换为 JSON 列表。

SELECT json_agg(e)
FROM (
    SELECT employee_id, name
    FROM employees
   WHERE department_id = 1
    ) e;
复制

请注意，为了剥离记录中的数据，我们使用子查询对 json_agg
。

[
  {
    "employee_id": 1,
    "name": "Paul"
  },
  {
    "employee_id": 2,
    "name": "Martin"
  }
]
复制

使用子查询的嵌套结果集

到目前为止，所有这些都很容易在中间件中复制，但当您开始转储结构化结果时，事情会变得更有趣。

使用聚合并将结果分阶段转换为 JSON，可以构建反映表关系的嵌套 JSON 输出。

WITH
-- strip down employees table
employees AS (
  SELECT department_id, name, start_date
  FROM employees
),
-- join to departments table and aggregate
departments AS (
  SELECT d.name AS department_name,
         json_agg(e) AS employees
 FROM departments d
  JOIN employees e
  USING (department_id)
  GROUP BY d.name
)
-- output as one json list
SELECT json_agg(departments)
FROM departments;
复制

结果对于每个部门都有一个条目，每个条目都包含其两名员工。

[
{
        "department_name": "cloud",
        "employees": [
            {
                "department_id": 2,
                "name": "Craig",
                "start_date": "2019-11-01"
            },
            {
                "department_id": 2,
                "name": "Dan",
                "start_date": "2020-10-01"
            }
        ]
},
{
        "department_name": "spatial",
        "employees": [
            {
                "department_id": 1,
                "name": "Paul",
                "start_date": "2018-09-02"
            },
            {
                "department_id": 1,
                "name": "Martin",
                "start_date": "2019-09-02"
            }
        ]
}
]
复制

如果您希望输出是关联数组而不是列表，请将最终的json_agg
替换为json_object_agg
。

JSON 格式的表结构

曾经想从数据库中快速提取表结构的定义吗？使用 JSON 格式化程序和 PostgreSQL 系统表，所有这些信息都在手边。

WITH rows AS (
  SELECT c.relname, a.attname, a.attnotnull, a.attnum, t.typname
  FROM pg_class c
  JOIN pg_attribute a
    ON c.oid = a.attrelid and a.attnum >= 0
 JOIN pg_type t
    ON t.oid = a.atttypid
  JOIN pg_namespace n
    ON c.relnamespace = n.oid
 WHERE n.nspname = 'public'
    AND c.relkind = 'r'
),
agg AS (
  SELECT rows.relname, json_agg(rows ORDER BY attnum) AS attrs
  FROM rows
 GROUP BY rows.relname
)
SELECT json_object_agg(agg.relname, agg.attrs)
FROM agg;
复制

这是departments
表的条目。

{
  "departments": [
    {
      "relname": "departments",
      "attname": "department_id",
      "attnotnull": true,
      "attnum": 1,
      "typname": "int8"
    },
    {
      "relname": "departments",
      "attname": "name",
      "attnotnull": false,
      "attnum": 2,
      "typname": "text"
    }
  ],
  ...
}
复制

结论

PostgreSQL JSON 转换器可以将任何结果集直接转换为数据库中的 JSON
通过将JSON创建推到堆栈的更底层，可以大大简化Web层
如果在自定义类型上定义了转换为 JSON，则自定义类型可以转换自定义 JSON

文章转载自飞象数据，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。