json - 如果连接不匹配，如何与 json_build_object 一起返回 null？

Question

我有两张表，一张外键指向另一张:

CREATE TABLE employee (
  employee_id INT PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE project (
  project_id INT PRIMARY KEY,
  employee_id INT NOT NULL REFERENCES employee(employee_id),
  name text NOT NULL
);

我正在尝试查询一名员工及其项目(假设只允许一个项目)。如果项目存在，我希望项目显示为 JSON 对象，否则我理想情况下喜欢 null。

我有以下查询，它执行我想要的...

SELECT e.employee_id,
       e.name,
       row_to_json(p.*) AS project
FROM employee e
LEFT JOIN project p USING(employee_id)

问题是，我想从连接中有选择地选择列，所以我将查询调整为如下:

SELECT e.employee_id,
       e.name,
       json_build_object('name', p.name) AS project
FROM employee e
LEFT JOIN project p USING(employee_id)

上述查询的问题在于，如果员工没有匹配的项目，json_build_object 仍会生成一个具有空值的对象，而不是仅仅不生成一个对象。 row_to_json 查询能够看到连接没有匹配的行，因此它没有创建 json 对象。

有没有什么方法可以实现我正在寻找的东西，同时能够使用我选择的列构建我自己的 JSON 对象？

Answer 1

您可以使用CASE 来检查该值是否为空:

SELECT e.employee_id,
       e.name,
       CASE 
            WHEN p.name IS NULL THEN NULL
            ELSE json_build_object('name', p.name) 
       END AS project
FROM employee e
LEFT JOIN project p USING(employee_id);

如果您打算重复选择不同的值，此函数会很有用:

create or replace function to_json_or_null(key text, val text)
returns json language sql as $$
    select case
        when val is null then null
        else json_build_object(key, val)
    end
$$;

SELECT e.employee_id,
       e.name,
       to_json_or_null('name', p.name) AS project
FROM employee e
LEFT JOIN project p USING(employee_id);