LEFT JOIN应该返回null，但返回具有null值的嵌套属性的对象

Question

我有两个表，

CREATE TABLE IF NOT EXISTS my_schema.layout (
id serial PRIMARY KEY,
layout_name VARCHAR (50) UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS my_schema.page (
id serial PRIMARY KEY,
layout_id BIGINT REFERENCES my_schema.layout (id)
);
INSERT INTO my_schema.layout VALUES
(1 ,'basic'),
(2 ,'header');

INSERT INTO my_schema.page VALUES
(1, 1),
(2, null);

您可以注意到，id为2的page记录的列layout_id具有null值。

但是，当我执行查询时，我会得到以下结果：

id  layout
1   {"id" : 1, "layout_name" : "basic"}
2   {"id" : null, "layout_name" : null}

但我除了结果输出是：

id  layout
1   {"id" : 1, "layout_name" : "basic"}
2   null

这是我的问题：

SELECT
p.id,
json_build_object(
'id', l.id,
'layout_name', l.layout_name
) as layout
FROM my_schema.page p
LEFT JOIN my_schema.layout l
ON l.id = p.layout_id

我也尝试过COALESCE，但它一直在抛出一个错误。我在这里能做什么？

Answer 1

由于键与列名相同，因此可以将其简化为：

SELECT p.id,
to_jsonb(l) as layout
FROM my_schema.page p
LEFT JOIN my_schema.layout l ON l.id = p.layout_id

它将返回您所期望的CCD_ 6值。

Answer 2

Horse的答案很有效，但我认为它不是很一般。一个更通用的解决方案是将函数封装在CASE:中

CASE WHEN l.id is NULL THEN NULL ELSE json_build_object(
'id', l.id,
'layout_name', l.layout_name
) END as layout